U need to check Partition Pruning techniques
> Check if U have any funtion on the index columns
> Make sure U have LOCAL prefixed indexes for the range key
========================================
Partition Pruning
The Oracle 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.
Partition Pruning Example
We have a partitioned table called orders. The partition key for orders is
order_date. Let's assume that 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 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
PURPOSE
-------
The purpose of the article is to discuss the partition elimination/pruning
feature
in oracle and how to verify if the partition elimination/pruning is
happening.
SCOPE & APPLICATION
-------------------
DBAs, Application Developers
Partition Pruning/Elimination
-----------------------------
One of the more desirable features of partitioning is partition pruning also
referred to as partition elimination). This is the process wherein the
optimizer
transparently eliminates partitions from the partition access list. This can
significantly reduce the amount of data accessed by a particular query. A
common
example involves sales data, partitioned quarterly. Without table
partitioning,
you may be required to scan the entire table for dates falling within a
particular quarter. With partition pruning, the optimizer will eliminate 3
of
the partitions, and only scan the partition with the relevant range of
dates.
The partition key does not have to be a date column. The following example
illustrates parition pruning on a range-partitioned table:
Cont....'d
================================================
HTHU
Ankur Shah
Oracle DBA
DHR-GA
----- Original Message -----
From: "Wilkinson, Marcus" <[Email Address Removed] "LazyDBA.com Discussion" <[Email Address Removed] Tuesday, April 20, 2004 6:53 PM
Subject: Problem SQL cont......again..
Three of the tables are partitioned 16 ways so that explains why I see
each table scanned 16 times.
Why does each partition get scanned one at a time? The table has a
degree of parallel set and the explain shows that it is running parallel
queries.
Any ideas why each partition is scanned in serial?
Oracle LazyDBA home page