비용기반의 오라클 원리 (2009년)
단일 테이블 선택도 0 0 65,174

by 구루비스터디 Cardinality 선택도 [2018.09.27]


단일 테이블 선택도

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


핵심 공식!!

Cardinality = Base Cardinality * Selectivity


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

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


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


1200명의 청중을 대상으로 이 중에 몇 명이 12월에 태어났겠느냐
물었을 때 CBO적으로 답을 구하는 행동
{code:sql}select count(*)
from auudience 청중
where month_no = 12{code}
1년은 12달이다알려진 사실
생일은(아마도) 년 중 고르게 분산되어 있을것이다.가정density or 1/NDVuser_tab_col_statistics.num_distinct = 12
청중의 1/12는 어느 특정 월에 태어났을 것이다.월의 선택도selectivityuser_tab_col_statistics.num_density = 1/12
user_ab_histogram=popular histogram 같은 분포를 가진다.
(차라리 히스토그램이 없어서 1/ndv를 사용했다라고하자)
어느 특정 월을 요청하였다.조건predicatemonth_no=12
요청한 월은 실제로 달력에 존재한다.경계체크month_no=12는 user_tab_col_statistics의 low_value와 high_vlaue사이에 있다.
user_tab_col_statistics.num_null = 0
청중은 1200명이다.기본 카디널리티base cardinalityuser_tables.num_rows=1200
정답은 1200명의 1/12/인 100명이다.계산된 카디날리티adjust cardinalitycount(*)= 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/NDVuser_tab_col_statistics.num_distinct = 12
selectivityuser_tab_col_statistics.num_density = 1/12
user_tab_histogram=popular histogram 같은 분포를 가진다.
(차라리 히스토그램이 없어서 1/ndv를 사용했다라고하자)
predicatemonth_no=12
num_nullsmonth_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 cardinalityuser_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 변화를 미리 감지하기 힘들다
    {warning:}
"코어 오라클 데이터베이스 스터디모임" 에서 2009년에 "비용기반의 오라클 원리 " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3976

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입