7. 히스토그램

조회 수 8234 추천 수 0 2010.02.02 07:41:03
ZoneWorker2 *.222.143.186

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

 

TENTH

LOW_VAL

HIGH_VAL

WIDTH

HEIGHT

1

-32003

-8966

23037

4.34

2

-8966

-5883

3083

32.44

3

-5883

-3659

2224

44.96

4

-3659

-1761

1898

52.69

5

-1761

17

1778

56.24

6

17

1792

1775

56.34

7

1792

3678

1886

53.02

8

3678

5897

2219

45.07

9

5897

8974

3077

32.5

10

8974

34660

25686

3.89

 

분석 함수 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'

 

CURR

PREV

32003

 

-8966

32003

-5883

-8966

-3659

-5883

-1761

-3659

17

-1761

1792

17

3678

1792

5897

3678

8974

5897

34660

8974

 

분석 함수 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

;

 

 

TENTH

LOW_VAL

HIGH_VAL

WIDTH

HEIGHT

1

-32003

-8966

23037

4.34

2

-8966

-5883

3083

32.44

3

-5883

-3659

2224

44.96

4

-3659

-1761

1898

52.69

5

-1761

17

1778

56.24

6

17

1792

1775

56.34

7

1792

3678

1886

53.02

8

3678

5897

2219

45.07

9

5897

8974

3077

32.5

10

8974

34660

25686

3.89

 

 

일반적인 히스토그램 190 / 이창헌

 

균일하지 않은 데이터 분포에 대한 선택도와 카디널리티 계산을 향상

 

도수분포 히스토그램(Frequency histogram) : 255 distinct값을 가진 데이터집합(테이블)

높이균형 히스토그램(Height balanced histogram) : 다수의 distinct값을 가진 데이터집합(테이블)

 

user_tab_histograms, user_part_histograms, user_subpart_histograms : 단순한 숫자 쌍으로 존재


히스토그램과 바인드 변수 191 / 이창헌

    

 column = 상수, column = :바인드_변수 : 히스토그램을 통해 구해진 density를 이용( user_tab_columns.desity)

 

 

  바인드 변수 peeking

-   문장이 최적화될 때 마다, 오라클은 바이드 변수의 실제 값을 확인하고 이 특정 값에 대해서 문장을 최적화 한다.

 

  커서 공유 : cursor_sharing 파라미터의 사용,

     설정값 :

  ‘force  : 리터럴 상수를 시스템이 생성산 바인드 변수로 변경함. 바인드 변수로의 변환 때문에, cursor_sharing

                  히스토그램의 유효성에 큰 영향을 미친다.

 

해결책 :

 

1.    /*+ cursor_sharing_exact */힌트를 추가하여 오라클에게 해당 문장의 리터럴 상수를 바인드 변수로 바꾸지 말라고 얘기함.

 

2 . cursor_sharing = similar 로 설정

    오라클은 먼저 리터럴 상수를 바인드 변수로 바꾸고 나서 바인드 변수를 peeking 할지 결정(변수의 값이

     실행계획에 차이를 만든다면 오라클이 다시 최적화할 것이다)

 

      두가지 경우에 최적화가 다시 발생

    2-1. 조건절의 어느 하나라도 범위 조회와 관련된 겨우

    2-2. 단순한 equal조건일지라도 조건절에 나타난 컬럼에 히스토그램이 존재함.

 

     Similar 문제점 : 최적화에 필요한 리소스와 더블어 경합이 증가함.



오라클은 언제 히스토그램을 무시하는가? 193 / 이창헌

 

1.       히스트그램과 조인

오라클은 아주 명백한 입력 값이 존재하 때만 히스토그램을 완전하게 사용함.

 

Select

              T1.v1, t2.v1

From t1, t1

Where t1.n2 = 99  <= 오라클이 t1.n299인 로우에 대해서 조인 컬럼 값 범위와 빈도를 알수 없다.

   And t1.n1 = t2.n1 <= 컬럼에 히스토그램이 존재함

 

2.       오라클은 로컬 쿼리 or 리모트 쿼리(단일 사인트를 가진)만으로 작업을 수행하는 경우, 오라클은 히스토그램을 사용한다.

 

도수분포 히스토그램 197  / 박우창
? 도수분포히스토그램 - 255개 미만의 소수의 distince 값을 가진 데이터

? 예제 테이블 t1(총 3240개 튜플)(c_skew_freq.sql)

Name

Type


SKEW

NUMBER

1=<x=<80, x가 x가 x개씩 있음

PADDING

VARCHAR2(400)

‘x'

         인덱스 : create index t1_i1 on t1(skew);

? 예제에 테이블 t1.skew 속성 대한 user_tab_histogram 테이블 검색 결과

   (ENDPOINT_VALUE에 대한 누적 개수가 ENDPOINT_NUMBER에 나타남)

ENDPOINT_NUMBER

ENDPOINT_VALUE

1

1

3

2

6

3

10

4

15

5

21

6

...

...

3081

78

3160

79

3240

80

 

? 카디널리티 예측 비교표

조건

설명

CBO

실제

skew = 40

Column=상수

40

40

skew = 40.5

Column = 범위안의 존재하지 않는 값

1

0

skew between 21 and 24

범위내 Between

90

90

skew between 20.5 and 24

범위내 Between

90

90

skew between 1 and 2

Between range (시작점)

3

3

skew between 79 and 80

Between range (끝점)

159

159

skew > 4 and skew < 8

< 과 >

18

18

skew = -10

최소값 미만

1

0

skew = 100

최대값 초과

1

0

skew between ?.5 and ?.3

최소값 미만 범위조건

1

0

skew between 92 and 94

최대값 초과 범위 조건

1

0

skew between 79 and 82

경계값 걸쳐있음

159

159

skew = :b1

Column = :bind

41

???

skew between :b1 and :b2

Column between :bind1 and :bind

8

???

 

* 범위조건은 0.25%를 사용(8), 바인드변수는 num_rows/num_distincts(41)

? 고려사항

- 통계정보를 사용할 때 버켓개수는 254(최대값)을 사용하는 것이 좋다.

- 데이터값이 변경되면 히스토그램은 재생성할 필요가 있다.

- 히스토그램은 바인드변수에는 영향이 없다.

   바인드 변수는 1/num_distinct로 계산된다. 범위는 5%와 0.25% 규칙을 사용한다.

- 히스토그램이 있으면 CBO는 범위조건이 low/high를 벗어날 때 잘 처리한다.


도수분포 히스토그램 조작하기 201 / 박우창

? 문제 : dbms_stats는 analyze 명령어보다 더 많은 버킷이 필요하다. 254개 distinct 값이 있을 때

    analyze 명령어를 사용하지않고 어떻게 히스토그램을 사용할 것인가?

  (조작 알고리즘)

   컬럼과 카운트 수집 -> 지역변수에 저장 -> 수정 -> set_column_stats 호출

? 통계정보 조작할 경우 사용자는 데이터 특징을 옵티마이저보다 더 잘 알고있어야한다.

- 사례1 : 100만개중 10만개에만 관심있을 때 10만개에 대해서만 도수분포를 생성하고 나머지는 NULL로 처리

- 사례2 : 400개의 서로 다른 값을 가진 속성이 있다면 빈번히 사용되는 254개에 대하여 히스토그램을 생성한다.


 


통계정보 조작 시 주의사항 202 /남송휘


  • 히스토그램은 각각의 값이 얼마나 많은 로우를 갖는지에 대한 목록을 작성함으로서 데이터를 묘사하며
  • 이 로우 카운트를 테이블에 대한 중요한 값에 맞춰서 조정
  • 도수분포 히스토그램의 endpoint_number 컬럼에 나타나는
  • 최대 누적값은 테이블의 로우 개수에서 컬럼의 null 개수를 뺀것과일치해야하며
  • 그렇지 않으면 오라클은 카디널리트를 적절히 조정
  • X개의 로우가 density * (user_tables.num_rows - user_tab_columns.num_nulls)보다 작으면 , 이중에서 더 큰값을 사용


9.2.0.4

SQL> @fake_hist.sql


  NUM_ROWS
----------
    300000

1 row selected.


COLUMN_NAME           NUM_NULLS    DENSITY
-------------------- ---------- ----------
ID                            0 3.3333E-06
CURRENCY_ID                   0 1.6667E-06
SMALL_VC                      0 3.3333E-06

3 rows selected.


ENDPOINT_VALUE ENDPOINT_NUMBER
-------------- ---------------
             1          256000
             2          281000
             3          286000
             4          290000
             5          293000
             6          295000
             7          297000
             8          298000
             9          299000
            10          300000

10 rows selected.

Baseline:
num_rows                300,000 rows
Max endpoint_number     300,000
density                 1/600,000 (0.000016667)
num_nulls               0
histogram entry         1,000
Should see CARD = 1000

select  *
from t1
where
    currency_id = 10;



Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=97 Card=1000 Bytes=18000)
   1    0   TABLE ACCESS (FULL) OF 'T1' (Cost=97 Card=1000 Bytes=18000)



user_tables.num_rows를 150,000로 변경


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=97 Card=500 Bytes=9000)
   1    0   TABLE ACCESS (FULL) OF 'T1' (Cost=97 Card=500 Bytes=9000)


user_tab_columns.num_nulls를 200,000로 변경

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=97 Card=333 Bytes=5994)
   1    0   TABLE ACCESS (FULL) OF 'T1' (Cost=97 Card=333 Bytes=5994)



user_tab_colums.density를 0.01로 변경

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=97 Card=3000 Bytes=54000)
   1    0   TABLE ACCESS (FULL) OF 'T1' (Cost=97 Card=3000 Bytes=54000)








높이 균형 히스토그램




SQL> @c_skew_ht_01.sql


10.2.0.1


NUM_DISTINCT    DENSITY NUM_BUCKETS
------------ ---------- -----------
          80 .016563786          75

1 row selected.


9.2.0.4
NUM_DISTINCT    DENSITY NUM_BUCKETS
------------ ---------- -----------
          80 .013885925          58


※ 버킷의 갯수가 버젼별로 다르게 나타남



ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
              0              1
              1              9
              2             13
              3             16
              4             19
              5             21
              6             23
              7             25
              8             26
              9             28
             10             29
             11             31
             12             32
             13             33
             14             35
             15             36
             16             37
             17             38
             18             39
             19             40
             20             41

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
             21             43
             23             44
             24             45
             25             46
             26             47
             27             48
             28             49
             29             50
             30             51
             32             52
             33             53
             34             54
             35             55
             37             56
             38             57
             39             58
             41             59
             42             60
             43             61
             45             62
             46             63

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
             48             64
             49             65
             51             66
             52             67
             54             68
             56             69
             57             70
             59             71
             60             72
             62             73
             64             74
             66             75
             67             76
             69             77
             71             78
             73             79
             75             80

59 rows selected.


※ endpoint_number에 생략된 로우는 전후 로우를 통해 오라클이 추정하여 압축



equality on a popular value - uses bucket counts

Execution Plan
----------------------------------------------------------
Plan hash value: 2432955788

-----------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |     3 |     1 |
|   1 |  SORT AGGREGATE   |       |     1 |     3 |       |
|*  2 |   INDEX RANGE SCAN| T1_I1 |    86 |   258 |     1 |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SKEW"=77)

Note
-----
   - cpu costing is off (consider enabling it)

equality on a non-popular value - uses density

Execution Plan
----------------------------------------------------------
Plan hash value: 2432955788

-----------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |     3 |     1 |
|   1 |  SORT AGGREGATE   |       |     1 |     3 |       |
|*  2 |   INDEX RANGE SCAN| T1_I1 |    86 |   258 |     1 |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SKEW"=75)

Note
-----
   - cpu costing is off (consider enabling it)

SQL>



  • endpoint 목록이 압축되지 않은 상태에서 값이 반복해서 나타날때 그것을 '빈번한값'으로 인식
  • 어떤 개수의 버킷을 사용해도 모든 빈번한 값을 잡아낼수는 없음
    (버킷은 최대 254개(약 1/250, 0.4% 정도) 이며 만약 250개 이상의 특별한 값을 가진다면 이중일부는 히스토그램에서 놓침)
  • 한 로우가 거의  두개의 버킷에 걸쳐 있을 정도로 많아도 오라클이 빈번한 값으로 인식하지 못할수도 있음




계산  208 / 이태경



    높이균형 히스토그램을 이용한 카디널리티 계산의 3가지 전략

  • 조건절이 'column=상수' 일 경우, 상수를 빈번한 값으로 간주한다. ? 오라클은 Bucket에 의한 계산을 수행한다.
ENDPOINT_NUMBER     ENDPOINT_VALUE
---------------         --------------
...
59                            71
60                            72
62                            73
64                            74
65                            75
67                            76
69                            77
71                            78
73                            79
75                            80

77 이 endpoint_number 69 에 나타나고, 바로 앞의 76 이 endpoint_number 67 에 나타난다. ? 77을 Bucket 75개 중 2개에 걸쳐있는 빈번한 값으로 간주한다.
∴ 77의 선택도 = 2/75, Cardinality = 3,240 * 2/75 = 86.4

Execution Plan (9.2.0.6 - select count(*) from t1 where skew = 77)
------------------------------------------------------------------
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=86 Bytes=258)
  • 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)
  • 범위 조회

1. 빈번한 값이 없는 Bucket에 대해서 범위조회(between 100 and 200)
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)
2. 빈번한 값을 가진 Bucket에 대해서 범위조회(between 400 and 600)
ENDPOINT_NUMBER          ENDPOINT_VALUE
---------------               --------------
12                                 357
13                                 450
19                                 500 *** 돌출부분
20                                 520
21                                 598
22                                 670

선택도 = 범위 선택도 + N개(범위에 완전히 걸치는 Bucket의 갯수)의 완전한 Bucket을 위한 선택도
         = (450 - 400) / (450 - 357) + (600 - 598) / (670 - 598) + 2 * 0.000177746 + 8
         = 0.565738 + 8 = 8.565738

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

데이터 문제 다시 생각하기 212 / 이태경



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

부적절한 데이터 타입 212  / 위충환

  • T1 테이블 컬럼 Type

d1   Date            -- 오라클 날짜

n1   Number(8)   -- yyyymmdd   

v1   Varchar2(8)  --'yyyymmdd'


  • T1 테이블 입력 값 범위

select min(d1), max(d1) from t1;
MIN(D1)  MAX(D1)

-------- --------
99/11/01 04/10/31


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


  • 범위기반 조건 사용 시 부정확한 카디널리티 발생
          ∵ 숫자와 문자 버전 정보는 내부적으로 큰 값 차이


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

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


where n1 between 20021230 and 20030105
▲ 7일치의 결과를 나타는 쿼리

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

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



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

컬럼

타입

120 버킷 히스토그램 생성 전

120 버킷 히스토그램 생성 후

날짜


 where   d1 between to_date('30-Dec-2002','dd-mon-yyyy')
                and to_date('05-Jan-2003','dd-mon-yyyy')

 Execution Plan
 -----------------------------------------------------------
 | Id  | Operation          | Name | Rows  | Bytes | Cost  |
 -----------------------------------------------------------
 |   0 | SELECT STATEMENT   |      |     5 |   115 |     3 |
 |*  1 |  FILTER            |      |       |       |       |
 |*  2 |   TABLE ACCESS FULL| T1   |     5 |   115 |     3 |
 -----------------------------------------------------------
 Predicate Information (identified by operation id):
 ---------------------------------------------------
    1 - filter(TO_DATE('30-Dec-2002','dd-mon-yyyy')

            <= TO_DATE('05-Jan-2003','dd-mon-yyyy'))
    2 - filter("D1">=TO_DATE('30-Dec-2002','dd-mon-yyyy')

           AND "D1"<=TO_DATE('05-Jan-2003','dd-mon-yyyy'))

 where  d1 between to_date('30-Dec-2002','dd-mon-yyyy')

               and to_date('05-Jan-2003','dd-mon-yyyy')

 Execution Plan
 -----------------------------------------------------------
 | Id  | Operation          | Name | Rows  | Bytes | Cost  |
 -----------------------------------------------------------
 |   0 | SELECT STATEMENT   |      |     5 |   115 |     3 |
 |*  1 |  FILTER            |      |       |       |       |
 |*  2 |   TABLE ACCESS FULL| T1   |     5 |   115 |     3 |
 -----------------------------------------------------------
 Predicate Information (identified by operation id):
 ---------------------------------------------------

    1 - filter(TO_DATE('30-Dec-2002','dd-mon-yyyy')

            <= TO_DATE('05-Jan-2003','dd-mon-yyyy'))

    2 - filter("D1">=TO_DATE('30-Dec-2002','dd-mon-yyyy')

           AND "D1"<=TO_DATE('05-Jan-2003','dd-mon-yyyy'))

숫자


 where  n1 between 20021230 and 20030105


 Execution Plan
  ----------------------------------------------------------
  | Id  | Operation         | Name | Rows  | Bytes | Cost  |
  ----------------------------------------------------------
 |   0 | SELECT STATEMENT  |      |   3277521 |     3 |
 |*  1 |  TABLE ACCESS FULL| T1   |   3277521 |     3 |
 ----------------------------------------------------------
 Predicate Information (identified by operation id):
 ---------------------------------------------------
    1 - filter("N1">=20021230 AND "N1"<=20030105)

 where  n1 between 20021230 and 20030105

 Execution Plan
 ----------------------------------------------------------
 | Id  | Operation         | Name | Rows  | Bytes | Cost  |
 ----------------------------------------------------------
 |   0 | SELECT STATEMENT  |      |    15 |   345 |     3 |
 |*  1 |  TABLE ACCESS FULL| T1   |    15 |   345 |     3 |
 ----------------------------------------------------------
 Predicate Information (identified by operation id):
 ---------------------------------------------------
    1 - filter("N1">=20021230 AND "N1"<=20030105)

문자

 where  v1 between '20021230' and '20030105'


 Execution Plan
 ----------------------------------------------------------
 | Id  | Operation         | Name | Rows  | Bytes | Cost  |
 ----------------------------------------------------------
 |   0 | SELECT STATEMENT  |      |   3277521 |     3 |
 |*  1 |  TABLE ACCESS FULL| T1   |   3277521 |     3 |
 ----------------------------------------------------------  
 Predicate Information (identified by operation id):
 ---------------------------------------------------
    1 - filter("V1">='20021230' AND "V1"<='20030105')

 where  v1 between '20021230' and '20030105'

 Execution Plan
 ----------------------------------------------------------
 | Id  | Operation         | Name | Rows  | Bytes | Cost  |
 ----------------------------------------------------------
 |   0 | SELECT STATEMENT  |      |    15 |   345 |     3 |
 |*  1 |  TABLE ACCESS FULL| T1   |    15 |   345 |     3 |
 ----------------------------------------------------------  
 Predicate Information (identified by operation id):
 ---------------------------------------------------
    1 - filter("V1">='20021230' AND "V1"<='20030105')


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

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


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

    BUCKET    LOW_VAL   HIGH_VAL      WIDTH     HEIGHT
---------- ---------- ---------- ---------- ----------
         1   19991101   19991116         15      1.015

         2   19991116   19991202         86       .177

         3   19991202   19991218         16      .9516

         4   19991218   20000103       8885      .0017

         5   20000103   20000119         16      .9516


        26   20001204   20001220         16      .9516
        27   20001220   20010105       8885      .0017

        28   20010105   20010120         15      1.015

        29   20010120   20010204         84      .1813


        51   20011216   20011231         15      1.015
        52   20011231   20020115       8884      .0017
        53   20020115   20020130         15      1.015
        54   20020130   20020214         84      .1813


        75   20021211   20021226         15      1.015

        76   20021226   20030110       8884      .0017

        77   20030110   20030125         15      1.015


        98   20031121   20031206         85      .1791

        99   20031206   20031221         15      1.015

       100   20031221   20040105       8884      .0017

       101   20040105   20040120         15      1.015

       102   20040120   20040204         84      .1813


       118   20040916   20041001         85      .1791

       119   20041001   20041016         15      1.015
       120   20041016   20041031         15      1.015



histogram.jpg


* 청록색 글씨 라인 참조

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


선택도 =

    = ('요청된 범위' /  '버킷의 총 대상 범위') + 2 * density

    = (20030105 - 20021230) / (20030111 - 20021227) + 2 * 0.000547345

    = 8875 / 8884 + 0.00109469

    = 1.0008



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

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

∴ round(15*1.0008) = 15


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

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



위험한 디폴트 값 216 /위충환

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개 버킷의 히스토그램 생성

    BUCKET LOW_VAL     HIGH_VAL         WIDTH     HEIGHT
---------- ----------- ----------- ---------- ----------
         1 01-Jan-2000 15-Jun-2000        166   100.0548
         2 15-Jun-2000 28-Nov-2000
        166   100.0548
         3 28-Nov-2000 13-May-2001        166   100.0548
         4 13-May-2001 27-Oct-2001        167    99.4556
         5 27-Oct-2001 11-Apr-2002        166   100.0548
         6 11-Apr-2002 24-Sep-2002        166   100.0548
         7 24-Sep-2002 09-Mar-2003        166   100.0548
         8 09-Mar-2003 23-Aug-2003        167    99.4556
         9 23-Aug-2003 05-Feb-2004        166   100.0548
        10 05-Feb-2004 20-Jul-2004        166   100.0548
        11 20-Jul-2004 31-Dec-4000     729188      .0228


히스토그램 생성 후 결과

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


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

히스토그램 일반적 처리

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

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

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

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


의의

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


결론

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

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

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



요약 218  / 이창헌




테스트 스크립트 219  / 이창헌