Database Reference
In-Depth Information
An interesting side-effect of logical rowids is that secondary indexes always contain the primary key, also if it's
not explicitly indexed. The following example illustrates how the database engine is able to extract the primary key
(
id
) from a secondary index created only on another column (
n
) by performing an index-only scan:
SQL> SELECT id FROM t WHERE n = 42;
---------------------------------
| Id | Operation | Name |
---------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | INDEX RANGE SCAN| I |
---------------------------------
1 - access("N"=42)
In addition to avoiding accessing a table segment, index-organized tables provide two more advantages that
shouldn't be underestimated. The first is that data is always clustered, and therefore, range scans based on the
primary key can always be performed efficiently, and not only when the clustering factor is low like with heap tables.
The second advantage is that range scans based on the primary key always return the data in the order in which the
data is stored in the primary key index. This could be useful for optimizing
ORDER BY
operations.
Global, Local, or Nonpartitioned Indexes?
With partitioned tables, it's common to create local partitioned indexes. The main advantage of doing so is to
reduce the dependencies between indexes and table partitions. For example, it makes things much easier when
partitions are added, dropped, truncated, or exchanged. Simply put, creating local indexes is generally good.
Nevertheless, there are situations where it's not possible or not advisable to do so.
preFIXeD VS. NONpreFIXeD INDeXeS
an index is prefixed if the partition key is the left prefix of the index columns and, for subpartitioned indexes, the
subpartioning key is included in the index key. While local indexes can be prefixed or nonprefixed, only global
prefixed indexes can be created.
according to the
Oracle Database VLDB and Partitioning Guide
manual, nonprefixed indexes don't perform as
well as prefixed indexes. In practice, I've never seen a performance problem related to the fact that an index was
nonprefixed. my advice is therefore to create the most sensible index without being concerned about whether it's
prefixed or nonprefixed.
The first problem is related to primary keys and unique indexes. In fact, to be based on local indexes, their keys
must contain the partition key. Although this is sometimes possible, more often than not there's no such possibility
without distorting the logical database design. This is especially true when range partitioning is used. So, in my
opinion, this should be considered only as a last resort. You should never mess up the logical design. Because the
logical design can't be changed, only two other possibilities remain. The first is to create a non-partitioned index. The
second is to create a global partitioned index. The latter should be implemented only if there's a real advantage in
doing so. Because such indexes are commonly hash partitioned, however, it's advantageous to do it only for very large
indexes or for indexes experiencing a very high load. In summary, it's not uncommon at all to create nonpartitioned
