SQL QUERY TO FIND COLUMNS WHICH ARE INDEXED FOR A TABLE IN ORACLE

Use below query to find the all columns in table which are indexed

select i.index_name,
i.table_name,
c.column_name
from dba_indexes i
full outer join dba_tab_columns c on i.table_name=c.table_name
where c.owner='TEST'
and
c.table_name='TABX';

Sample output of the query

kish@exdbx<>select i.index_name,i.table_name,c.column_name
from dba_indexes i
full outer join dba_tab_columns c on i.table_name=c.table_name
where c.owner='TEST'
and
c.table_name='TABX';  2    3    4    5    6

INDEX_NAME      TABLE_NAME      COLUMN_NAME
--------------- --------------- ---------------
IDX_ID          TABX            ORDER_COST
IDX_ID          TABX            NULL_COLUMN
IDX_ID          TABX            TOTAL_PROFIT
IDX_ID          TABX            TOTAL_COST
IDX_ID          TABX            TOTAL_REVENUE
IDX_ID          TABX            UNIT_COST
IDX_ID          TABX            UNIT_PRICE
IDX_ID          TABX            UNITS_SOLD
IDX_ID          TABX            SHIP_DATE
IDX_ID          TABX            ORDER_ID
IDX_ID          TABX            ORDER_DATE
IDX_ID          TABX            ORDER_PRIORITY
IDX_ID          TABX            SALES_CHANNEL
IDX_ID          TABX            ITEM_TYPE
IDX_ID          TABX            COUNTRY
IDX_TABX        TABX            ORDER_COST
IDX_TABX        TABX            NULL_COLUMN
IDX_TABX        TABX            TOTAL_PROFIT
IDX_TABX        TABX            TOTAL_COST
IDX_TABX        TABX            TOTAL_REVENUE
IDX_TABX        TABX            UNIT_COST
IDX_TABX        TABX            UNIT_PRICE
IDX_TABX        TABX            UNITS_SOLD
IDX_TABX        TABX            SHIP_DATE
IDX_TABX        TABX            ORDER_ID
IDX_TABX        TABX            ORDER_DATE
IDX_TABX        TABX            ORDER_PRIORITY
IDX_TABX        TABX            SALES_CHANNEL
IDX_TABX        TABX            ITEM_TYPE
IDX_TABX        TABX            COUNTRY

30 rows selected.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s