Oracle Partition Table execution plan, oracle execution plan

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.