Databases Reference
In-Depth Information
Virtual Index
You can instruct Oracle to create an index that will never be used and won't have any extents allocated to
it via the
NOSEGMENT
clause.
create index cust_idx1
on cust(first_name) nosegment;
Even though this index is not physically instantiated, you can instruct Oracle to determine if the
index might be used by the optimizer via the
_USE_NOSEGMENT_INDEXES
initialization parameter. For
example,
SQL> alter session set "_use_nosegment_indexes"=true;
When would this be useful? If you have a very large index that you want to create without allocating
space, to determine if the index would be used by the optimizer, creating an index with
NOSEGMENT
allows
you to test that scenario. If you determine that the index would be useful, you can drop the index and re-
create it without the
NOSEGMENT
clause.
Invisible Index
An invisible index means the optimizer doesn't use the index when retrieving data for a query. However,
the index structure is still maintained as the underlying table has records inserted, updated, or deleted.
This feature is used when you want to test the viability of an index without impacting existing
application code. Use the
INVISIBLE
keyword to create an invisible index.
create index cust_iidx1
on cust(last_name) invisible;
Note
Invisible indexes are only available in Oracle Database 11g and higher.
Globally and Locally Partitioned Indexes
A partitioned index is one where you have one logical index, but physically the index is implemented in
several different segments. This allows for good performance even with very large databases. A
partitioned index can be either global or local.
Note
Partitioning is an extra-cost option available only with the Oracle Enterprise Edition of the database.
A globally partitioned index is where the index uses a partitioning strategy that is not mapped to the
underlying table's segments. You can build a globally partitioned index on a regular table or a
