Frequency Histogram

• 장점 : 특정 값(Value)에 해당하는 빈도(Frequency)를 정확하게 알 수 있다. 단 Literal 일 경우.

Bind 변수를 사용 시

• 존재하지 않은 값에 대해서는 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를 사용)