Prefixed vs Non prefixed

Three index types are supported in the two groups mentioned above:

- Local prefixed.
- Local non prefixed.
- Global prefixed.

Prefixed vs. Non prefixed; what is the difference?

[@more@] An index is considered prefixed if its partitioning key/keys constitute
the left part of the index key, columns being kept in the same order.

Non prefixed global indexes are not supported.

Non-partitioned indexes are considered to be global prefixed indexes.

We will use the following base table for the examples of each type of index:

CREATE TABLE emp
(empno NUMBER NOT NULL,
ename VARCHAR2(10) NOT NULL,
job VARCHAR2(14),
deptpno NUMBER)
PARTITION BY RANGE (empno)
(PARTITION part1 VALUES LESS THAN (30),
PARTITION part2 VALUES LESS THAN (60),
PARTITION part3 VALUES LESS THAN (MAXVALUE));


IV.1 Prefixed Local Index:
---------------------------

CREATE INDEX emp_idx1 ON emp(empno) LOCAL;

The index is considered as prefixed because the index key 'empno' is
identical to the partitioning key. This index is defined as 'local'.
It is thus partitioned automatically by Oracle on the same key as the
emp table, namely 'empno'. There is, therefore, no need to define
again the partitioning criteria tied to the index. Oracle automatically
creates three index partitions based on the same partition limits as
those of the emp table. Each of the index keys is stored in a partition
whose limits are identical to those of the base table.

The main advantage of the prefixed mode is that it enables the optimizer
to ignore the index partitions that do not match the search criteria tied
to the index key.

Look at the following query:

select * from emp where empno = 62;

The optimizer avoids the first two partitions and goes directly to the
third index partition.

emp_idx1 Index

empno empno empno
partitions -> 0-29 30-59 60-MAXVALUE

Index key -> empno
0-29 30-59 60-MAXVALUE

table emp -> empno


IV.2 Non-prefixed Local Index:
-------------------------------

CREATE INDEX emp_idx2 ON emp(deptno) LOCAL;

In the above example, Oracle creates three index partitions, equi-
partitioned with the base table. The physical storage of the index
keys, on the other hand, does not follow the partitioning rule. This
implies the spreading of the index keys in the different partitions.
Thus queries that include index key based predicates will force the
optimizer to scan all the index partitions in order to retrieve the
selected values.

In the following query:

Select * from emp where deptno = X;

the optimizer must scan all three partitions to the index emp_idx2
in order to locate the particular index key. The value specified
in the query's predicate can be in any one of the three index partitions.

emp_idx2
empno
partitions -> 0-29 30-59 60-MAXVALUE

Index Key -> deptno
55-95 56-18 57-82

Emp table -> empno

For non-prefixed unique key local indexes, it is mandatory that the
index key be a subset of the partitioning key in order for an index
key to only belong to one partition at a time.


IV.3 Global prefixed index:
----------------------------

CREATE INDEX emp_idx3 ON emp(ename)
GLOBAL PARTITION BY RANGE (ename)
(PARTITION p1 VALUES LESS THAN ('N'),
PARTITION p2 VALUES LESS THAN (MAXVALUE));

Using Global Prefixed Indexes allows a partitioning key different
from that of the underlying table. This type of index must be
prefixed. The index key is based on the partitioning key, which
enables the optimizer to select which of the partitions are
concerned by the query's predicate.

Consider the following query:

select * from emp where ename like 'B%';

The optimizer automatically selects the first index partition.
On the other hand, rows that make up this partition have ROWIDs
referencing rows in other table partitions.

emp_Idx3

partitions -> ename
A-M

Index key -> ename
A-M N-MAXVALUE

emp -> empno

Spreading index access over the different table partitions induces
performance loss as disk I/O increases. Global indexes are harder
to manage than local indexes since Oracle does not maintain the
index partitioning automatically in case of table reorganization.
A change in the table's storage causes all the index's partitions
to be put in UNUSABLE status. Index rebuild is then necessary, and
its duration is proportional to the size of the table rather than
to the size of one of its partitions.


IV.4 Guidelines to Partitioned Indexes:
----------------------------------------

The choice of a particular type of index is tied to the constraints
of your Oracle applications. A trade-off has to be found between
performance of the jobs, availability, and ease of administration.

The main rules to remember are as follows:

- Local indexes are easier to maintain and offer a higher availability
than global indexes.
- Prefixed indexes enable the optimizer to limit the probe to the
partitions directly concerned by the query predicates.
- Local prefixed indexes and global indexes are more adapted to an
OLTP styles of use whereas local non-prefixed indexes are better
adapted to data-warehousing and decision making environments.

Ask yourself the following questions before you choose a particular
type of partitioned index:

If your index is partitioned on a left prefix of the index columns,
then ===> Local prefixed

If you want to create a unique index, whose columns do not include
your partitioning keys ===> Global prefixed

If you can use parallel access to the different partitions and mainly
work in a DSS environment ===> Local non-prefixed

If you work in an OLTP environment ===> Global prefixed

Local non-prefixed indexes are particularly useful for historical type
databases in which the data is partitioned on a DATE type criteria.
This index is useful in efficiently probing the database with particular
criteria, different from a date. Maintenance operations tied to adding
or dropping a partition are simpler.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/38542/viewspace-926955/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/38542/viewspace-926955/