queries for Oracle partitioned tablesIf the [partition table] is partitioned on the field [CreateDate], each month is a partition: June 2009 is partition P2009062009 year July is partition P2009072009 year August is partition P200908
Execute SQL using the partition key to retrieve:Select*from partition table T where createdate > To_date (' 2009-07-12 ', ' yyyy-mm-dd ') and CreateDate < To_date (' 2009-08-12 ', ' Yyyy-mm-dd ') execution plan is as follows, the partition key is used to index, it will be retrieved automatically in the area where the data exists. Since the starting area is 2, the end area is 3, at a glance. SELECT STATEMENT, GOAL = all_rowspartition RANGE ITERATOR Partition start=2 Partition stop=3 TABLE ACCESS full Partition start=2 Partition stop=3
Execute SQL does not use the partition key to retrieve:Select*from partition table T where sms_report_date > To_date (' 2009-07-12 ', ' yyyy-mm-dd ') and Sms_report_date < To_date (' 2009-08-12 ', ' yyyy-mm-dd ') The execution plan is as follows: If the partition key is not used for retrieval, then all table partitions will be queried. Because the data to be queried is on the 2 and 3 partitions, the other partition data is also read, increasing the database pressure and inefficiency. SELECT STATEMENT, GOAL = all_rows www.2cto.com PARTITION RANGE all PARTITION start=1 PARTITION stop=31 TABLE ACCESS full Partition start=1 Partition stop=31
Execute SQL Specifies that the partition is used:Select*from partition Table partition (P200907) t where sms_report_date > To_date (' 2009-07-12 ', ' yyyy-mm-dd ') and Sms_report_date & Lt To_date (' 2009-08-12 ', ' yyyy-mm-dd ') execution plan is as follows: SELECT STATEMENT, GOAL = all_rowspartition RANGE single Partition start=2 Partition stop=2 TABLE ACCESS full Partition start=2 Partition stop=2
Oracle Partitioned Table queries
