Partition Key

Partition Table에 대한 Cardinality 예측 또한 예상치 못한 결과를 보임
Partition Elimination이 가능한 경우와 불가능한 경우 많은 차이를 보이며, 가능하더라도 단일 Partition을 Access 하는 경우와 다중 Partiton을 Access 하는 경우에 다른 Patten의 Cardanality 계산이 이루어짐

Test


-- create partition table T1
DROP TABLE T1 PURGE;

CREATE TABLE T1(C1 INT,
                C2 INT)
PARTITION BY LIST(C1)
(PARTITION P1 VALUES (1),
 PARTITION P2 VALUES (2),
 PARTITION P3 VALUES (3),
 PARTITION P4 VALUES (4),
 PARTITION P5 VALUES (5)
);
               
-- create data
INSERT INTO T1
SELECT 1, LEVEL FROM DUAL CONNECT BY LEVEL <= 10000 UNION ALL
SELECT 2, LEVEL FROM DUAL CONNECT BY LEVEL <= 1000  UNION ALL
SELECT 3, LEVEL FROM DUAL CONNECT BY LEVEL <= 100   UNION ALL
SELECT 4, LEVEL FROM DUAL CONNECT BY LEVEL <= 10    UNION ALL
SELECT 5, LEVEL FROM DUAL CONNECT BY LEVEL <= 1 
;

COMMIT;

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

-- create table T4
DROP TABLE T4 PURGE;

CREATE TABLE T4(C1 INT,
                C2 INT)
;                

-- create data
INSERT INTO T4
SELECT 1, LEVEL FROM DUAL CONNECT BY LEVEL <= 10000 UNION ALL
SELECT 2, LEVEL FROM DUAL CONNECT BY LEVEL <= 1000  UNION ALL
SELECT 3, LEVEL FROM DUAL CONNECT BY LEVEL <= 100   UNION ALL
SELECT 4, LEVEL FROM DUAL CONNECT BY LEVEL <= 10    UNION ALL
SELECT 5, LEVEL FROM DUAL CONNECT BY LEVEL <= 1 
;

COMMIT;

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

-- disable bind peeking
ALTER SYSTEM SET "_OPTIM_PEEK_USER_BINDS" = FALSE;

통계 정보를 확인한다.


-- 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 IN ('T1', 'T4')
;

TABLE_NAME	NUM_ROWS	BLOCKS	SAMPLE_SIZE	LAST_ANAL

T1	11111	210	11111	2011/05/13 12:06:36
T4	11111	42	11111	2011/05/13 12:06:51
T1	10000	42	10000	2011/05/13 12:06:36
T1	1000	42	1000	2011/05/13 12:06:36
T1	100	42	100	2011/05/13 12:06:36
T1	10	42	10	2011/05/13 12:06:36
T1	1	42	1	2011/05/13 12:06:36


-- 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 IN ('T1', 'T4')
;

TABLE_NAME	COLUMN_NAME	NUM_DISTINCT	NUM_NULLS	DENSITY	LOW_VALUE	HIGH_VALUE	HISTOGRAM

T4	C2	10000	0	0.0001	C102	C302	NONE
T4	C1	5	0	0.2	C102	C106	NONE
T1	C2	10000	0	0.0001	C102	C302	NONE
T1	C1	5	0	0.2	C102	C106	NONE


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

TABLE_NAME	COLUMN_NAME	ENDPOINT_NUMBER	ENDPOINT_VALUE

T1	C1	0	1()
T4	C1	0	1()
T1	C1	1	5()
T4	C1	1	5()
T1	C2	0	1()
T4	C2	0	1()
T4	C2	1	10000()
T1	C2	1	10000()

*
= Base Cardinality * Selectivity(C1)
= 11111 * 0.2
= 2222


VAR B1 NUMBER;
VAR B2 NUMBER;

EXEC :B1 := 1;
EXEC :B2 := 3;

SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(*)
FROM   T1
WHERE  C1 = :B1
;

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

PLAN_TABLE_OUTPUT

Plan hash value: 2215101622
 
------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE        |      |      1 |      1 |            |      1 |00:00:00.01 |      44 |
|   2 |   PARTITION LIST SINGLE|      |      1 |   2222 |    11   (0)|  10000 |00:00:00.01 |      44 |
|   3 |    TABLE ACCESS FULL   | T1   |      1 |   2222 |    11   (0)|  10000 |00:00:00.01 |      44 |
------------------------------------------------------------------------------------------------------


SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(*)
FROM   T4
WHERE  C1 = :B1
;

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

PLAN_TABLE_OUTPUT

Plan hash value: 4162970584
 
--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |            |      1 |00:00:00.01 |      44 |
|*  2 |   TABLE ACCESS FULL| T4   |      1 |   2222 |    11   (0)|  10000 |00:00:00.01 |      44 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("C1"=TO_NUMBER(:B1))

Patition Elimination 전체 Row 10000건 Density = 1이므로 Cardinality = 10000 * 1 = 10000건이 된다.


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

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

Plan hash value: 3751617648
 
------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE        |      |      1 |      1 |            |      1 |00:00:00.01 |      44 |
|   2 |   PARTITION LIST SINGLE|      |      1 |  10000 |    11   (0)|  10000 |00:00:00.01 |      44 |
|   3 |    TABLE ACCESS FULL   | T1   |      1 |  10000 |    11   (0)|  10000 |00:00:00.01 |      44 |
------------------------------------------------------------------------------------------------------

Non Partition Table이므로 0.2의 Density를 사용한다.


SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(*)
FROM   T4
WHERE  C1 = 1
;

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

PLAN_TABLE_OUTPUT

Plan hash value: 4162970584
 
--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |            |      1 |00:00:00.01 |      44 |
|*  2 |   TABLE ACCESS FULL| T4   |      1 |   2222 |    11   (0)|  10000 |00:00:00.01 |      44 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("C1"=1)

다중 Partition에 대한 Access가 이루어지는 경우는 Partition Statistics가 아닌 Global Statistics가 사용된다.
= Cardinality(C1 BETWEEN 1 AND 3)
= Cardinality(C1 = 1) + Cardinality(C1 = 3) + Cardinality(1 < C1 < 3)
= 0 + (11111 * 0.2) + ((3 - 1) * (5 - 1) * 11111)
= 7777.7 = 7778


SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(*)
FROM   T1
WHERE  C1 BETWEEN 1
          AND     3
;

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

PLAN_TABLE_OUTPUT

Plan hash value: 2624924945
 
--------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE          |      |      1 |      1 |            |      1 |00:00:00.01 |      86 |
|   2 |   PARTITION LIST ITERATOR|      |      1 |   7778 |    29   (0)|  11100 |00:00:00.01 |      86 |
|   3 |    TABLE ACCESS FULL     | T1   |      3 |   7778 |    29   (0)|  11100 |00:00:00.01 |      86 |
--------------------------------------------------------------------------------------------------------


SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(*)
FROM   T4
WHERE  C1 BETWEEN 1
          AND     3
;

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

PLAN_TABLE_OUTPUT

Plan hash value: 4162970584
 
--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |            |      1 |00:00:00.01 |      44 |
|*  2 |   TABLE ACCESS FULL| T4   |      1 |   7778 |    11   (0)|  11100 |00:00:00.01 |      44 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("C1"<=3 AND "C1">=1))

Distinct Count가 20보다 큰 경우 : Density가 0.05보다 작은 경우에는 5%의 Rule을 사용한다.
Distinct Count가 20보다 작은 경우 : Density가 0.05보다 큰 경우에는 Density를 사용한다.

Cardinality = 11111 * 0.2 * 0.2 = 444.4 = 445


SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(*)
FROM   T1
WHERE  C1 BETWEEN :B1
          AND     :B2
;

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

PLAN_TABLE_OUTPUT

Plan hash value: 3650607437
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE           |      |      1 |      1 |            |      1 |00:00:00.01 |      86 |
|*  2 |   FILTER                  |      |      1 |        |            |  11100 |00:00:00.01 |      86 |
|   3 |    PARTITION LIST ITERATOR|      |      1 |    445 |    48   (0)|  11100 |00:00:00.01 |      86 |
|   4 |     TABLE ACCESS FULL     | T1   |      3 |    445 |    48   (0)|  11100 |00:00:00.01 |      86 |
---------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(TO_NUMBER(:B1)<=TO_NUMBER(:B2))

Partion Key (by csahn79) [2011.05.11]

Partition Key

Test