Partition Pruning

最新推荐文章于 2025-12-19 15:39:34 发布

转载最新推荐文章于 2025-12-19 15:39:34 发布 · 177 阅读

文章标签：

本文介绍Oracle数据库如何通过分区和子分区识别优化SQL查询性能。分区剪除技术能有效减少不必要的索引和数据分区访问，显著提升查询效率。文章详细解释了在不同类型的分区条件下，如何利用等值、范围、LIKE及IN列表谓词实现分区剪除。

The Oracle database server explicitly recognizes partitions and subpartitions. It then
optimizes SQL statements to mark the partitions or subpartitions that need to be
accessed and eliminates (prunes) unnecessary partitions or subpartitions from access
by those SQL statements. In other words, partition pruning is the skipping of
unnecessary index and data partitions or subpartitions in a query.

For each SQL statement, depending on the selection criteria specified, unneeded
partitions or subpartitions can be eliminated. For example, if a query only involves
March sales data, then there is no need to retrieve data for the remaining eleven
months. Such intelligent pruning can dramatically reduce the data volume, resulting
in substantial improvements in query performance.

If the optimizer determines that the selection criteria used for pruning are satisfied by
all the rows in the accessed partition or subpartition, it removes those criteria from the
predicate list (WHERE clause) during evaluation in order to improve performance.
However, the optimizer cannot prune partitions if the SQL statement applies a
function to the partitioning column (with the exception of the TO_DATE function).
Similarly, the optimizer cannot use an index if the SQL statement applies a function to
the indexed column, unless it is a function-based index.

当对分区列进行函数运算时，优化器不能进行分区剪除。

Pruning can eliminate index partitions even when the underlying table's partitions
cannot be eliminated, but only when the index and table are partitioned on different
columns. You can often improve the performance of operations on large tables by
creating partitioned indexes that reduce the amount of data that your SQL statements
need to access or modify.

Equality, range, LIKE, and IN-list predicates are considered for partition pruning with
range or list partitioning, and equality and IN-list predicates are considered for
partition pruning with hash partitioning.

等于，范围，like,in list 都能进行分区剪除，其中等于和in list 进行哈希分区剪除。

Partition Pruning Example
We have a partitioned table called cust_orders. The partition key for cust_orders
is order_date. Let us assume that cust_orders has six months of data, January to
June, with a partition for each month of data. If the following query is run:
SELECT SUM(value)
FROM cust_orders
WHERE order_date BETWEEN '28-MAR-98' AND '23-APR-98';
Partition pruning is achieved by:
■ First, partition elimination of January, February, May, and June data partitions.
Then either:
■ An index scan of the March and April data partition due to high index selectivity
or
■ A full scan of the March and April data partition due to low index selectivity

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10599713/viewspace-1002503/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10599713/viewspace-1002503/