by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

3. 단일 테이블 선택도




단일 테이블 선택도

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

Cardinality = Base Cardinality * Selectivity

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

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

?

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

1200명의 청중을 대상으로 이 중에 몇 명이 12월에 태어났겠느냐
물었을 때 CBO적으로 답을 구하는 행동
 
select count(*)
from auudience 청중
where month_no = 12
 
1년은 12달이다 알려진 사실        
생일은(아마도) 년 중 고르게 분산되어 있을것이다. 가정 density or 1/NDV user_tab_col_statistics.num_distinct = 12
청중의 1/12는 어느 특정 월에 태어났을 것이다. 월의 선택도 selectivity user_tab_col_statistics.num_density = 1/12
user_ab_histogram=popular histogram 같은 분포를 가진다.
(차라리 히스토그램이 없어서 1/ndv를 사용했다라고하자)
어느 특정 월을 요청하였다. 조건 predicate month_no=12
요청한 월은 실제로 달력에 존재한다. 경계체크   month_no=12는 user_tab_col_statistics의 low_value와 high_vlaue사이에 있다.
user_tab_col_statistics.num_null = 0
청중은 1200명이다. 기본 카디널리티 base cardinality user_tables.num_rows=1200
정답은 1200명의 1/12/인 100명이다. 계산된 카디날리티 adjust cardinality count= 100
//온라인코드집(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
density or 1/NDV user_tab_col_statistics.num_distinct = 12
selectivity user_tab_col_statistics.num_density = 1/12
user_tab_histogram=popular histogram 같은 분포를 가진다.
(차라리 히스토그램이 없어서 1/ndv를 사용했다라고하자)
predicate month_no=12
num_nulls  month_no=12는 user_tab_col_statistics의 low_value와 high_vlaue사이에 있다.
user_tab_col_statistics.num_nulls = 0 -> user_tab_col_statistics.num_null = 120
base cardinality user_tables.num_rows=1200

Base Selectivity: 1/12
Num_Nulls = 120

"조정된 Cardinality"

Adjust Selectivity: Base Selectivity * (num_rows-num_nulls)/num_ros
Adjust Selectivity: (1/12) * ((1200-120)/1200) = 0.075
Adjust Cardinality : 0.075 * 1200 = 90

NULL값이 영향을 주어 조정된 값이 도출된다.

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수가 너무 비현실적이다.
"리터럴을 사용한 범위검색 시의 비공식적인 알고리즘"

Selectivity = '요청한 범위' / '전체 대상 범위'

  • 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)을 벗어나는 조건

조건 컬럼이 두개인 쿼리

조건이 두개인 쿼리의 일반적인 선택도 계산방법"
  1. (조건 and 조건)의 선택도 = (조건1)의 선택도 * (조건2)의 선택도
  2. (조건 or 조건)의 선택도 = (조건1)의 선택도 + (조건2)의 선택도 - ((조건1)의 선택도 * (조건2)의 선택도)
  3. (not 조건1)의 선택도 = 1 - (조건1)의 선택도

"※ 오류의 가능성"
  1. 컬럼의 Distinct값의 개수가 적을 때, 이런 추정치가 크게 벗어날 수 있음
  2. 두 개의 조건절이 완전히 독립적이라는 가정을 출발점으로 삼기 때문에 종속된 컬럼이 있을 수밖에 없는 현실로써는 엉터리 결과를 내놓는 경우가 많다.
  3. 히스토그램을 생성했을때 Density의 변경으로 인한 Selectivity 변화를 미리 감지하기 힘들다
}

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.