꿈꾸는 개발자, DBA 커뮤니티 구루비

Cardinality
1. Frequency Histogram
2. Height-Balanced Histogram

Cardinality

Histogram이 Cardinality의 계산에 매우 큰 영향을 주며, Histogram의 종류(Frequency/Height-Balanced)에 따라 미치는 영향이 다르다.
(아래의 Plan은 앞 단락의 "기본개념"편에서 수집한 Histogram을 기준으로 실행한 것이므로, 참고하여 보길 바람)

Frequency Histogram

특정 값(Value)에 해당하는 빈도(Frequency)를 정확하게 알 수 있다.
Bind 변수를 사용한다면 Cardinality는 Density대신 1/NDV를 사용한다.
Bind 변수가 Range Predicate와 사용되는 경우 5% Rule 적용.
Bind 변수가 Between Predicate와 사용되는 경우 5% Rule 적용.
존재하지 않은 값에 대해서는 0으로 계산(10.2.0.4버전부터는 Bucket의 최소빈도/2의 값을 사용)
Histogram에 존재하지 않은 값에 대한 Cardinality의 계산은 어떻게 해야할까? => Manual하게 Histogram생성(7장에서 알려줌)

1) Literal(c1=1)


SQL> explain plan for
  2  select * from t1
  3  where c1=1;

SQL> @plan
--------------------------------------------------------------------------
| 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개로 예측!!!

2) Literal(c1=2)


SQL> explain plan for
  2  select * from t1
  3  where c1=2
  4  ;

SQL> @plan
--------------------------------------------------------------------------
| 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개로 예측!!!(얘도 Literal값이니까)

3) Bind 변수(c1=:b1)


SQL> explain plan for
  2  select * from t1
  3  where c1 = :b1;

SQL> @plan
--------------------------------------------------------------------------
| 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))

Bind변수에서는 Cardinality계산에 Density대신 1/NDV값을 사용. (11111*1/5 = 2222)

4) Bind 변수-Range Predicate (c1>:b1)


SQL> explain plan for
  2  select * from t1
  3  where c1 > :b1;

SQL> @plan
--------------------------------------------------------------------------
| 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))

책에선 5%의 Rule이 적용된다 했으나, 적용안됨. 어떤값을 근거로 위의 Row수를 산출한걸까..
5% Rule이 적용되었을 경우, 11111*0.05=556 정도..

5) Bind변수-Range Predicate (c1>=:b1)


SQL> explain plan for
  2  select *
  3  from t1
  4  where c1 >= :b1;

SQL> @plan
--------------------------------------------------------------------------
| 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))

책에선 5%의 Rule이 적용된다 했으나, 적용안됨. 어떤값을 근거로 위의 Row수를 산출한걸까..
5% Rule이 적용되었을 경우, 11111*0.05=556 정도..

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

책에선 5%의 Rule이 적용된다 했으나, 적용안됨. 어떤값을 근거로 위의 Row수를 산출한걸까..
5% Rule이 적용되었을 경우, 11111*0.05*0.05=28 정도..

7) 존재하지 않는 값 (c1=-1)


SQL> explain plan for
  2  select *
  3  from t1
  4  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))

존재하지 않는 값에 대해서는 0으로 계산, 0대신 1로 사용(Oracle 10.2.0.4부터는 1이 아닌 Bucket의 최소빈도/2를 사용)

Height-Balanced Histogram

Popular Value에 대해서 Cardinality = Base Cardinality * (bucket count / bucket size)
Non Polpular Value에 대해서 Cardinality = Base Cardinality * Density (Bind변수를 사용한 경우도 동일)
Height-Balanced Histogram이 생성되면 Density가 1/NDV와는 전혀다른 값을 지닐수 있다. 그러므로 예상치 못한 실행계획의 변화가 생길수 있으므로 이점에 유의한다.
Bind 변수가 Range Predicate와 사용되는 경우 5% Rule 적용.
존재하지 않은 값에 대해서는 최대/최소값을 기준으로 선형적으로 감소하는 방식으로 계산됨

1) Literal-Popular Value(c2=1)


explain plan for
select * from t1
where c2 = 1;

@plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  4943 | 29658 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  4943 | 29658 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("C2"=1)

=>Cardinality는 Base Cardinality * (bucket Count / bucket Size)이므로, 11111*113/254=4943로 계산

2) Literal-Non Popular Value(c2=10)


explain plan for
select * from t1
where c2 = 10;

@plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |    24 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     4 |    24 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("C2"=10)

Non Polular Value에 대해서 Cardinality는 Density를 이용하므로, 11111*0.00036=4로 계산

3) Bind 변수(c2=:b1)


explain plan for
select * from t1
where c2 = :b1;

@plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    37 |   222 |     7  (15)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |    37 |   222 |     7  (15)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("C2"=TO_NUMBER(:B1))

책에선 Bind 변수도 Non Popular Value의 Rule을 따른다 하였으나, 안따름.... 따랐으면 4

4) Bind 변수-Range Predicate (c2>:b1)


explain plan for
select * from t1
where c2 > :b1;

@plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   556 |  3336 |     7  (15)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   556 |  3336 |     7  (15)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("C2">TO_NUMBER(:B1))

=>5% Rule적용하여, 11111*0.05=556

5) Bind 변수-Range Predicate (c2>=:b1)


explain plan for
select * from t1
where c2 >= :b1;

@plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   556 |  3336 |     7  (15)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   556 |  3336 |     7  (15)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("C2">=TO_NUMBER(:B1))

=>5% Rule적용하여, 11111*0.05=556

6) Bind 변수-Between Predicate (c2 between :b1 and :b2)


explain plan for
select * from t1
where c2 between :b1 and :b2;

@plan
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    28 |   168 |     7  (15)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |    28 |   168 |     7  (15)| 00:00:01 |
---------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter(TO_NUMBER(:B1)<=TO_NUMBER(:B2))
   2 - filter("C2">=TO_NUMBER(:B1) AND "C2"<=TO_NUMBER(:B2))

=>5% Rule적용하여, 11111*0.05*0.05=28

6) 존재하지 않는 값(c2=301)


explain plan for
select * from t1
where c2 = 301;

@plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |    24 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     4 |    24 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("C2"=301)

=> 존재하지 않는 값에 대하여 최대/최소 값을 기준으로 선형적으로 감소하는 방식으로 계산된다

8) 존재하지 않는 값(c2=400)


explain plan for
select * from t1
where c2 = 400;

@plan
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |    18 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     3 |    18 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("C2"=400)

=> 존재하지 않는 값에 대하여 최대/최소 값을 기준으로 선형적으로 감소하는 방식으로 계산된다

8) 존재하지 않는 값(c2=599)


explain plan for
select * from t1
where c2 = 599;

@plan
--------------------------------------------------------------------------
| 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("C2"=599)

=> 존재하지 않는 값에 대하여 최대/최소 값을 기준으로 선형적으로 감소하는 방식으로 계산된다

최대/최소 값을 기준으로 선형적으로 감소하는 방식

예) C2 컬럼
최대-최소구간 : (1~300)
위의 Frequency : 4515
(-300~0) (301~600)구간의 Frequncy는 선형적으로 감소한다고 가정, 감소크기는 4515/300=15 즉, 15씩 감소한다고 가정한다.
(-300~0) (301~600) 구간을 벗어나는 값은 동일하게 Cardinality 1로 사용.
=> 내가 수행한 결과로는 Density가 아주 작은 값으로 나와(0.00036), Frequency가 4로 나와서, 가시적으로 책과 같이 나오지는 않았음.