Database Reference
In-Depth Information
There is nothing inherently better about a local prefixed index as opposed to a local nonprefixed index when that
index is used as the initial path to the table in a query. What I mean is that if the query can start with “
scan an index
”
as the first step, there isn't much difference between a prefixed and a nonprefixed index.
Partition Elimination Behavior
For the query that starts with an index access, whether or not it can eliminate partitions from consideration all really
depends on the predicate in your query. A small example will help demonstrate this. The following code creates a
table,
PARTITIONED_TABLE
, that is range partitioned on a numeric column
A
such that values less than two will be in
partition
PART_1
and values less than three will be in partition
PART_2
:
EODA@ORA12CR1> CREATE TABLE partitioned_table
2 ( a int,
3 b int,
4 data char(20)
5 )
6 PARTITION BY RANGE (a)
7 (
8 PARTITION part_1 VALUES LESS THAN(2) tablespace p1,
9 PARTITION part_2 VALUES LESS THAN(3) tablespace p2
10 )
11 /
Table created.
We then create both a local prefixed index,
LOCAL_PREFIXED
, and a local nonprefixed index,
LOCAL_NONPREFIXED
.
Note that the nonprefixed index does not have
A
on the leading edge of its definition, which is what makes it a
nonprefixed index:
EODA@ORA12CR1> create index local_prefixed on partitioned_table (a,b) local;
Index created.
EODA@ORA12CR1> create index local_nonprefixed on partitioned_table (b) local;
Index created.
Next, we'll insert some data into one partition and gather statistics:
EODA@ORA12CR1> insert into partitioned_table
2 select mod(rownum-1,2)+1, rownum, 'x'
3 from dual connect by level <= 70000;
70000 rows created.
EODA@ORA12CR1> begin
2 dbms_stats.gather_table_stats
3 ( user,
4 'PARTITIONED_TABLE',
5 cascade=>TRUE );
6 end;
7 /
PL/SQL procedure successfully completed.
