Databases Reference
In-Depth Information
Data Dictionary Index Information
As you've learned, data dictionary views can provide you with information
about all database objects. The two key data dictionary views relating to indexes
that every DBA should know about are DBA_INDEXES and DBA_ IND_COLUMNS,
which contain the names of the indexes and the names of the indexed columns,
respectively.
DBA_INDEXES
To find out the owners, tablespace names, and index type for all indexes on the
EMPLOYEES table, Janice constructs a query against the DBA_INDEXES data dic-
tionary view, as follows:
select owner, index_name, index_type, tablespace_name from
dba_indexes where table_name = 'EMPLOYEES';
OWNER INDEX_NAME INDEX_TYPE TABLESPACE_NAME
------- -------------------- ------------- ---------------
HR EMP_EMAIL_UK NORMAL EXAMPLE
HR EMP_EMP_ID_PK NORMAL/REV EXAMPLE
HR EMP_DEPARTMENT_IX NORMAL EXAMPLE
HR EMP_JOB_IX NORMAL EXAMPLE
HR EMP_MANAGER_IX NORMAL EXAMPLE
HR UK1_EMPLOYEES NORMAL EXAMPLE
HR BM_EMPLOYEES_GENDER BITMAP EXAMPLE
HR EMP_NAME_IX NORMAL EXAMPLE
8 rows selected.
All of the indexes on the EMPLOYEES table are normal b-tree indexes, except
that the primary key index EMP_EMP_ID_PK is a reverse key b-tree index and the
new BM_EMPLOYEES_GENDER index is a bitmap index.
DBA_IND_COLUMNS
To further drill down into the details of the indexes on the EMPLOYEES table,
Janice queries the DBA_IND_COLUMNS table to find out which columns are in the
EMP_NAME_IX index:
select index_name, table_name,
column_name, column_position from
dba_ind_columns where index_name = 'EMP_NAME_IX';
