Data Dictionary Index Views (Information)

Lesson 8	Data dictionary views about indexes
Objective	View 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

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

ALL_INDEXTYPES

The next lesson shows how to drop an index.