1. II. Correlated Columns
   1. II.1. 테스트 데이터 생성
   2. II.2. 통계정보 확인
   3. II.3. 테스트

II. Correlated Columns

II.1. 테스트 데이터 생성

-- create object
DROP TABLE T1 PURGE;

CREATE TABLE T1(C1 VARCHAR2(1), C2 INT);

-- create data
INSERT INTO T1
SELECT 'A', 1
FROM   DUAL
CONNECT BY LEVEL <= 1000
UNION ALL
SELECT 'B', 2
FROM   DUAL
CONNECT BY LEVEL <= 100
UNION ALL
SELECT 'C', 2
FROM   DUAL
CONNECT BY LEVEL <= 100
;

COMMIT;

-- gather stats "with" histogram
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1', METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY', NO_INVALIDATE => FALSE);

II.2. 통계정보 확인

-- Table
SELECT TABLE_NAME,
       NUM_ROWS,
       BLOCKS,
       SAMPLE_SIZE,
       TO_CHAR(LAST_ANALYZED, 'YYYY/MM/DD HH24:MI:SS') AS LAST_ANAL
FROM   USER_TAB_STATISTICS
WHERE  TABLE_NAME = UPPER('T1')
;

-- Column
SELECT S.TABLE_NAME,
       S.COLUMN_NAME,
       S.NUM_DISTINCT,
       S.NUM_NULLS,
       S.DENSITY,
       S.LOW_VALUE,
       S.HIGH_VALUE,
       S.HISTOGRAM
FROM   USER_TAB_COLS S
WHERE  S.TABLE_NAME = UPPER('T1')
;

-- Histogram
SELECT TABLE_NAME,
       COLUMN_NAME,
       ENDPOINT_NUMBER,
       ENDPOINT_VALUE || '(' || ENDPOINT_ACTUAL_VALUE || ')' AS ENDPOINT_VALUE
FROM   USER_TAB_HISTOGRAMS
WHERE  TABLE_NAME = UPPER('T1')
ORDER  BY COLUMN_NAME,
          ENDPOINT_NUMBER
;

II.3. 테스트

1) C1 Columns 'A' Cardanality with histogram

SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(*)
FROM   T1
WHERE  C1 = 'A'
;

select * from table(dbms_xplan.display_cursor(null, null, 'iostats cost last'));

--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |            |      1 |00:00:00.01 |      15 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |   1000 |     5   (0)|   1000 |00:00:00.01 |      15 |
--------------------------------------------------------------------------------------------------
;

2) C1 = 'A' AND C2 = 1

• = Base Cardinality * Selectivity
• = Base Cardinality * Selectivity(C1 = 'A' AND C2 = 1)
• = Base Cardinality * Selectivity(C1 = 'A') * Selectivity(C2 = 1)
• = 1200 * 1000/1200 * 1000/1200
• = 833

SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(*)
FROM   T1
WHERE  C1 = 'A'
AND    C2 = 1
;

select * from table(dbms_xplan.display_cursor(null, null, 'iostats cost last'));

--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |            |      1 |00:00:00.01 |      15 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |    833 |     5   (0)|   1000 |00:00:00.01 |      15 |
--------------------------------------------------------------------------------------------------
;

3) C1 = 'A' AND C2 = 2

• = Base Cardinality * Selectivity
• = Base Cardinality * Selectivity(C1 = 'A' AND C2 = 2)
• = Base Cardinality * Selectivity(C1 = 'A') * Selectivity(C2 = 2)
• = 1200 * 1000/1200 * 200/1200
• = 166

SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(*)
FROM   T1
WHERE  C1 = 'A'
AND    C2 = 2
;

select * from table(dbms_xplan.display_cursor(null, null, 'iostats cost last'));

--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |            |      1 |00:00:00.01 |      15 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |    166 |     5   (0)|      0 |00:00:00.01 |      15 |
--------------------------------------------------------------------------------------------------
;

4. Exetended Statistics

• 10g까지는 Column들간으 상호 의존성을 Oracle이 인식하도록 할 수 있는 방법은 없다
• 11g부터는 Extended Statistics를 이용해 Column들간의 상호 의존성을 통계 정보에 저장 가능

• 구문

 EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1', METHOD_OPT => 'FOR COLUMNS (C1, C2) SIZE SKEWONLY', NO_INVALIDATE => FALSE);