[Oracle] Oracle Cardinality(오라클 카디날리티)

Cardinality는 집합 안의 원소 개수, 오라클의 경우는 예측 Row 수라고 할 수 있습니다.

옵티마이저가 실행계획을 세우는 데 있어서 가장 중요한 것은 정확한 Cardinality의 계산이며, 동시에 그것은 실행계획이 비효율적인 가장 큰 원인이기도 합니다.

아래 실습은 기본적인 테이블을 생성 후에 ANALYZE TABLE을 하면 통계정보가 생성되며, 이후 Cardinality를 확인하는 방법과 Histogram에 따라서 정확한 값을 예측 불가능 하다는 것을 확인해보도록 하겠습니다. 

즉, Histogram을 사용하지 않도록 설정합니다.

Cardinality의 기본적인 개념을 먼저 파악하겠습니다.

1. Base Cardinality

Base Cardinality란 Table의 전체 Row 수를 의미합니다. 예를 들면, DBA_TABLES의 NUM_ROWS의 값 입니다.

2. Calculated Cardinality

calculated Cardinality란 Predicate, 즉, 조건 절에 의한 Filtering 된 Row 수를 의미하며, EST_CARDINALITY 테이블의 전체 Row 수가 100건이고, WHERE 조건절에 EST_CARDINALITY.CARD_NO > 90으로 했을 경우, 조건에 만족하는 건수가 10건이라고 가정하고, Base Cardinality(EST_CARDINALITY 테이블)는 1000, Calculated Cardinality는 10 됩니다.

3. Estimated Cardinality

Base Cardinality 또는 Calculated Cardinality 모두 Oracle이 실행계획을 세우는 단계에서 사용하는

모든 Cardinality는 예측치이며, 이것을 Estimated Cardinality라고 부릅니다.

4. Actual Cardinality

Query를 수행한 후 계산된 실제 Row 수를 의미합니다.

Estimated Cardinality가 실행계획을 수립하는 단계에서 계산되는 반면 Actual Cardinality는 실제 수행한 후에만 알 수 있습니다.

Estimated Cardinality 값과 Actual Cardinality 값이 큰 차이를 보인다면 Oracle의 예측이 부정확했다는 것을 의미합니다.

Explan Plan에서 아래와 같은 정보를 의미합니다.

E-Rows : Estimated Cardinality

A-Rows : Actual Cardinality

/* 테이블 삭제 */
DROP TABLE EST_CARDINALITY CASCADE CONSTRAINT PURGE;

/* 테이블 생성 */

CREATE    TABLE EST_CARDINALITY AS
SELECT    ROWNUM CARD_NO, MOD(ROWNUM, 5) + 1 AS CARD_GB
FROM      DUAL
CONNECT BY LEVEL <= 1000;

/*
통계정보수집을 위한 방법이고 자세한 설명은 아래와 같습니다.
compute statistics : Table내의 모든 데이터에 기초한 통계정보 생성
for table : 테이블에 대한 통계정보 생성
for all columns : 테이블의 모든 [인덱스]컬럼에 대해 히스토리그램 정보 생성

*/

ANALYZE TABLE EST_CARDINALITY COMPUTE STATISTICS FOR TABLE FOR ALL COLUMNS;

/*
  여기서부터 E-ROWS는 OPERATION에서 예측한 로우 수를 의미하는데 정확한 값을 예측 하지 못합니다. 
*/

SELECT    A.CARD_NO
FROM      EST_CARDINALITY A
WHERE     A.CARD_gb = 3
;
--------------------------------------------------------------------------------------
| Id  | Operation         | Name            | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |        |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| EST_CARDINALITY |    200 |  1000 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

SELECT    A.CARD_NO
FROM      EST_CARDINALITY A
WHERE     A.CARD_no >= 500 
;
--------------------------------------------------------------------------------------
| Id  | Operation         | Name            | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |    504 |  1512 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EST_CARDINALITY |    504 |  1512 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

SELECT    A.CARD_NO
FROM      EST_CARDINALITY A
WHERE     A.CARD_no <= 500 
;
--------------------------------------------------------------------------------------
| Id  | Operation         | Name            | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |    496 |  1488 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EST_CARDINALITY |    496 |  1488 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

SELECT   A.CARD_NO
FROM     EST_CARDINALITY A
where    a.card_no between 1 and 100
;
--------------------------------------------------------------------------------------
| Id  | Operation         | Name            | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |     99 |   297 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EST_CARDINALITY |     99 |   297 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

SELECT    A.TABLE_NAME
         ,A.COLUMN_NAME
         ,A.NUM_DISTINCT
         ,A.DENSITY
         ,A.HISTOGRAM
         ,UTL_RAW.CAST_TO_NUMBER(A.LOW_VALUE) AS LOW_VALUE
         ,UTL_RAW.CAST_TO_NUMBER(A.HIGH_VALUE) AS HIGH_VALUE
         ,B.NUM_ROWS AS TOT_TAB_ROWS
         ,b.blocks as blocks
FROM      USER_TAB_COL_STATISTICS A
         ,USER_TABLES B
WHERE     B.TABLE_NAME = 'EST_CARDINALITY'
AND       A.TABLE_NAME = B.TABLE_NAME;

--------------------------------
TABLE_NAME:    EST_CARDINALITY
COLUMN_NAME:   CARD_NO
NUM_DISTINCT:  1000
DENSITY:       0.001
HISTOGRAM:     HEIGHT BALANCED
LOW_VALUE:     1
HIGH_VALUE:    1000
TOT_TAB_ROWS:  1000
BLOCKS:        2
--------------------------------
TABLE_NAME:    EST_CARDINALITY
COLUMN_NAME:   CARD_GB
NUM_DISTINCT:  5
DENSITY:       0.0005
HISTOGRAM:     FREQUENCY
LOW_VALUE:     1
HIGH_VALUE:    5
TOT_TAB_ROWS:  1000
BLOCKS:        2
--------------------------------

SELECT    A.*
FROM      USER_HISTOGRAMS A
WHERE     A.TABLE_NAME = 'EST_CARDINALITY'      
;

table_name column_name endpoint_number endpoint_value endpoint_actual_value
EST_CARDINALITY CARD_NO 0 1 
EST_CARDINALITY CARD_NO 1 14 
EST_CARDINALITY CARD_NO 2 28 
EST_CARDINALITY CARD_NO 3 42 
..
생략
.. 
EST_CARDINALITY CARD_NO 70 980 
EST_CARDINALITY CARD_NO 71 994 
EST_CARDINALITY CARD_NO 72 1000 
EST_CARDINALITY CARD_GB 200 1 
EST_CARDINALITY CARD_GB 400 2 
EST_CARDINALITY CARD_GB 600 3 
EST_CARDINALITY CARD_GB 800 4 
EST_CARDINALITY CARD_GB 1000 5 

/* 
  SYSTEM의 EST_CARDINALITY 테이블의 컬럼(Highand Low Column Value), 연관 인덱스의 통계정보를 생성 합니다.
  FOR ALL COLUMNS SIZE는 기본적으로 AUTO로 설정되어서 오라클이 알아서 판단하게 됩니다.
  FOR ALL COLUMNS SIZE 1로 변경하게 되면, 모든 컬럼에 대해서 Histogram bucket의 수를 1로 변경 합니다.
  즉, 컬럼 내에 존재하는 여러 가지 값들의 Cardinality는 모두 동일한 값으로 간주하며, Cardinality가 제대로 예측하지 못하여, PLAN이 변경 되는 것을 방지하기 위한 용도입니다.
 */
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYSTEM', 'EST_CARDINALITY', METHOD_OPT=>'FOR ALL COLUMNS SIZE 1');


SELECT    A.TABLE_NAME
         ,A.COLUMN_NAME
         ,A.NUM_DISTINCT
         ,A.DENSITY
         ,A.HISTOGRAM
         ,UTL_RAW.CAST_TO_NUMBER(A.LOW_VALUE) AS LOW_VALUE
         ,UTL_RAW.CAST_TO_NUMBER(A.HIGH_VALUE) AS HIGH_VALUE
         ,B.NUM_ROWS AS TOT_TAB_ROWS
         ,b.blocks as blocks
FROM      USER_TAB_COL_STATISTICS A
         ,USER_TABLES B
WHERE     B.TABLE_NAME = 'EST_CARDINALITY'
AND       A.TABLE_NAME = B.TABLE_NAME;

--------------------------------
TABLE_NAME:    EST_CARDINALITY
COLUMN_NAME:   CARD_NO
NUM_DISTINCT:  1000
DENSITY:       0.001
HISTOGRAM:     NONE
LOW_VALUE:     1
HIGH_VALUE:    1000
TOT_TAB_ROWS:  1000
BLOCKS:        2
--------------------------------
TABLE_NAME:    EST_CARDINALITY
COLUMN_NAME:   CARD_GB
NUM_DISTINCT:  5
DENSITY:       0.2
HISTOGRAM:     NONE
LOW_VALUE:     1
HIGH_VALUE:    5
TOT_TAB_ROWS:  1000
BLOCKS:        2
--------------------------------

SELECT    A.*
FROM      USER_HISTOGRAMS A
WHERE     A.TABLE_NAME = 'EST_CARDINALITY'      
;
--------------------------------
table_name column_name endpoint_number endpoint_value endpoint_actual_value
EST_CARDINALITY CARD_NO 0 1 
EST_CARDINALITY CARD_GB 0 1 
EST_CARDINALITY CARD_NO 1 1000 
EST_CARDINALITY CARD_GB 1 5 
--------------------------------


SELECT    A.CARD_NO
FROM      EST_CARDINALITY A
WHERE     A.CARD_no >= 500 
;

--------------------------------------------------------------------------------------
| Id  | Operation         | Name            | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |    502 |  2008 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EST_CARDINALITY |    502 |  2008 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

SELECT    A.CARD_NO
FROM      EST_CARDINALITY A
WHERE     A.CARD_no <= 500 
;
--------------------------------------------------------------------------------------
| Id  | Operation         | Name            | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |    500 |  2000 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EST_CARDINALITY |    500 |  2000 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------