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

용어	정의	설명
NDV	Number of Distinct Value의 약자.	만일 Column c1이 ('A', 'B', 'C', 'D', 'E') 다섯 가지 종류의 값으로 이루어져 있다면 NDV(C1) = 5가 된다.
	유일하게 구별되는 Column값의 개수	각 값이 차지하는 분포는 고려대상이 아니다. NDV값은 Object Statistics 수집 시 계산되며, DBA_TAB_COL_STATISTICS.NUM_DISTINCT Column을 통해 조회 가능
Density	밀도.	만일 Column c1이 'A'라는 하나의 값만으로 이루어져 있다면 Density = 1이 된다.
	분포도 라고 부르기도 한다.	만일 Column c1 이 (1, 2, 3, ...10000)의 유일하게 구별되는 10,000으로 이루어져 있다면 Density = 1/10000=0.0001(Density = 1/NDV)이 된다.
	Column이 얼마나 밀도가 높은지를 결정한다.	Histogram이 있는 경우에는 분포도를 고려한 보정된 Density값이 계산 된다. Object Statistics 수집 시 계산되며, DBA_TAB_COL_STATISTICS.DENSITY Column을 통해 조회 가능
Cardinality	집합의 크기를 의미한다.	집합에 속하는 원소의 수가 100개라면 ( Cardinality(집합)=100 ) 과 같이 표현된다. Tabel t1의 전체 Row수가 10,000개 라면 (Cardinality(t1) = 10000)이 된다.
		만일, Where t1.c1=1이라는 조건이 주어지고 해당 조건을 만족하는 Row수가 1,000개라면 (Cardinality(t1) = 1000)이 된다.
		(Adjusted Cardinatliy = Base Cardinality * Selectivity)의 공식을 따른다.
Selectivity	선택도.특정 조건(Predicate)을 만족할 확율.	Selectivity는 Density와 달리 고정된 값이 아니라 조건에 따라 바뀌는 값이다.
		만일, Column c1의 Density가 0.1이라고 하더라도 (c1 = 1 ), (c1 = :b1), (c1 between 1 and 10 ), (c1 etween :b1 and :b2 ) 조건의 Selectivity는 모두 다르다.
Histogram	분포도	가령, Histogram을 수집하면 Column c1의 분포가 ( n('A') = 1000, n('B') = 500, n('C')-=200, n('D')=100, n('E') = 1)을 따른다는 것을 알 수 있다.
		Histogram은 Data가 비대칭적일 때 그 분포를 알 수 있는 유일한 방법이다.



column c1은 1~10000까지의 값을 골고루 가지게끔,
column c2는 'A','B','C','D','E'가 각각 (5000,3000,1000,800,200)건이 존재하게끔 DATA를 생성한다.

drop table t1 purge;

create table t1(c1 int, c2 char(1));

insert into t1
select
 level,
 case
  when level between 1 and 5000 then 'A'
  when level between 5001 and 8000 then 'B'
  when level between 8001 and 9000 then 'C'
  when level between 9001 and 9800 then 'D'
  when level between 9801 and 10000 then 'E'
 end
from dual
connect by level <= 10000
;

commit;

통계정보를 수집하되 column c1은 히스토그램이 없이,
column c2는 bucket크기가 5가 되게끔 히스토그램을 생성한다.

exec dbms_stats.gather_table_stats('hyun', 't1', method_opt=>'FOR COLUMNS C1 size 1 C2 size 5');

-- table stats
select table_name, num_rows, blocks, sample_size, last_analyzed from user_tables;

TABLE_NAME                       NUM_ROWS     BLOCKS SAMPLE_SIZE LAST_ANA
------------------------------ ---------- ---------- ----------- --------
A                                       0          0           0 11/02/20
T1                                  10000         20       10000 11/03/09


-- column stats
select table_name, column_name, num_distinct, num_nulls, density
  from user_tab_columns
 where table_name = 'T1';

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS    DENSITY
------------------------------ ------------------------------ ------------ ---------- ----------
T1                             C1                                    10000          0      .0001
T1                             C2                                        5          0     .00005

-- histogram
select table_name, column_name, endpoint_number, endpoint_value
  from user_tab_histograms
 where table_name = 'T1'
 order by 3
;

TABLE_NAME	COLUMN_NAME	ENDPOINT_NUMBER	ENDPOINT_VALUE
---------------------------------------------------------------
T1		C1		0		1
T1		C1		1		10000
T1		C2		5000		3.3815E+35
T1		C2		8000		3.4334E+35
T1		C2		9000		3.4854E+35
T1		C2		9800		3.5373E+35
T1		C2		10000		3.5892E+35


7 개의 행이 선택되었습니다.

Table t1은 전체 Row수가 10,000건(물리적인 Row수가 아니라 통계 정보에 있는 Row수를 의미)
따라서 오라클은 다음과 같이 예측한다.

explain plan for
 select * from t1;

select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 | 50000 |     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   | 10000 | 50000 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

만약 c1=:b1이라는 조건이 주어지면 Column c1의 Density는 0.0001이다. 
따라서 c1=:b1이라는 단일 조건이 주어진 경우에는 selectivity = density 즉, 0.0001 이 된다.
그러므로 Cardinality = Base Cardinality (10000) * Selectivity (0.001) =1 이 된다.

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

select * from table(dbms_xplan.display);

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

Column c2의 Density는 0.00005이다.
만일 Density가 Selectivity산출 근거로 사용되었다면 Cardinality = Base Cardinality(10000) * Selectivity(0.00005)=0.5=1이 된다.
하지만 Cardinality = 2000의 값을 보인다. 그 이유는 Selectivity = 1/NDV = 1/5=0.2의 값으로 계산되었기 때문이다.
Histogram이 존재하는 경우에는 Selectivity계산에 Density가 아닌 NDV값이 사용된다.

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

select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2000 | 10000 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  2000 | 10000 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

만일 Histogram이 존재하는 Column c2에 대해 c2='A' 조건이 사용된 경우라면 Histogram 덕분에 정확한 분포를 알 수 있기 때문에
c2='A'조건에 해당하는 Cardinality 는 5000 이라는 것을 알 수 있다.

explain plan for
select * from t1
 where c2 = 'A';

select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  5000 | 25000 |     8  (13)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  5000 | 25000 |     8  (13)| 00:00:01 |
--------------------------------------------------------------------------

만일 c1=:b1 and c2=:b2같은 복잡한 조건에 대해서는 다음과 같은 공식이 사용된다.
Selectivity(p1 and p2) = Selectivity(p1) * Selectivity(p2)

Selectivity(c1 = :b1) = 0.001 이다.
Selectivity(c2 = :b2) = 0.2 이다.
따라서 Selectivity = 0.001 * 0.2* 10000 = 0.2 = 1이 된다.

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

select * from table(dbms_xplan.display);

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

만일 c2 like '%A%와 같은 조건이라면 답은 '모른다' 이다.
모를 때는 상수(Constant, Magic Number)를 이용할 수 밖에 없다. 이 경우에는 Selectivity로 5%라는 고정된 값을 사용한다.
따라서 Cardinality = Base Cardinality(10000) * Selectivity(0.05) = 500이 된다.

explain plan for
select * from t1
 where c2 like '%A%';

select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   500 |  2500 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   500 |  2500 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------