by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

Partion Key




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

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.