Databases Reference
In-Depth Information
Partition-Level Requirements
If you have specific partition-level requirements, you will need to specify each partition within your
CREATE INDEX DDL. For example,
SQL> CREATE INDEX employees_part_i1
2 ON employees_part (hire_date)
3 LOCAL
4 (partition pi1990 tablespace EMP1990_S
5 ,partition pi1991 tablespace EMP1991_S
6 ,partition pi1992 tablespace EMP1992_S
7 ,partition pi1993 tablespace EMP1993_S
8 ,partition pi1994 tablespace EMP1994_S
9 ,partition pi1995 tablespace EMP1995_S
10 ,partition pi1996 tablespace EMP1996_S
11 ,partition pi1997 tablespace EMP1997_S
12 ,partition pi1998 tablespace EMP1998_S
13 ,partition pi1999 tablespace EMP1999_S
14 ,partition pi2000 tablespace EMP2000_S
15 ,partition pimax tablespace EMPMAX_S);
In this example, the partition names were modified to insert an 'I' to note index partition. In order to
have different partition names for indexes, each partition needs to be specified in the CREATE INDEX
DDL. You also specified different tablespaces for each partition, which represents a year's worth of data.
By putting each year in its own tablespace, now tablespaces for previous years' data can be made read-
only. This can help both in query speed and backup speed, as you won't need to back up read-only
tablespaces with each backup of a database.
Again, to create a local partitioned index, it must be on top of a partitioned table. If not, you'll
receive the following error:
SQL> CREATE INDEX EMPLOYEES_I1
ON EMPLOYEES (HIRE_DATE)
TABLESPACE EMPINDEX_S
LOCAL;
2 3 4 ON EMPLOYEES (HIRE_DATE)
*
ERROR at line 2:
ORA-14016: underlying table of a LOCAL partitioned index must be partitioned
Prefixed and Non-Prefixed Options
Locally partitioned indexes can be created as prefixed or non-prefixed. When you create a prefixed
locally-partitioned index, it means that the partitioning column(s) for the table are on the leading edge
of the index. If the partitioning column(s) are not on the leading edge, it is regarded as a non-prefixed
index. In earlier versions of Oracle, having a local index as prefixed offered performance advantages over
its non-prefixed counterpart. With later versions of Oracle, including version 11gR2, the advantages of
creating local indexes as prefixed have diminished. However, if your database environment is an OLTP
system, it still benefits query performance to have local prefixed indexes over non-prefixed indexes, as
the optimizer will potentially scan less index partitions in order to retrieve the data for a query. Refer to
