Got struck with one of the query and need to explore indexes for a resolution.
It is not unusual to wonder what indexes a specific table might have, and what columns in the table are assigned to those indexes. The following query will provide this information to you. It draws on the information in the dba_ind_columns data dictionary view:SQL> column table_owner format a15 SQL> column table_name format a20 SQL> column index_name format a20 SQL> column column_name format a20 SQL> Select owner, table_name, index_name, column_name 2 FROM dba_ind_columns 3 Order by owner, table_name, column_position 4 Where owner=’SCOTT’ 5 AND table_name=’EMP’; You will get the output like this !
TABLE_OWNER TABLE_NAME INDEX_NAME COLUMN_NAME ————— ——————– ——————– ———- SCOTT EMP PK_EMP EMPNO
In this example we find that the EMP table in the SCOTT schema has one index called PK_EMP. This index is built on a single column, EMPNO.