단일 테이블 선택도

• Cardinality 가 왜 중요한가? Cardinality 는 최초의 조인순서와 최적의 인덱스선택에 결정적인 역할을 하기 때문이다.
• 책에서 selectivity에 집중하는 이유는 selectivity만이 조건형태에 따라 Cardinality에 변화를 주기 때문이다.
• Cardinality가 부정확하다는 것은 Selectivity의 계산이 잘못되었다는 의미이다.
• Selectivity(선택도)는 전체 데이터집합(base cardinality) 중 특정 조건에 의해 걸러질 것이라고 예상되는 비율이다.

조건의 형태에 따른 단일 테이블 selectivity

• 단일 컬럼의 단일조건
• 단일 컬럼의 범위조건
• 다중 컬럼의 한정/무한정 그리고 열린/닫힌 조전
• 바인드 변수를 포함한 단일조건
• 바인드 변수를 포함한 범위조건

단일 컬럼의 Cardinality를 구하는 알기 쉬운 예

-- 온라인코드집(birth_month_01.sql)을 보면 Test의 단순성을 위해서인지 _optimizer_cost_model을 io로 설정했고, 히스토그램 없이 통계정보를 수집했다

create table audience as
selec	trunc(dbms_random.value(1,13))	month_no
from	all_objects
wher	rownum <= 1200
;

begin
	dbms_stats.gather_table_stats(
		user,
		'audience',
		cascade => true,
		estimate_percent => null,
		method_opt => 'for all columns size 1'
	);
end;
/

set autotrace traceonly explain

select count(*) 
from audience
where month_no = 12
;

Null과 Cardinaliry

• 위의 예에서 자기 생일을 기억 못하는 청중이 10%라고 가정한다면

select count(*)
from auudience 청중
where month_no = 12

• Base Selectivity: 1/12
• Num_Nulls = 120

Update audience
 Set month = null
 Where rowed in (select rid
From (select rowed rid,
                     , ntil(10) over () nt
From audience) 
Where nt = 10);
Commit;
begin
	dbms_stats.gather_table_stats(
		user,
		'audience',
		cascade => true,
		estimate_percent => null,
		method_opt => 'for all columns size 1'
	);
end;
/
set autotrace traceonly explain

select count(*) 
from audience
where month_no = 12
;?

범위 기반 조건을 포함한 쿼리

표에서 볼 수 있는 몇 가지 패턴

1. 리터럴을 사용했을 때 열린 조건(>,<)과 닫힌 조건(<=, >=)의 차이가 정확히 100이다.
2. 컬럼 값의 범위가 user_col_statistics.low_value, user_col_statisticss.high_value를 벗어나면 100으로 고정된다. (그러나 10g에서는 선형적으로 Cardinality가 감소한다.)
3. 바인드 변수의 사례에서는 열린 조건과 닫힌 조건 사이에 아무런 차이가 없으며, 단일 검색 시 100개의 Row를 Return하는 상황에서 범위검색 시의 리턴 Row수가 너무 비현실적이다.

Case 1. Month_no > 8

• Selectivity = (high_value - limit) / (high_value - low_value) = (12-8)/(12-1 )= 4/11
• Cardinality = 1200 * 4/11 = 435...

Case 2. Month_no >= 8

• Case1의 사례와 틀린 점은 Month_no = 8만큼의 단일 컬럼 단일 조건의 Selectivity가 추가됐다는 점이다
• Selectivity = (high_value - limit) / (high_value - low_value) + 1/NDV = 4/11+1/12
• Cardinality = 1200 * (4/11 + 1/12) = 536...

Case 3, Case 4.

• Case 1, 2와의 차이는 식에서의 limit의 방향이 바뀐점
• Case 1, 2에는 High_value에서 limit를 빼주었다면 Case 3, 4에서는 limit에서 low_value를 빼준다는 점.

Case 5. month_no between 6 and9

• 내부적으로는 Case6. Month_no >=6 and Month_no <=9 과 같다
• Limit이 없고 범위 값이 6~9까지 정해져 있다. 그리고 각각 '='로 닫힌 조건을 가지고 있다.
• Selectivity = (9-6)/(12/1) + 1/12 + 1/12 = 0.439..
• Cardinality = 1200 * (3/11 + 2/12) = 527

Case 10~13. 무한정 범위의 바인드 변수

• 옵티마이져는 간단하게 선택도를 5%로 설정
• Cardinality = 1200 * 0.05 = 60

Case 14~18. 한정 범위의 바인드 변수

• 옵티마이져는 간단하게 선택도를 0.25%로 설정 (0.25% = 5% *5%)
• Cardinality = 1200 * 0.0025 = 3

Case 19, Case 20.

• 경계값(user_col_statistics.high_value, user_col_statistics.low_value)을 벗어나는 조건

조건 컬럼이 두개인 쿼리