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);

최대/최소값의 범위 실험

* 기본자료: 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

• 계산상의 오차

• 옵티마이저의 반올림 선택에 대한 코드상의 변화

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

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

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

요약

/ 위충환

테스트 스크립트

/ 위충환