Oracle Partition Table execution plan

Oracle Partition Table execution plan

Oracle Partitioned Tables have many advantages, such as being big, small, and small. With parallel use, loap can often improve the performance by dozens or even hundreds of times. Of course, poor table design can also be counterproductive, and the effect is worse than that of normal tables.
To better use a partitioned table, let's take a look at the execution plan of the partitioned table.
Partition range all: scan ALL partitions
Partition range iterator: scans multiple partitions, less than the number of all partitions
Partition range single: scan a SINGLE PARTITION
KEY, indicating which partition is known during execution

When you see the keyword "ALL", you must note that ALL partitions are scanned. When writing SQL statements, it is best to make full use of the partition FIELD IN THE where condition. This can be used for partition pruning and does not need to be scanned for unnecessary partitions.

SQL> create table t1
2 partition by range (created )(
3 partition p1 values less than (to_date ('201312', 'yyyymmdd ')),
4 partition p2 values less than (to_date ('201312', 'yyyymmdd ')),
5 partition p3 values less than (to_date ('201312', 'yyyymmdd ')),
6 partition p4 values less than (to_date ('201312', 'yyyymmdd ')),
7 partition p5 values less than (to_date ('201312', 'yyyymmdd ')),
8 partition p6 values less than (to_date ('201312', 'yyyymmdd ')),
9 partition p7 values less than (to_date ('201312', 'yyyymmdd ')),
10 partition p8 values less than (to_date ('201312', 'yyyymmdd ')),
11 partition p9 values less than (to_date ('201312', 'yyyymmdd ')),
12 partition p10 values less than (to_date ('201312', 'yyyymmdd ')),
13 partition p11 values less than (to_date ('201312', 'yyyymmdd ')),
14 partition p12 values less than (to_date ('201312', 'yyyymmdd ')),
15 partition p13 values less than (maxvalue)
16)
17 as select * from dba_objects where created> = to_date ('201312', 'yyyymmdd ');
 
-- Partition range all: scan ALL partitions
SQL> explain plan for select count (*) from t1;
Bytes -------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (% CPU) | Time | Pstart | Pstop |
Bytes -------------------------------------------------------------------------------------
| 0 | select statement | 1 | 106 (1) | 00:00:02 |
| 1 | sort aggregate | 1 |
| 2 | partition range all | 41973 | 106 (1) | 00:00:02 | 1 | 13 |
| 3 | table access full | T1 | 41973 | 106 (1) | 00:00:02 | 1 | 13 |
Bytes -------------------------------------------------------------------------------------

-- Partition range iterator: scans multiple partitions, less than the number of all partitions
SQL> explain plan for select * from t1 where created> = to_date ('20140901', 'yyyymmdd ');
Bytes -------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |
Bytes -------------------------------------------------------------------------------------------------
| 0 | select statement | 13121 | 2267K | 39 (6) | 00:00:01 |
| 1 | partition range iterator | 13121 | 2267K | 39 (6) | 00:00:01 | 12 | 13 |
| * 2 | table access full | T1 | 13121 | 2267K | 39 (6) | 00:00:01 | 12 | 13 |
Bytes -------------------------------------------------------------------------------------------------

 

-- Partition range single: scan a SINGLE PARTITION
SQL> explain plan for select * from t1 where created> = to_date ('20140901', 'yyyymmdd ');
Bytes -----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |
Bytes -----------------------------------------------------------------------------------------------
| 0 | select statement | 947 | 28 (0) | 00:00:01 |
| 1 | partition range single | 947 | 28 (0) | 00:00:01 | 13 | 13 |
| * 2 | table access full | T1 | 947 | 28 (0) | 00:00:01 | 13 | 13 |
Bytes -----------------------------------------------------------------------------------------------

-- KEY indicates which partition is known during execution.
SQL> explain plan for select * from t1 where created> = sysdate-1;
Bytes -------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |
Bytes -------------------------------------------------------------------------------------------------
| 0 | select statement | 947 | 33 (16) | 00:00:01 |
| 1 | partition range iterator | 947 | 163K | 33 (16) | 00:00:01 | KEY | 13 |
| * 2 | table access full | T1 | 947 | 33 (16) | 00:00:01 | KEY | 13 |
Bytes -------------------------------------------------------------------------------------------------

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

2-filter ("CREATED"> = SYSDATE @! -1)

SQL> variable x varchar2;
SQL> explain plan for select * from t1 where created> = to_date (: x, 'yyyymmdd ');
Bytes -------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |
Bytes -------------------------------------------------------------------------------------------------
| 0 | select statement | 2099 | 362K | 107 (2) | 00:00:02 |
| 1 | partition range iterator | 2099 | 362K | 107 (2) | 00:00:02 | KEY | 13 |
| * 2 | table access full | T1 | 2099 | 362K | 107 (2) | 00:00:02 | KEY | 13 |
Bytes -------------------------------------------------------------------------------------------------

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

2-filter ("CREATED"> = TO_DATE (: X, 'yyyymmdd '))