Database Reference
In-Depth Information
EODA@ORA12CR1> select * from table(dbms_xplan.display(null,null,'BASIC +PARTITION'));
---------------------------------------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | PARTITION RANGE ALL | | 1 | 2 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PARTITIONED_TABLE | 1 | 2 |
| 3 | INDEX RANGE SCAN | LOCAL_NONPREFIXED | 1 | 2 |
---------------------------------------------------------------------------------------
Here the optimizer was
not
able to remove
PART_2
of
LOCAL_NONPREFIXED
from consideration—it needed to look
in both the
PART_1
and
PART_2
partitions of the index to see if
B=1
was in there. Herein lies a performance issue with
local nonprefixed indexes: they do not
make
you use the partition key in the predicate as a prefixed index does. It is
not that prefixed indexes are better; it's just that in order to use them, you must use a query that allows for partition
elimination.
If we drop the
LOCAL_PREFIXED
index and rerun the original successful query, as follows:
EODA@ORA12CR1> drop index local_prefixed;
Index dropped.
EODA@ORA12CR1> select * from partitioned_table where a = 1 and b = 1;
A B DATA
---------- ---------- --------------------
1 1 x
It succeeds, but as we'll see, it used the same index that just a moment ago failed us. The plan shows that Oracle
was able to employ partition elimination here—the predicate
A=1
was enough information for the database to
eliminate index partition
PART_2
from consideration:
EODA@ORA12CR1> explain plan for select * from partitioned_table where a = 1 and b = 1;
Explained.
EODA@ORA12CR1> select * from table(dbms_xplan.display(null,null,'BASIC +PARTITION'));
---------------------------------------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PARTITIONED_TABLE | 1 | 1 |
| 3 | INDEX RANGE SCAN | LOCAL_NONPREFIXED | 1 | 1 |
---------------------------------------------------------------------------------------
Note the
PSTART
and
PSTOP
column values of 1 and 1.This proves that the optimizer is able to perform partition
elimination even for nonprefixed local indexes.
If you frequently query the preceding table with the following queries, then you might consider using a local
nonprefixed index on (
b
,
a
):
select ... from partitioned_table where a = :a and b = :b;
select ... from partitioned_table where b = :b;
