composite partition plan
date : 2012.08.29(Wed)
list and hash partition
1) subparition을 직접 지정한 경우 : subpartition (P04_SP01);
PARTITION COMBINED ITERATOR 로 나오고 subpartition 통계정보 사용
2) partition을 직접 지정한 경우 : partition (P04)
PARTITION LIST SINGLE/PARTITION HASH ALL, Pstart/Pstop 상수
partition 통계정보 사용
3) partition/subpartition column을 상수로 사용한 경우
select count(*) from t where day = '4' and id = 'C';
PARTITION LIST SINGLE / PARTITION HASH SINGLE, Pstart/Pstop 상수
subpartition 통계정보 사용
4) 변수를 사용한 경우는 table 통계정보 사용
select count(*) from t where day = :v1 and id = :v2;
5) select count(*) from t where day = '4' and id = :v2;
table 통계정보 사용
-------------------------------------------------------------------------
| Id | Operation | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | 3 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | PARTITION LIST SINGLE | 3 (0)| 00:00:01 | 4 | 4 |
| 3 | PARTITION HASH SINGLE| 3 (0)| 00:00:01 | KEY | KEY |
|* 4 | TABLE ACCESS FULL | 3 (0)| 00:00:01 | KEY | KEY |
-------------------------------------------------------------------------
6) select count(*) from t where day = '4' and id >= :v2;
partition 통계정보 사용
------------------------------------------------------------------------
| Id | Operation | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | 9911 (100)| 00:00:26 | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | PARTITION LIST SINGLE| 9911 (100)| 00:00:26 | 4 | 4 |
| 3 | PARTITION HASH ALL | 9911 (100)| 00:00:26 | 1 | 4 |
|* 4 | TABLE ACCESS FULL | 9911 (100)| 00:00:26 | 13 | 16 |
------------------------------------------------------------------------
7) to_char(sysdate,'D'); 를 사용할 경우 partition 통계정보를 사용하는 것 같은데,
확실하지 않음. 맨 아래 plan 참조하세요.
=============================================================
-- composite partition
drop table t purge;
CREATE TABLE t
(
ID VARCHAR2 (1024) ,
DAY VARCHAR2(1)
)
PARTITION BY list (DAY)
SUBPARTITION BY HASH (ID)
SUBPARTITION TEMPLATE
(
SUBPARTITION SP01
, SUBPARTITION SP02
, SUBPARTITION SP03
, SUBPARTITION SP04
)
(
PARTITION P01 VALUES ('1')
,PARTITION P02 VALUES ('2')
,PARTITION P03 VALUES ('3')
,PARTITION P04 VALUES ('4')
,PARTITION P05 VALUES ('5')
,PARTITION P06 VALUES ('6')
,PARTITION P07 VALUES ('7')
)
LOGGING
ENABLE ROW MOVEMENT ;
EXEC DBMS_STATS.GATHER_TABLE_STATS(
ownname => USER
, TABNAME => 'T'
, estimate_percent => 100
, block_sample => TRUE
, method_opt => 'FOR ALL COLUMNS SIZE 1'
, granularity => 'ALL'
, force => TRUE);
------------------------------------------------------
=====================================================================
-- 일억
begin
dbms_stats.set_table_stats(
ownname => user
,tabname => 'T'
,partname => 'P04_SP01'
,numrows => 100000000
);
end;
begin
dbms_stats.set_table_stats(
ownname => user
,tabname => 'T'
,partname => 'P04'
,numrows => 100000000
);
end;
select to_char(sysdate,'D') from dual;
-- Partitin no 알기 Pstart, Pstop
select p.table_name, p.partition_name, s.subpartition_name
, p.subpartition_count * (p.partition_position-1) + s.subpartition_position part_no
from user_tab_partitions p, user_tab_subpartitions s
where p.table_name = 'T'
and p.partition_name = s.partition_name
order by p.table_name, p.partition_position, s.subpartition_position;
1) partition P04_SP01 : partition을 직접 지정한 경우
-> subpartition 통계정보 사용
EXPLAIN PLAN FOR
select count(*)
from t subpartition (P04_SP01);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
Plan hash value: 2478717134
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6758 (100)| 00:00:18 | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | PARTITION COMBINED ITERATOR| | 100M| 6758 (100)| 00:00:18 | KEY | KEY |
| 3 | TABLE ACCESS FULL | T | 100M| 6758 (100)| 00:00:18 | 13 | 13 |
---------------------------------------------------------------------------------------------
EXPLAIN PLAN FOR
select count(*)
from t subpartition (P04_SP02);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-> subpartition 통계정보 사용
PLAN_TABLE_OUTPUT
Plan hash value: 2478717134
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | PARTITION COMBINED ITERATOR| | 1 | 3 (0)| 00:00:01 | KEY | KEY |
| 3 | TABLE ACCESS FULL | T | 1 | 3 (0)| 00:00:01 | 14 | 14 |
---------------------------------------------------------------------------------------------
EXPLAIN PLAN FOR
select count(*)
from t partition (P04);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-> partition 통계정보 사용
PLAN_TABLE_OUTPUT
Plan hash value: 2109924533
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6758 (100)| 00:00:18 | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | PARTITION LIST SINGLE| | 100M| 6758 (100)| 00:00:18 | 4 | 4 |
| 3 | PARTITION HASH ALL | | 100M| 6758 (100)| 00:00:18 | 1 | 4 |
| 4 | TABLE ACCESS FULL | T | 100M| 6758 (100)| 00:00:18 | 13 | 16 |
---------------------------------------------------------------------------------------
EXPLAIN PLAN FOR
select count(*)
from t partition (P05);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-> partition 통계정보 사용
PLAN_TABLE_OUTPUT
Plan hash value: 2109924533
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | PARTITION LIST SINGLE| | 1 | 3 (0)| 00:00:01 | 5 | 5 |
| 3 | PARTITION HASH ALL | | 1 | 3 (0)| 00:00:01 | 1 | 4 |
| 4 | TABLE ACCESS FULL | T | 1 | 3 (0)| 00:00:01 | 17 | 20 |
---------------------------------------------------------------------------------------
2) partition 상수로 지정 -> partition 통계정보 사용
EXPLAIN PLAN FOR
select count(*) from t where day = '4';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-> partition 통계정보 사용
PLAN_TABLE_OUTPUT
Plan hash value: 2109924533
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 9911 (100)| 00:00:26 | | |
| 1 | SORT AGGREGATE | | 1 | 2 | | | | |
| 2 | PARTITION LIST SINGLE| | 1 | 2 | 9911 (100)| 00:00:26 | 4 | 4 |
| 3 | PARTITION HASH ALL | | 1 | 2 | 9911 (100)| 00:00:26 | 1 | 4 |
| 4 | TABLE ACCESS FULL | T | 1 | 2 | 9911 (100)| 00:00:26 | 13 | 16 |
-----------------------------------------------------------------------------------------------
EXPLAIN PLAN FOR
select count(*) from t where day = '5';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-> partition 통계정보 사용
PLAN_TABLE_OUTPUT
Plan hash value: 2109924533
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 3 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 2 | | | | |
| 2 | PARTITION LIST SINGLE| | 1 | 2 | 3 (0)| 00:00:01 | 5 | 5 |
| 3 | PARTITION HASH ALL | | 1 | 2 | 3 (0)| 00:00:01 | 1 | 4 |
| 4 | TABLE ACCESS FULL | T | 1 | 2 | 3 (0)| 00:00:01 | 17 | 20 |
-----------------------------------------------------------------------------------------------
3) partition 상수로 지정
EXPLAIN PLAN FOR
select count(*) from t where day = '4' and id = 'C';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-> subpartition 통계정보 사용
PLAN_TABLE_OUTPUT
Plan hash value: 671109269
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 516 | 9911 (100)| 00:00:26 | | |
| 1 | SORT AGGREGATE | | 1 | 516 | | | | |
| 2 | PARTITION LIST SINGLE | | 1 | 516 | 9911 (100)| 00:00:26 | 4 | 4 |
| 3 | PARTITION HASH SINGLE| | 1 | 516 | 9911 (100)| 00:00:26 | 1 | 1 |
|* 4 | TABLE ACCESS FULL | T | 1 | 516 | 9911 (100)| 00:00:26 | 13 | 13 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ID"='C')
EXPLAIN PLAN FOR
select count(*) from t where day = '4' and id = 'D';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-> subpartition 통계정보 사용
PLAN_TABLE_OUTPUT
Plan hash value: 671109269
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 516 | 3 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 516 | | | | |
| 2 | PARTITION LIST SINGLE | | 1 | 516 | 3 (0)| 00:00:01 | 4 | 4 |
| 3 | PARTITION HASH SINGLE| | 1 | 516 | 3 (0)| 00:00:01 | 4 | 4 |
|* 4 | TABLE ACCESS FULL | T | 1 | 516 | 3 (0)| 00:00:01 | 16 | 16 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ID"='D')
4) partition 변수로 지정
EXPLAIN PLAN FOR
select count(*) from t where day = :v;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-> table 통계정보 사용
PLAN_TABLE_OUTPUT
Plan hash value: 2109924533
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 3 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 2 | | | | |
| 2 | PARTITION LIST SINGLE| | 1 | 2 | 3 (0)| 00:00:01 | KEY | KEY |
| 3 | PARTITION HASH ALL | | 1 | 2 | 3 (0)| 00:00:01 | 1 | 4 |
| 4 | TABLE ACCESS FULL | T | 1 | 2 | 3 (0)| 00:00:01 | KEY | KEY |
-----------------------------------------------------------------------------------------------
EXPLAIN PLAN FOR
select count(*) from t where day = :v1 and id = :v2;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-> table 통계정보 사용
PLAN_TABLE_OUTPUT
Plan hash value: 2136208595
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 516 | 3 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 516 | | | | |
| 2 | PARTITION LIST SINGLE | | 1 | 516 | 3 (0)| 00:00:01 | KEY | KEY |
| 3 | PARTITION HASH SINGLE| | 1 | 516 | 3 (0)| 00:00:01 | KEY | KEY |
|* 4 | TABLE ACCESS FULL | T | 1 | 516 | 3 (0)| 00:00:01 | KEY | KEY |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ID"=:V2)
EXPLAIN PLAN FOR
select count(*) from t where day = '4' and id = :v2; :v2 = 'C'
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-> table 통계정보 사용
PLAN_TABLE_OUTPUT
Plan hash value: 2136208595
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 516 | 3 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 516 | | | | |
| 2 | PARTITION LIST SINGLE | | 1 | 516 | 3 (0)| 00:00:01 | 4 | 4 |
| 3 | PARTITION HASH SINGLE| | 1 | 516 | 3 (0)| 00:00:01 | KEY | KEY |
|* 4 | TABLE ACCESS FULL | T | 1 | 516 | 3 (0)| 00:00:01 | KEY | KEY |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ID"=:V2)
EXPLAIN PLAN FOR
select count(*) from t where day = '4' and id >= :v2; :v2 = 'C'
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-> partition 통계정보 사용
PLAN_TABLE_OUTPUT
Plan hash value: 2109924533
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 516 | 9911 (100)| 00:00:26 | | |
| 1 | SORT AGGREGATE | | 1 | 516 | | | | |
| 2 | PARTITION LIST SINGLE| | 1 | 516 | 9911 (100)| 00:00:26 | 4 | 4 |
| 3 | PARTITION HASH ALL | | 1 | 516 | 9911 (100)| 00:00:26 | 1 | 4 |
|* 4 | TABLE ACCESS FULL | T | 1 | 516 | 9911 (100)| 00:00:26 | 13 | 16 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ID">=:V2)
=================================================================
EXPLAIN PLAN FOR
select count(*) from t where day = to_char(sysdate,'D');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
Plan hash value: 2109924533
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 21170 (100)| 00:00:54 | | |
| 1 | SORT AGGREGATE | | 1 | 2 | | | | |
| 2 | PARTITION LIST SINGLE| | 1 | 2 | 21170 (100)| 00:00:54 | KEY | KEY |
| 3 | PARTITION HASH ALL | | 1 | 2 | 21170 (100)| 00:00:54 | 1 | 4 |
| 4 | TABLE ACCESS FULL | T | 1 | 2 | 21170 (100)| 00:00:54 | KEY | KEY |
-----------------------------------------------------------------------------------------------
EXPLAIN PLAN FOR
select count(*) from t partition (P04);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
Plan hash value: 2109924533
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6758 (100)| 00:00:18 | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | PARTITION LIST SINGLE| | 100M| 6758 (100)| 00:00:18 | 4 | 4 |
| 3 | PARTITION HASH ALL | | 100M| 6758 (100)| 00:00:18 | 1 | 4 |
| 4 | TABLE ACCESS FULL | T | 100M| 6758 (100)| 00:00:18 | 13 | 16 |
---------------------------------------------------------------------------------------
EXPLAIN PLAN FOR
select count(*) from t where day = to_char(sysdate-1,'D');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
Plan hash value: 2109924533
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 3 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 2 | | | | |
| 2 | PARTITION LIST SINGLE| | 1 | 2 | 3 (0)| 00:00:01 | KEY | KEY |
| 3 | PARTITION HASH ALL | | 1 | 2 | 3 (0)| 00:00:01 | 1 | 4 |
| 4 | TABLE ACCESS FULL | T | 1 | 2 | 3 (0)| 00:00:01 | KEY | KEY |
-----------------------------------------------------------------------------------------------
EXPLAIN PLAN FOR
select count(*) from t where day in ( to_char(sysdate-1,'D'), to_char(sysdate,'D') );
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
Plan hash value: 1253574174
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 3 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 2 | | | | |
| 2 | PARTITION LIST INLIST| | 1 | 2 | 3 (0)| 00:00:01 |KEY(I) |KEY(I) |
| 3 | PARTITION HASH ALL | | 1 | 2 | 3 (0)| 00:00:01 | 1 | 4 |
| 4 | TABLE ACCESS FULL | T | 1 | 2 | 3 (0)| 00:00:01 |KEY(I) |KEY(I) |
-----------------------------------------------------------------------------------------------
'Oracle > Admin' 카테고리의 다른 글
| [Oracle] 패치 정책 (0) | 2025.01.22 |
|---|---|
| [Oracle] Alert.log : Creating new log segment (0) | 2025.01.14 |
| [Oracle] 병렬 처리 (1) | 2024.12.27 |
| [Oracle] User Call vs Recursive Call (0) | 2024.12.17 |
| [Oracle] DBMS log 를 확인 하는 방법 정리 (1) | 2024.12.09 |
