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

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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google