CHAPTER 7. 히스토그램

시작하면서 183 / 이창헌

높이균형 히스토그램 ( 직선적인 형태의 히스토그램 = 동일깊이(높이) )

1백만 개의 로우

 select count(normal) as ct from t1

범위 :

 select max( normal ) ,min( normal ) from t1

   34,660 ~ -32003

범위내에서 42,117개의 다른 값

select count(*)

from

   ( select distinct normal as ct from t1 )

히스토그램 구하는 두가지 방범

방범 :1

  select    tenth        as tenth,

          min(normal)  as low_val,

          max(normal)  as high_val,

          max(normal) - min(normal)  as width,

          round( 100000 / (max(normal) - min(normal) ), 2 )  as height

  from

     ( select normal, ntile(10) over( order by normal ) tenth from t1 )

group by tenth

order by tenth

분석 함수 ntile()의 over()절을 사용하여 데이터를 순서대로 분류한 후 이 정렬된 리스트를 균등한 크기의 10개 섹션으로 나눔..

10분위수의 경계 값이 각 막대의 너비를 결정한다. 각 막대의 높이는 (100,000 /너비)이며, 이것은 10분위수 내 유효 범위에서 평균적인 로우 분포를 가지게 하고 모든 막대가 같은 면적을 가지게 하는 효과가 있다.

방범 : 2

테이블 통계정보 수집 후 실행

 select

             endpoint_value                   as curr,

             lag(endpoint_value,1) over (

                    order by endpoint_number

             )                          asprev

       from

             user_tab_histograms

       where

              table_name = 'T1'

       and    column_name = 'NORMAL'

분석 함수 lag()함수는 over()절에 적절한 정렬 순서를 제공하면, 이전 로우의 값이 현재 로우에 전달된다.

select

       rownum                           tenth,

       prev                             low_val,

       curr                             high_val,

       curr - prev                      width,

       round(100000 / (curr - prev) , 2)       height

from

       (

       select

             endpoint_value                   ascurr,

             lag(endpoint_value,1) over (

                    order by endpoint_number

             )                          asprev

       from

             user_tab_histograms

       where

             table_name = 'T1'

       and    column_name = 'NORMAL'

       )

where

       prev is not null

order by

       curr

;

• 조건절이 'column=상수' 일 경우, 상수를 빈번한 값으로 간주한다. ? 오라클은 Bucket에 의한 계산을 수행한다.

• 75 가 특별한 값(빈번한 값?)이 아닐 경우, 오라클은 이 값에 해당하는 모든 로우를 선택한다.

ENDPOINT_NUMBER     ENDPOINT_VALUE
---------------          --------------
...
59                             71
60                             72
61                             73
62                             73
63                             74
64                             74
65                             75         ***
66                             76
67                             76
68                             77
69                             77
70                             78
71                             78
72                             79
73                             79
74                             80
75                             80

density(1/num_distinct가 아님)를 사용한다. density = 0.013885925
density 공식 = nonpopulart 값의 빈도의 제곱의 합 / (NULL이 아닌 로우의 개수 * NULL이 아닌 nonpopular 로우의 개수) 차후 공식 유도설명 추가
Cardinality = 3,240 * 0.013885925 = 44.99

Execution Plan (9.2.0.6 - select count(*) from t1 where skew = 75)
------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=1 Card=45 Bytes=135)

ENDPOINT_NUMBER         ENDPOINT_VALUE
---------------             --------------
8                               -120
9                                 17
10                               117
11                               251
12                               357

100과 200 사이의 값은 17,117,251의 endpoint_value를 가진 3개 Bucket에 걸쳐있다. density는 0.000177746 이다.
∴ 선택도 = (117-100)/(117-17) + (200-117)/(251-117) + 2*0.000177746 = 0.789047508
∴ 카디널러티 = 선택도 * "한 버킷 안에 있는" 로우 수 = 0.789047508 * 80 = 63.1238

Execution Plan (9.2.0.6 autotrace)
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=60 Card=63 Bytes=945)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=60 Card=63 Bytes=945)

• 범위기반 조건에서 날짜타입의 데이터를 숫자나 문자열로 비교했을 때 부정확한 카디널러티가 나타나는 원인

SQL> select     *
  2  from       t1
  3  where      n1 between 20021230 and 20030105
  4  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    15 |   345 |     3 |
|*  1 |  TABLE ACCESS FULL| T1   |    15 |   345 |     3 |
----------------------------------------------------------

    BUCKET    LOW_VAL   HIGH_VAL      WIDTH     HEIGHT
---------- ---------- ---------- ---------- ----------
        64   20020829   20020913         84      .1813
        65   20020913   20020928         15      1.015
        66   20020928   20021013         85      .1791
        67   20021013   20021028         15      1.015
        68   20021028   20021112         84      .1813
        69   20021112   20021127         15      1.015
        70   20021127   20021212         85      .1791
        71   20021212   20021227         15      1.015
        72   20021227   20030111       8884      .0017
        73   20030111   20030126         15      1.015
        74   20030126   20030210         84      .1813
        75   20030210   20030225         15      1.015
        76   20030225   20030312         87       .175
        77   20030312   20030327         15      1.015
        78   20030327   20030411         84      .1813
        79   20030411   20030426         15      1.015
        80   20030426   20030511         85      .1791
        81   20030511   20030526         15      1.015
        82   20030526   20030610         84      .1813
        83   20030610   20030625         15      1.015
        84   20030625   20030710         85      .1791

*숫자 20021227과 20030111 사이의 범위에 매우 큰 간격이 존재하는데, 데이터가 아주 얇고 넓게 퍼져있다.
그러나 이러한 정보는 인간중심적인 것이다. 따라서 오라클은 히스토그램의 도움이 없이는 정확한 데이터를 인지할 수 없다.

아래 그림을 보면(3번째) 오라클은 히스토그램 정보를 가질 때 비로소 데이터를 올바르게 인지한다.

위 실행계획에서 생성된 부정확한 카디널러티에 대한 계산을 유도해보자.(히스토그램을 생성하면 'density = 1/num_distinct'를 보장할 수 없다. 여기선 예외)
선택도 = (요청된 범위/버킷의 총 대상 범위) + 2*density
         = (20030105 - 20021230) / (20030111 - 20021227) + 2 * 0.000547345 = 1.00008
또한 이 선택도는 테이블 전체가 아니라 버킷당 로우 수(1827/120)에 해당하므로
카디널러티 = round(1.00008*15) = 15

• T1 테이블 컬럼 Type

• T1 테이블 입력 값 범위

• equal 조건 쿼리
• 모든 데이터 타입에서도 정상 처리
• 가상의 날짜 컬럼으로 데이터 정렬 시 순서 정확

• 범위기반 조건 사용 시 부정확한 카디널리티 발생

ex)   2003년 4월 1일은 2003년 3월 31일의 (날짜형) 다음날로 인식

   2003401과 20030331 비교 and '20030401'과 '20030331' 비교 시 다음 값이라 인식 못함

• 순수한 날짜(date형) 컬럼을 사용한 쿼리의 카디널리티 = 8 (교재 - 오라클9i), 5 (직접테스트 - 오라클10gR2)
• 위의 쿼리(숫자형)의 카디널리티 = 396 (교재 - 오라클9i), 327 (직접테스트 - 오라클10gR2)

☞ 120개 버킷 히스토그램 생성 시 숫자형컬럼에 의한 쿼리의 카디널리티 = 15

▼ date_oddity.sql 테스트 결과 (WinXP, Oracle 10gR2)

☞ 테스트 결과 날짜형은 히스토그램에 상관없음 (히스토그램 생성 전에 이미 잘 처리함)

    숫자형과 문자형은 히스토그램 생성 후 오라클에게 더 좋은 데이터 이미지 제공

▼ 120 버킷 히스토그램 결과 (※ 직접 테스트한 테이블 data가 교재와 차이가 있음)

* 청록색 글씨 라인 참조

12월에서 1월로 넘어가는 범위에 매우 큰 Gap 발생 (데이터가 얇고 넓게 퍼짐)

사람은 1년 간격을 인지하기 때문에 당연히 이해(오라클이 날짜형을 이 처럼 논리적 판단함)

[68번 Bucket] *  교재 p.214

20021028 ~ 20021112 범위는 실제로 14일의 날짜를 의미하지만 오라클은 84개(20021112-20021028)로 인식

(실제 테이블에서 14개의 로우 차지)

옵티마이저는 표준공식을 사용하여 선택도를 계산하지만, 버킷의 endpoint 값 사용

between 절을 사용했으므로 범위의 양쪽 끝에 보정 팩터(correction factor) 사용

과거, 보정 팩터로 1/num_distinct 사용하기에 density 필수 사용에 큰 비중 없음

그러나 실제 필요항목은 density

(참고) 이번 예제의  density = 1/num_distinct 성립 => density =  1/1,827 = 0.000547345

위 선택도는 테이블 전체가 아닌 관련된 버킷의 로우 개수에만 적용

버킷 당 1,827 / 120 = 15 Rows   ( 전체 로우 개수 / 관련 버킷 로우 개수)

∴ round(15*1.0008) = 15

※ 히스토그램은 옵티마이저에게 엄청난 차이를 제공

☞ 완전히 편평하지 않은 비연속적인 그래프의 데이터 분포를 가진 컬럼을 where 절에 사용할 시 히스토그램 필요

Null 사용을 피하고자 특별한 값 (Date형 컬럼에 '4000-12-31' ) 사용

☞ 옵티마이저가 날짜기반의 계산식을 정확하게 처리

    범위 기반의 쿼리에 문제 발생

    ∵ 표준 공식에 전혀 비현실적인 high value를 대입

[상황]

테이블의 데이터 범위 : '2000-01-01' ~ '2004-12-31'               ▶ 5년 범위

where date_closed between to_date('01-Jan-2003','dd-mon-yyyy')   ▶ 1년 범위

                      and to_date('31-DEC-2003','dd-mon-yyyy')

[예상]

사용자 : 1 / 5 (5년 중 1년 범위)에 대해 작업할 것으로 예상

오라클 : 상위값 '4000-12-31' 값을 알기에 약 3 / 2000 의 선택도 산출

[선택도]

= (2003년 12월 31일 - 2003년 1월 1일) / (4000년 12월 31일 - 2000년 1월 1일) + 2 / 1828

= 0.00159

▼ 연별 두개의 버킷과 하나의 여분을 포함한 11개 버킷의 히스토그램 생성

[히스토그램 생성 후 결과]

선택도와 카디널리티 정확한 계산

데이터 집합 : 일별 100개의 로우, 그 중 1% 데이터만 '4000-12-31'로 설정

히스토그램 일반적 처리

앞쪽의 4.5년에 대해서 일별로 대략 100개의 표현

마지막 버킷은 매우 넓은 범위에 걸쳐 일별로 매우 작은 개수(0.0028)의 로우를 관리하는 방식으로 특이한 값('4000-12-31') 관리

☞ 앞쪽의 4년에 대한 쿼리는 매우 정확한 카디널리티 추정치 계산

    마지막 6개월('2004-07-20') 이후)에 대해서만 잘못된 수치 계산

[의의]

더 많은 버킷을 생성함으로써 오류 발생 여지 감소

[결론]

히스토그램은 몇가지 빈번한 값을 가진 데이터만 아니라

비규칙 적인 데이터에 대해서 CBO가 알도록하여

잘못된 계산 발생 여지를 감소하는데 있음