by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

Frequency Histogram




Frequency Histogram

  • 장점 : 특정 값(Value)에 해당하는 빈도(Frequency)를 정확하게 알 수 있다. 단 Literal 일 경우.
  • Bind 변수를 사용 시
    Predicaete Type Cardinality  
    Predicate ( = ) 1/NDV 11111 * 1/5 = 22222
    Range Predicate ( >, <, etc) 5% rule 11111 * 0.05 = 556
    Between Predicate 5% rule 11111 * 0.05 * 0.05 = 27.77 = 28
  • 존재하지 않은 값에 대해서는 0으로 계산(10.2.0.4버전부터는 Bucket의 최소빈도/2의 값을 사용)
  • Histogram에 존재하지 않은 값에 대한 Cardinality의 계산 방법 => Manual하게 Histogram생성(7장)
예제

TABLE_NAME                    : T1
COLUMN_NAME                   : C1
NUM_DISTINCT                  : 5
NUM_NULLS                     : 0
DENSITY                       : .0000450004500045
LOW_VALUE                     : C102
HIGH_VALUE                    : C106
HISTOGRAM                     : FREQUENCY


  • 컬럼 C1에 대한 Histogram 정보 - Frequency Histogram
            C1        CNT
    ---------- ----------
             1      10000
             2       1000
             3        100
             4         10
             5          1
    
    TABLE_NAME           COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE
    -------------------- -------------------- --------------- --------------------
    T1                   C1                             10000 1()
    T1                   C1                             11000 2()
    T1                   C1                             11100 3()
    T1                   C1                             11110 4()
    T1                   C1                             11111 5()
    


  • Literal 의 경우
    SQL> explain plan for
      2  select * from t1 where c1 = 1;
      
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      | 10000 | 60000 |     6   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| T1   | 10000 | 60000 |     6   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    
       1 - filter("C1"=1)
    
    • Cardinality : 10000 으로 예측


  • Literal(c1=2)
    SQL> explain plan for
      2  select * from t1
      3  where c1 = 2;
    
    해석되었습니다.
    
    SQL> @plan
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Plan hash value: 3617692013
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |  1000 |  6000 |     6   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| T1   |  1000 |  6000 |     6   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    
       1 - filter("C1"=2)
    
    • Cardinality : 1000 으로 예측


  • Bind 변수 : Predicate (c1=:b1)
    SQL> explain plan for
      2  select * from t1
      3  where c1 = :b1;
    
    해석되었습니다.
    
    SQL> @plan
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Plan hash value: 3617692013
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |  2222 | 13332 |     7  (15)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| T1   |  2222 | 13332 |     7  (15)| 00:00:01 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    
       1 - filter("C1"=TO_NUMBER(:B1))
    
    • Cardinality : 1/NDV = (11111*1/5 = 2222)


  • Bind 변수 : Range Predicate (c1>:b1)
    SQL> explain plan for
      2  select * from t1
      3  where c1 > :b1;
    
    해석되었습니다.
    
    SQL> @plan
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Plan hash value: 3617692013
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |  2223 | 13338 |     7  (15)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| T1   |  2223 | 13338 |     7  (15)| 00:00:01 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    
       1 - filter("C1">TO_NUMBER(:B1))
    
    • Cardinality : 5% rule 적용(11111*0.05=556) 이어야 하는데... 결과는 다르다. 근거를 알 수가 없음...


  • Bind변수 : Between Predicate(c1 between :b1 and :b2)
    SQL> explain plan for
      2  select * from t1
      3  where c1 between :b1 and :b2;
    
    해석되었습니다.
    
    SQL> @plan
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Plan hash value: 3332582666
    
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |   445 |  2670 |     7  (15)| 00:00:01 |
    |*  1 |  FILTER            |      |       |       |            |          |
    |*  2 |   TABLE ACCESS FULL| T1   |   445 |  2670 |     7  (15)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    ---------------------------------------------------
    
       1 - filter(TO_NUMBER(:B1)<=TO_NUMBER(:B2))
       2 - filter("C1">=TO_NUMBER(:B1) AND "C1"<=TO_NUMBER(:B2))
    
    • Cardinality : 5% rule 적용(11111*0.05*0.05=28) 이어야 하는데... 또 결과는 다르다. 뭘까...
  • 존재하지 않는 값 (c1=-1)
    SQL> explain plan for
      2  select * from t1
      3  where c1 = -1;
    
    해석되었습니다.
    
    SQL> @plan
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    Plan hash value: 3617692013
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |     1 |     6 |     6   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| T1   |     1 |     6 |     6   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    
       1 - filter("C1"=(-1))
    
    • Cardinality : 1 (Oracle 10.2.0.4부터는 1이 아닌 Bucket의 최소빈도/2를 사용)

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.