Partitioning - Expert Oracle Database Architecture - page 621

That index would be useful for both of the preceding queries. The local prefixed index on ( a , b ) would be useful
only for the first query.
The bottom line here is that you should not be afraid of nonprefixed indexes or consider them as major
performance inhibitors. If you have many queries that could benefit from a nonprefixed index as outlined previously,
then you should consider using one. The main concern is to ensure that your queries contain predicates that allow for
index partition elimination whenever possible. The use of prefixed local indexes enforces that consideration. The use
of nonprefixed indexes does not. Consider also how the index will be used. If it will be used as the first step in a query
plan, there are not many differences between the two types of indexes.
Local Indexes and Unique Constraints
To enforce uniqueness—and that includes a UNIQUE constraint or PRIMARY KEY constraints—your partitioning key
must be included in the constraint itself if you want to use a local index to enforce the constraint. This is the largest
limitation of a local index, in my opinion. Oracle enforces uniqueness only within an index partition—never across
partitions. What this implies, for example, is that you cannot range partition on a TIMESTAMP field and have a primary
key on the ID that is enforced using a locally partitioned index. Oracle will instead utilize a global index to enforce
uniqueness.
In the next example, we will create a range partitioned table that is partitioned by a column named TIMESTAMP
but has a primary key on the ID column. We can do that by executing the following CREATE TABLE statement in a
schema that owns no other objects, so we can easily see exactly what objects are created by looking at every segment
this user owns:
EODA@ORA12CR1> CREATE TABLE partitioned
2 ( timestamp date,
3 id int,
4 constraint partitioned_pk primary key(id)
5 )
6 PARTITION BY RANGE (timestamp)
7 (
8 PARTITION part_1 VALUES LESS THAN
9 ( to_date('01/01/2014','dd/mm/yyyy') ) ,
10 PARTITION part_2 VALUES LESS THAN
11 ( to_date('01/01/2015','dd/mm/yyyy') )
12 )
13 /
Table created.
And inserting some data so that we get segments created:
EODA@ORA12CR1> insert into partitioned values(to_date('01/01/2013','dd/mm/yyyy'),1);
1 row created.
EODA@ORA12CR1> insert into partitioned values(to_date('01/01/2014','dd/mm/yyyy'),2);
1 row created.