Oracle Partition Table execution plan, oracle execution plan

Oracle Partition Table execution plan, oracle execution plan

Partition tables have many advantages: they are 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: scan multiple partitions, less than the number of ALL partitions partition range single: scan a single partition key, indicates the partition that is only known when the keyword "ALL" is displayed. 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 ('20170101', 'yyyymmdd ')), 6 partition p4 values less than (to_date ('201312', 'yyyymmdd'), 7 partition p5 values less than (to_date ('20170101', 'yyyymmdd ')), 8 partition p6 valu Es less than (to_date ('20140901', 'yyyymmdd'), 9 partition p7 values less than (to_date ('20140901', 'yyyymmdd ')), 10 partition p8 values less than (to_date ('201312', 'yyyymmdd'), 11 partition p9 values less than (to_date ('20170101', 'yyyymmdd ')), 12 partition p10 values less than (to_date ('201312', 'yyyymmdd'), 13 partition p11 values less than (to_date ('2017010', '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 ('000000', 'yyyymmdd'); -- partition range all: scan ALL partitions SQL> explain plan for select count (*) from t1; Role | Id | Operation | Name | Rows | Cost (% CPU) | Time | Pstart | Pstop | STATEMENT | 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 | average -------------------------------------------------------------------------------------- -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 '); role | 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 | partition range single: scan a single partition SQL> explain plan for select * from t1 where created> = to_d Ate ('20140901', 'yyyymmdd'); route | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop | latency | 0 | select statement | 947 | 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 | 13 | primary KEY, explain plan for select * from t1 where created> = sysdate-1; partition | Id | Operation | Name | Ro Ws | Bytes | Cost (% CPU) | Time | Pstart | Pstop | percent | 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 | 13 | ------------------------------------------- Required 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 '); role | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop | percent | 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 | identified Predicate Information (identified by operation id): CREATED 2-filter ("CREATED"> = TO_DATE (: X, 'yyyymmdd '))