Oracle: SQL Query to Find out Index Name, Column Name for Table

advertisements

_____________________________________________________________________________________________________________________

It is common requirement for a developer or DBA to find out the index name, column name for the table. Sometimes the index would be composite index. So in that case we have to find out that also like how many columns associated with the index and their order. There are 2 data dictionary views for the same. DBA_INDEXES and DBA_IND_COLUMNS. DBA_INDEXES gives just index names associated with the table and DBA_IND_COLUMNS gives the associated columns and their order.

Query 1. Just to see the index names

column table_owner format a20

column table_name format a25

column index_name format a25

column column_name format a25

select table_owner,TABLE_NAME,index_name

from   dba_indexes

where  table_name='CUSTOMER_SITES';

TABLE_OWNER     TABLE_NAME           INDEX_NAME          

--------------- -------------------- --------------------

SCOTT           CUSTOMER_SITES       CUSTOMER_SITES_IDX1

SCOTT           CUSTOMER_SITES       CUSTOMER_SITES_IDX2

Query 2: To see the index name, column names and their positions

Select owner, table_name, index_name, column_name, COLUMN_POSITION

FROM   dba_ind_columns

Where  owner='SCOTT'

AND    table_name='CUSTOMER_SITES'

AND    INDEX_NAME='CUSTOMER_SITES_IDX1'

Order by owner, table_name, column_name;

TABLE_OWNER     TABLE_NAME           INDEX_NAME           COLUMN_NAME      COLUMN_POSITION

--------------- -------------------- -------------------- ---------------  ---------------

SCOTT           CUSTOMER_SITES       CUSTOMER_SITES_IDX1  CS_ID            1

SCOTT           CUSTOMER_SITES       CUSTOMER_SITES_IDX1  CS_LOC_ID         2

_____________________________________________________________________________________________________________________

Website Stats