Partition Pruning
What is Partition Pruning?
Some tables are so large that a Full Table Scan is unthinkable. If these tables are not partitioned, they should be.
[@more@]The most common (by far) form of partitioning is Range Partitioning on a DATE column. For example, a table may be partitioned on TXN_DATE, with a separate partition for each year, month, or even day (I have seen a multi-terabyte database where daily partitions were the norm!).
If Oracle can use the WHERE predicates to eliminate some partitions from a search, then it probably will. This is called Partition Pruning. For example:
SELECT * FROM my_big_partition_table WHERE calendar_date = :b1 SELECT STATEMENT PARTITION RANGE SINGLE TABLE ACCESS FULL MY_BIG_PARTITION_TABLE SELECT * FROM my_big_partition_table WHERE calendar_date >= :b1 SELECT STATEMENT PARTITION RANGE ITERATOR TABLE ACCESS FULL MY_BIG_PARTITION_TABLE
Explain Plan lines showing PARTITION RANGE SINGLE
or PARTITION RANGE ITERATOR
indicate that Oracle is performing a Partition Prune. A line of PARTITION RANGE ALL
indicates Oracle is scanning all partitions. Depending on your Explain Plan tool, there is one confusing situation: if Oracle can work out exactly which partition or partitions to scan, then the step is removed from the plan. eg.
SELECT * FROM my_big_partition_table WHERE calendar_date = to_date('01-MAY-2003','DD-MON-YYYY') SELECT STATEMENT TABLE ACCESS FULL MY_BIG_PARTITION_TABLE
This looks like Oracle is performing a Full Table Scan of all partitions, but it is not. If that were the case, the plan would look like this:
SELECT STATEMENT PARTITION RANGE ALL TABLE ACCESS FULL MY_BIG_PARTITION_TABLE
New Explain Plan tools will show the actual partition number(s) thus eliminating this confusion.
When can I use Partition Pruning?
There are three ways to exploit partitons for performance:
Use your range partiton key in =, [=], BETWEEN, or LIKE predicates, comparing the key to either literals, bind variables, literal / bind variable expressions, or non-correlated sub-queries. eg.
- col = :my_date
- col BETWEEN :my_date AND :my_date + 3
- col = (SELECT processing_date FROM current_processing_date)
For List and Hash partitions, use = or IN predicates.
Perform a join to a partitoned table using the partiton key in an equals clause, where one of the above rules can be derived transitively. eg.
SELECT a.* FROM table_a a, big_partitioned_table b WHERE a.calendar_date > :a AND a.calendar_date = b.calendar_date
Here, Oracle can use transitive rules to learn something about the partiton key. eg. If A > :x, and A = B, then B > :x
Perform a partition-wise join. If you have two tables that are partitioned the same way, then even if you have to scan the entire table, you can make the Hash or Sort-Merge join faster by joining matching partitions. eg.
SELECT * FROM big_partitioned_a a, big_partitioned_b b WHERE a.key1 = b.key1 AND a.key2 = b.key2 AND a.calendar_date = b.calendar_date SELECT STATEMENT PARTITION RANGE ALL HASH JOIN TABLE ACCESS FULL ON BIG_PARTITIONED_A TABLE ACCESS FULL ON BIG_PARTITIONED_B
How to fix SQLs that won't Partition Prune
- If you are not using the partition key, but you are using another low-cardinality key, then speak to the DBA about Hash (v8i and above) or List (V9i and above) sub-partitions within the existing Range partitions.
- The syntax
WHERE partition_key oper (sub-query)
will only perform a partition prune for = and IN operators; this is consistent with Index Scans. For >[=] or WHERE partition_key oper my_func() (NB. Do not pass any columns from the query into the function as arguments; it won't partition prune). For = and IN sub-queries that won't Partition Prun, make sure that the sub-query is not correlated. - Use ranges instead of functions of partition keys. eg.
Don't useWHERE to_char(calendar_date,'MON-YYYY') = 'JAN-2003'
Instead useWHERE calendar_date BETWEEN '01-JAN-2003' and '01-FEB-2003' - 0.00001
- Never denormalize the partition key into other columns, because queries on those other columns will not partition prune. eg. If
calendar_date
were the partition key, do not create another column such ascalendar_month
that is derived fromcalendar_date
. Instead, create a date lookup (dimension) table that does the denormalization for you and use the STAR_TRANSFORMATION hint. eg.SELECT /*+ STAR_TRANSFORMATION*/ * FROM big_partitioned a, months b WHERE b.calendar_month = '200304' AND a.calendar_date BETWEEN b.month_start and b.month_end
If you are joining on the partition key, but not using equals joins, then you may have a design problem. eg.
- SELECT *
FROM big_partitioned_a a, big_partitioned_b b
WHERE a.calendar_date = :a
AND a.key = b.key
AND b.calendar_date <= a.calendar_date
- If the join is only performed once per day or less, a Nested Loops indexed join to big_partitioned_b may be faster than a hash join with full table scan on big_partitioned_b.
- If the join is more frequent, or if the query is not constrained to a single day or small subset of big_partitioned_a, then the results of the join should be built incrementally into a de-normalised table over nignt. Every day, select the new rows from big_partitioned_a and use an indexed Nested Loop join to big_partitioned_b, inserting the combined results into partitioned table big_partitioned_ab.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/76065/viewspace-1013039/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Pruning、Reference Partition、Exchange Partition
- Partition Pruning和Partition-Wise Joins
- 分割槽剪除 (partition pruning)
- Oracle的分割槽修剪介紹:Partition PruningOracle
- MOE pruning
- partition table and partition indexIndex
- PARTITION partition01
- PARTITION partition02
- PARTITION partition04
- partition timestamp(0) not use partition
- PARTITION SPILT
- hive partitionHive
- over (partition by)
- exchange partition
- oracle partitionOracle
- split partition
- Clique Partition
- LeetCode之Binary Tree Pruning(Kotlin)LeetCodeKotlin
- [論文分享] DHP: Differentiable Meta Pruning via HyperNetworks
- oracle partition by group by,詳解partition by和group by對比Oracle
- partition table update partition-key result in changing tablespace
- sql shard/partitionSQL
- partition table test
- partition table(1)
- partition table(2)
- exchange partition(轉)
- When to Partition a Table
- oracle分割槽partition及分割槽索引partition index(一)Oracle索引Index
- ORA-14257: cannot move partition other than a Range or Hash partition
- oracle composite partition組合分割槽_composite partition rangeOracle
- DMCP: Differentiable Markov Channel Pruning for Neural Networks 閱讀筆記筆記
- 7.74 DATAOBJ_TO_PARTITIONOBJ
- 86. Partition List
- exchange partition原理探究
- exchange partition 實驗
- oracle partition的方法Oracle
- exchange partition 的用法
- 關於Hash Partition