TABLES WITH QUESTIONABLE INDEX(ES)

 

Tabs with Questionable Inds

TABLES WITH QUESTIONABLE INDEX(ES) NOTES:

Owner - Owner of the table

Table Name - Name of the table

Column - Name of the column in question

The above query shows all tables that have more than one index with the same leading column. These indexes can cause queries to use an inappropriate indexes; in other words, Oracle will use the index that was created most recently if two indexes are of equal ranking. This can cause different indexes to be used from one environment to the next (e.g., from DEV to TEST to PROD).

The information does not automatically indicate that an index is incorrect; however, you may need to justify the existence of each of the indexes above.

select  TABLE_OWNER,
 TABLE_NAME,
 COLUMN_NAME
from   dba_ind_columns 
where   COLUMN_POSITION=1
and   TABLE_OWNER not in ('SYS','SYSTEM')
group   by TABLE_OWNER, TABLE_NAME, COLUMN_NAME
having  count(*) > 1