RelationalDBDesign RelationalDBDesign


Oracle Indexes   «Prev  Next»
Lesson 8 Data dictionary views about indexes
ObjectiveView information about indexes in the data dictionary.

Data dictionary views about constraints

You can find out more about indexes through views in the data dictionary. There are two basic views that contain information about indexes, the USER_INDEXES view and the USER_IND_COLUMNS view.
The USER_INDEXES view contains the following columns, among others:

INDEX_NAME: The name of the index
INDEX_TYPE: The type of index
TABLE_OWNER: The schema that owns the table the index is associated with
TABLE_NAME: The name of the table the index is associated with
UNIQUENESS: Will either have the value of UNIQUE or NONUNIQUE
TABLESPACE_NAME: The name of the tablespace that contains the index
BLEVEL: The depth of the B*-tree structure
LEAF_BLOCKS: The number of leaf blocks in the index
DISTINCT_KEYS: The number of distinct values in the index
NUM_ROWS: The number of rows in the index

The USER_IND_COLUMNS view contains the following columns:
INDEX_NAME: The name of the index
TABLE_NAME: The name of the table the index is associated with
COLUMN_NAME: The name of the column in the index
COLUMN_POSITION: The position of the column in the index
COLUMN_LENGTH: The length of the column

The USER_PART_INDEXES also contains information about the specific partitions used in partitioned indexes.

ALL_INDEXTYPES

ALL_INDEXTYPES displays information about the indextypes accessible to the current user.

Related Views

  1. DBA_INDEXTYPES displays information about all indextypes in the database.
  2. USER_INDEXTYPES displays information about the indextypes owned by the current user.
ALL_INDEXTYPES

The next lesson shows how to drop an index.