1. IV. Partition KEY
   1. IV.1. 테스트 데이터 생성
   2. IV.2. 통계정보 확인
   3. IV.3. 테스트

IV. Partition KEY

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

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

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

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

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

IV.3. 테스트

1) Partition Table 'T1' with Bind Variable 'C1' Column

• = 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'));

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

2) Normal Table 'T4' with Bind Variable 'C1' COLUMN

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

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 |      31 |
|*  2 |   TABLE ACCESS FULL| T4   |      1 |   2222 |     9   (0)|  10000 |00:00:00.01 |      31 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("C1"=:B1)
;

3) Partition Table 'T1' with Literal Variable 'C1' Column

SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(*)
FROM   T1
WHERE  C1 = 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 |      31 |
|   2 |   PARTITION LIST SINGLE|      |      1 |  10000 |     9   (0)|  10000 |00:00:00.01 |      31 |
|   3 |    TABLE ACCESS FULL   | T1   |      1 |  10000 |     9   (0)|  10000 |00:00:00.01 |      31 |
------------------------------------------------------------------------------------------------------

4) Partition Table 'T4' with Literal Variable 'C1' Column

SELECT /*+ GATHER_PLAN_STATISTICS */
       COUNT(*)
FROM   T4
WHERE  C1 = 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 |      31 |
|*  2 |   TABLE ACCESS FULL| T4   |      1 |   2222 |     9   (0)|  10000 |00:00:00.01 |      31 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("C1"=1)
;

5) Partition Table 'T1' access range with Literal Variable 'C1' Column

• = 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
• 다중 Partition에 대한 Access가 이루어지는 경우는 Partition Statistics가 아닌 Global Statistics가 사용된다.

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

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

6) Normal Table 'T4' access range with Literal Variable 'C1' Column

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

--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |            |      1 |00:00:00.01 |      31 |
|*  2 |   TABLE ACCESS FULL| T4   |      1 |   7778 |     9   (0)|  11100 |00:00:00.01 |      31 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("C1"<=3 AND "C1">=1))
;

7) Partition Table 'T1' access range with Bind Variable 'C1' Column

• = Cardinality = 11111 * 0.2 * 0.2 = 444.4 = 445
• Distinct Count가 20보다 큰 경우 : Density가 0.05보다 작은 경우에는 5%의 Rule을 사용
• Distinct Count가 20보다 작은 경우 : Density가 0.05보다 큰 경우에는 Density를 사용

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

---------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE           |      |      1 |      1 |            |      1 |00:00:00.01 |      61 |
|*  2 |   FILTER                  |      |      1 |        |            |  11100 |00:00:00.01 |      61 |
|   3 |    PARTITION LIST ITERATOR|      |      1 |    445 |    23   (0)|  11100 |00:00:00.01 |      61 |
|   4 |     TABLE ACCESS FULL     | T1   |      3 |    445 |    23   (0)|  11100 |00:00:00.01 |      61 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(:B1<=:B2)