Oracle Partitioned Table Execution plan

Partitioned tables have many advantages, such as small, small, and parallel use, in the LOAP can often improve the effect of dozens of times times or even hundreds of times times. Of course, the design of the table is not good and counterproductive, the effect is worse than the normal table. To better use the partitioned table, take a look at the execution plan for the partitioned table. PARTITION Range All: Scans all partitions PARTITION range ITERATOR: Scans multiple partitions, less than the number of partitions PARTITION range Single: Scans the individual partition key, Indicates that the partition will be aware when it sees the keyword all when it is executed, and all the partitions are scanned. When writing SQL, the best way to limit the use of partitioning fields in the where condition is to be able to cut partitions so that unnecessary partitions do not have to be scanned. sql> CREATE TABLE T1 2 partition by range (created) (3 partition P1 values less than (to_date (' 20140101 ', ' YYYYMMDD ') ), 4 partition P2 values less than (to_date (' 20140201 ', ' YYYYMMDD '), 5 partition P3 values less than (To_date (' 2014030 1 ', ' YYYYMMDD '), 6 partition P4 values less than (to_date (' 20140401 ', ' YYYYMMDD ')), 7 partition P5 values less than (to  _date (' 20140501 ', ' YYYYMMDD '), 8 partition P6 values less than (to_date (' 20140601 ', ' YYYYMMDD ')), 9 partition P7 values Less Than (to_date (' 20140701 ', ' YYYYMMDD ')), partition P8 values less than (to_date (' 20140801 ', ' YYYYMMDD ')), parti tion P9 values less than (to_date (' 20140901 ', ' YYYYMMDD '), and partition P10 values less thAn (to_date (' 20141001 ', ' YYYYMMDD ')), partition P11 values less than (to_date (' 20141101 ', ' YYYYMMDD ')), partition P1 2 values less than (to_date (' 20141201 ', ' YYYYMMDD '), partition P13 values less than (MaxValue)) + as SELECT * F Rom dba_objects where created>=to_date (' 20131001 ', ' yyyymmdd '); --partition RANGE All: Scans all partitions sql> explain plan for select COUNT (*) from T1;------------------------------------------ -------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU) | Time | Pstart|   Pstop |-------------------------------------------------------------------------------------| 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 | ------------------|---------------------------------------------------------------------PARTITION RANGE ITERATOR: Scan multiple partitions, Less than the total number of partitions sql> explain plan for SELECT * from T1 where created>=to_date (' 20141101 ', ' yyyymmdd ');------------------- ------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart|   Pstop |-------------------------------------------------------------------------------------------------| 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 | ---------------------------------------------------------------------------------------------------| PARTITION RANGE Single: Scans for one partition sql> explain plan for SELECT * from T1 where created>=to_date (' 20141217 ', ' YYYYMMDD');-----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart|   Pstop |-----------------------------------------------------------------------------------------------| 0 |      SELECT STATEMENT |   |   947 |    163k| 28 (0) |       00:00:01 |       |   ||  1 |      PARTITION RANGE single|   |   947 |    163k| 28 (0) |    00:00:01 |    13 | 13 | |   * 2 | TABLE ACCESS Full |   T1 |   947 |    163k| 28 (0) |    00:00:01 |    13 | -------------------------------------------------------------------------------------------------KEY, Represents the execution to know which partition sql> explain plan for SELECT * from T1 where created>=sysdate-1;------------------------------------- ------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |-------------------------------------------------------------------------------------------------| 0 |      SELECT STATEMENT |   |   947 |    163k| 33 (16) |       00:00:01 |       |   ||  1 |      PARTITION RANGE iterator|   |   947 |    163k| 33 (16) |   00:00:01 |    KEY | 13 | |   * 2 | TABLE ACCESS Full |   T1 |   947 |    163k| 33 (16) |   00:00:01 |    KEY | -------------------------------------------------------------------------------------------------predicate Information (identified by Operation ID):---------------------------------------------------2-filter ("CREATED" > [email protected]!-1] sql> variable x varchar2; Sql> explain plan for SELECT * from T1 where created>=to_date (: x, ' YYYYMMDD ');------------------------------------ -------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart|   Pstop |-------------------------------------------------------------------------------------------------| 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 | -------------------------------------------------------------------------------------------------predicate Information (identified by Operation ID):---------------------------------------------------2-filter ("CREATED" > =to_date (: X, ' YYYYMMDD '))

Oracle Partitioned Table Execution plan