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='SYS'
and
c.table_name='T';
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.