3. 단일 테이블 선택도

조회 수 3628 추천 수 0 2010.01.20 11:47:38
ZoneWorker2 *.166.73.136

CHAPTER 3. 단일 테이블 선택도

 발표/총정리 :  위충환


시작하면서 / 위충환


? 1,200 명의 청중 중 12월 생일자의 수는 ?  CBO 행동 → 약 100명

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


     Execution Plan (9.2.0.6)
     ----------------------------------------------------------
        0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=3)
        1    0   SORT (AGGREGATE)
        2    1     TABLE ACCESS (FULL) OF 'AUDIENCE' (Cost=2 Card=100 Bytes=300)      


? 데이터 딕셔너리에 저장 된 통계 정보(user_tab_col_statistics)

     select   column_name,   num_distinct,   density

     from     user_tab_col_statistics

     where   table_name = 'AUDIENCE';

 

     COLUMN_NAME                        NUM_DISTINCT                            DENSITY

                                                    

     MONTH_NO                                               12                           .083333333        


              - num_distinct : null을 제외한 distinct값 개수
              - density : 쿼리에 의해 반활 될 것으로 예상되는 데이터의 비율, null값을 가진 로우 비율 계산 무시


? 컬럼에 히스토그램이 없을 시   density = 1 / num_distinct


? 히스토그램 생성 시 위의 식이 항상 일치하지 않음.

? 적절한 히스토그램 존재 시, 오라클 버전에 따라 조금씩 다른 방식으로 작동

  • 라클 10g 옵티마이저

      - 적절한 히스토그램이 없을 시, num_distinct 컬럼을 사용하여    

        카디널리티 = num_rows / num_distinct 계산


      - 적절한 히스토그램이 존재 시, density 컬럼을 사용하여            

        카디널리티 = num_rows * density 계산

  • 오라클 8i : 항상 density 사용.
  • 오라클 9i : shared pool을 flush 시 오라클 10g와 동일.

NULL 값 처리 54 / 위충환


? 1,200 명의 청중 중 10%가 자신의 생일을 기억하지 못 할 경우

  12월 생일자라는 확신하는 청중의 수는 ?  CBO 행동 → 약 90명

  • 기본 선택도 = 1 / 12   (density 또는 1 / num_distinct 이용)
  • num_nulls = 120
  • num_rows = 1200
  • 조정된 선택도 = 기본 선택도 * (num_rows - num_nulls) / num_rows
                                   = (1 / 12) * ((1200 - 120) / 1200) = 0.075
  • 조정된 카디널리티 = 조정된 선택도 * num_rows

                                    = 0.075 * 1200 = 90


      Execution Plan
      ----------------------------------------------------------
         0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=3)
         1    0   SORT (AGGREGATE)
         2    1     TABLE ACCESS (FULL) OF 'AUDIENCE' (Cost=2 Card=90 Bytes=270)      



IN-LIST 처리방식



select count(*) from audience where month_no in (1,2,3);

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=3)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'AUDIENCE' (Cost=2 Card=300 Bytes=900)


  • 9i,10g  -> 카디널리티 300
  • 8i -> 276
  • 이유

계산결과가 잘못된 경우

업그레이드에 따라 계산 방식이 변경


※ In-list 오류

month_no in (6,7,8)


옵티마이져는 month_no=6 or month_no=7 or month_no=8 로변환

8i에도 use_concat 힌트를 추가하면 union all형태로 변환 -> 8i도 결과가 동일하게 나타남 (300)


-> 8i의 다중접속 알고리즘의 오류로 인해 잘못된 결과가 나타남



DISTINCT 값이 1000개

* ROW 12,000개

 

select count(*) from audience where month_no in (1,2);
select count(*) from audience where month_no in (1,2,3);
select count(*) from audience where month_no in (1,2,3,4);
select count(*) from audience where month_no in (1,2,3,4,5);
select count(*) from audience where month_no in (1,2,3,4,5,6);
select count(*) from audience where month_no in (1,2,3,4,5,6,7);
select count(*) from audience where month_no in (1,2,3,4,5,6,7,8);
select count(*) from audience where month_no in (1,2,3,4,5,6,7,8,9);
select count(*) from audience where month_no in (1,2,3,4,5,6,7,8,9,10);
select count(*) from audience where month_no in (1,2,3,4,5,6,7,8,9,10,11);
select count(*) from audience where month_no in (1,2,3,4,5,6,7,8,9,10,11,12);
select count(*) from audience where month_no in (1,2,3,4,5,6,7,8,9,10,11,12,13);
select count(*) from audience where month_no in (1,2,3,4,5,6,7,8,9,10,11,12,13,14);



In-List 항목의 개수
12개의 distinct값 8i(9i,10g)
1000개의 dirsinct 값 8i(9i,10g)
1
100(100)
12(12)
2
192(200)
24(24)
3
276(300) 36(36)
4
353(400) 48(48)
5
424(500) 60(60)
6
489(600) 72(72)
7
548(700) 84(84)
8
602(800) 96(96)
9
652(900) 108(108)
10
698(1000) 120(120)
11
740(1100) 132(132)
12
778(1200) 144(144)
13
813(1200) 156(156)
14
846(1200) 167(168)
30
1112(1200) 355(360)

* in-list 항목의 개수가 적은데도 카디널리티 오차가 크게 발생




최대/최소값의 범위 실험




8i
9i,10g(10.1.0.4)
10g (10.2.0.1)
설명 (8i,9i)
where month_no=25 100
100
1
최대/최소값을 벗어나는지 인식못함
where month_no in (4,4) 100
100
100
중복값을 인식
where month_no in (3,25) 192
200
100
최대/최소값을 벗어나는지 인식못함
where month_no in (3,25,26) 276
300
101
최대/최소값을 벗어나는지 인식못함

where month_no in (3,25,25,26)

276
300
101
최대최소 구분못하지만 중복 구분
where month_no in (3,25,null) 276
300
200
null도 같이 계산
where month_no in (:b1,:b2,:b3) 276
300
300
상수일때의 카디널리티 사용


* 기본자료: 1 부터 12까지 / 1200개의 row를 가짐




NOT IN에 대한 실험



select count(*) from audience where month_no not in (1,2);
select count(*) from audience where month_no in (1,2);


  • 8i 일경우 카디널리티 1,200
  • 9i  1008   + 200 = 1208
  • 10g 1008   + 200 = 1208


10g 업데이트 61


  • CBO의 변화에 의해 10g 10.1.0.2 와 10.2 버젼의 결과가 다르다.
  • 10.2의 예측방법은 최대값에서 최소값을 뺀값 (12-1=11) 을 범위로
  • 최소값-11 / 최대값 +11 로 범위를 예측 한후
  • -11에서 1까지 증가하다가 12까지 평행 다시  23까지하향하여 좌우 각 값이 0으로 하여 계산한다.
  • 참고) 통계정보가 최신으로 유지되지 않을경우 원하지 않는 극적인 결과가 나타날수 있음



범위 기반 검색조건 62 /이태경

Case Predicate Card(8i) Card(9i /10g) 조건
1
month_no > 8 437 436 Unbounded, open
2
month_no >= 8 537 536 Unbounded, closed
3
month_no < 8 764 764 Unbounded, open
4
month_no <= 8 864 864 Unbounded, closed
5
month_no between 6 and 9 528 527 Bounded, closed, closed
6
month_no >= 6 and month_no <= 9 528 527 Bounded, closed, closed
7
month_no >= 6 and month_no < 9 428 427 Bounded, closed, open
8
month_no > 6 and month_no <= 9 428 427 Bounded, open, closed
9
month_no > 6 and month_no < 9 328 327 Bounded, open, open
10
month_no > :b1 60 60 Unbounded, open
11
month_no >= :b1 60 60 Unbounded, closed
12
month_no < :b1 60 60 Unbounded, open
13
month_no <= :b1 60 60 Unbounded, closed
14
month_no between :b1 and :b2 4 3 Bounded, closed, closed
15
month_no >= :b1 and month_no <= :b2 4 3 Bounded, closed, closed
16
month_no >= :b1 and month_no < :b2 4 3 Bounded, closed, open
17
month_no > :b1 and month_no < :b2 4 3 Bounded, open, open
18
month_no > :b1 and month_no <= :b2 4 3 Bounded, open, closed
19
month_no > 12 100 100 Unbounded, open
20
month_no between 25 and 30 100 100 Bounded, closed, closed

범위 기반 조건에 대한(8i, 9i/10g) 카디널리티 변화의 원인
  • 계산상의 오차
            진수 변환 과정에서 소수점 15번째 자리 뒤의 숫자에 대한 올림(8i)과 반올림(9i/10g)
  • 옵티마이저의 반올림 선택에 대한 코드상의 변화
        ?뭘까
카디널리티(이하 Card) 오류에 따른 부작용
  • 결과집합이 작을수록 실행계획에 더 심각한 영향을 준다.

범위에 따른 Card의 패턴
  • 리터럴(리터럴이란 column명, alias가 아닌 SELECT리스트에 포함된 문자,표현식 또는 숫자.) 사용
        ? 열린 조건과 닫힌 조건일 때의 Card 차이 = 100
  • 컬럼값의 범위를 벗어날 경우
        ? Card가 100으로 고정
  • 바인드 변수 사용
  1. 열린 조건과 닫힌 조건일 때의 Card 차이가 없다.
  2. 현실적이 못하다. (∵ 개별값 검색이 범위 검색보다 많은 로우를 리턴)

옵티마이저의 Card 계산식 추론(조건절의 변수 종류에 따른)
  • 바인드 변수
  1. 무한정 범위의 바인드 변수 ? 선택도를 5% (0.05 * 1200 = 60)
  2. 한정 범위의 바인드 변수 ? 선택도 0.25% (0.0025 * 1200 = 3)  Q)왜 5%의 5%로?
  3. 데이터 범위를 벗어남 ? 'column = 상수' 조건에 해당하는 Card
  • 리터럴 값
  1. 무한정 And 열린 범위 ? 해당범위/전체범위 * 총 Row 수
  2. 무한정 And 닫힌 범위 ? (해당범위/전체범위 + 1/num_distinct) * 총 Row 수
  3. between 양쪽 닫힌 범위 ? (해당범위/전체범위 + 1/num_distinct + 1/num_distinct) * 총 Row 수

전체 범위 사용시 주의
  • 범위를 벗어나는 데이터 삽입시 전체 범위에 영향을 주며 이것은 선택도의 오류를 유발한다.

바인드 변수 PEEKING

10g 업데이트 67

/ 이태경

  • (경계값을 벗어난 경우) 가능한 값의 범위에서 멀어질 경우 Card는 100을 사용하는 대신 점점 작아진다.(표 3-5)
Predicate Cardinality(10.1.0.2) Cardinality(10.1.0.4)
month_no between 6 and 9 527 527
month_no between 14 and 17 100 82
month_no between 18 and 21 100 45
month_no between 24 and 27 100 1



조건절이 두 개일 때의 처리방식 68

/ 박우창

조건절이 두 개일때의 처리 방식

? (사례) month_no가 (1,12)의 정수값을 가질때 1,200개의 튜플을 가진 테이블에서

조건 where month_no > 8 or month_no <=8 일때 카디널리티는?

(추정)

1. 1301 = 437(month_no > 8 의 추정값) + 864(month_no <= 8 의 추정값)

2. 1200 = (전체값 범위이므로)

3. 986 = (실제 수행해본 값)

? (조건절에서의 선택도 계산 방법)

- 조건1 and 조건2 = 조건1의 선택도 * 조건2의 선택도

- 조건1 or 조건 2 = 조건1의 선택도 + 조건2의 선택도 -조건1과조건2의 선택도

- not 조건1 = 1 - 조건1의 선택도

? month_no between :b1 and :b2의 선택도는 왜 0.0025 인가?

- 두 개의 조건을 AND로 처리하기 때문이다.

- month_no > :b1 의 선택도를 0.05로 처리한다(이유 없음?)

- not (column > :b1) 는 0.95가 아니라 0.05이다.

- not (month_no between :b1 and :b2) => (month_no > :b1 or month_no < :b2)

= 0.05 + 0.05 - 공통부분(:b1과 :b2 사이 값들)


다중 조건일 때의 문제점 71

/ 이창헌

옵티마이저는 사람과 다르다.
옵티마이저는 코드(공식)에 따라 계산할 뿐, 주어진 상황을 이해하지는 못한다.

 

   테이블 내 1,200개 로우를 100%완벽하게 만족하는 조건을 사용=>986의 카디널리티를 계산

    where month_no > 8  --조건1(P1)
       or month_no <= 8 --조건2(p2)

 

    1.단일 선택도 공식 = (요청한 범위/전체 범위)
      조건1 :  (12-8)/(12-1) = 4/11 = 0.363636

      조건2 :  (8-1)/(12-1)+1/12 = 7/11+1/12 = 0.719696

 

    2.( p1 or p2 ) 선택도 = (p1의 선택도) + (p2의 선택도) - ( p1 and p2의 선택도)
            0.363636 + 0.719696 - (0.363636 *  0.719696 ) = 0.8216


    3. 0.8216 * 1200 = 986


  카디널리티가 부정확 =>  부적절한 실행계획


요약

/ 위충환


테스트 스크립트

/ 위충환