Indexes - Pro Oracle Database 12c Administration - page 208

Table 8-1. ( continued )

Index Type Usage

Invisible The index is not visible to the query optimizer. However, the structure of the index is

maintained as table data are modified. Useful for testing an index before making it visible to
the application. Any index type can be created as invisible.

Global
partitioned

Global index across all partitions in a partitioned or regular table; can be a B-tree index type and
can't be a bitmap index type.

Local
partitioned

Local index based on individual partitions in a partitioned table; can be either a B-tree or
bitmap index type.

Domain Specific for an application or cartridge.

B-tree cluster Used with clustered tables.

Hash cluster Used with hash clusters.

■ several of the index types listed in table 8-1 are actually just variations on the B-tree index. A reverse-key
index, for example, is merely a B-tree index optimized for evenly spreading I/O when the index value is sequentially
generated and inserted with similar values.

Note

This chapter focuses on the most commonly used indexes and features—B-tree, function based, unique, bitmap,
reverse key, and key compressed—and the most used options. IOTs are covered in Chapter 7, and partitioned indexes are
covered in Chapter 12. If you need more information about index types or features, see the Oracle SQL Reference Guide ,
which is available for download from the Technology Network area of the Oracle web site ( http://otn.oracle.com ) .

Estimating the Size of an Index Before Creation
If you don't work with large databases, then you don't need to worry about estimating the amount of space an index
will initially consume. However, for large databases, you absolutely need an estimate on how much space it will take
to create an index. If you have a large table in a data warehouse environment, a corresponding index could easily be
hundreds of gigabytes in size. In this situation, you need to ensure that the database has adequate disk space available.
The best way to predict the size of an index is to create it in a test environment that has a representative set
of production data. If you can't build a complete replica of production data, a subset of data can often be used to
extrapolate the size required in production. If you don't have the luxury of using a cut of production data, you can
also estimate the size of an index using the DBMS_SPACE.CREATE_INDEX_COST procedure.
For reference, here is the table creation script that the index used in the subsequent examples is based on:

CREATE TABLE cust
(cust_id NUMBER
,last_name VARCHAR2(30)
,first_name VARCHAR2(30)
) TABLESPACE users;