How do you detect if there is an index for a specific column on a table in Oracle?

Question

A table exists that someone else loaded. I need to query against the table, but the lack of indexes makes the query plan abysmal. What I would like to do is detect if there is an index for a particular column, so that I can created it if it does not exist, and not create it if it is already there.

Thanks.

Evil

Answer 1

Become familiar with querying the SYS schema:

Select * from sys.all_ind_columns where table_name=:TabName and table_owner=:TabOwner;

Answer 2

You can query DBA_/ALL_/USER_IND_COLUMNS, i.e.

SQL> SELECT index_name
  2    FROM dba_ind_columns
  3   WHERE table_owner = 'SCOTT'
  4     AND table_name  = 'EMP'
  5     AND column_name = 'EMPNO';

INDEX_NAME
------------------------------
PK_EMP

Of course, you may want to expand the query a bit. This will pick up any index that the EMPNO column appears in. You may want to limit yourself to indexes where the column is the leading column of the index (COLUMN_POSITION = 1). Or you may want to limit yourself to indexes just on that particular column (so that there is no column in COLUMN_POSITION 2), i.e.

SQL> ed
Wrote file afiedt.buf

  1  SELECT index_name
  2    FROM dba_ind_columns a
  3   WHERE table_owner = 'SCOTT'
  4     AND table_name  = 'EMP'
  5     AND column_name = 'EMPNO'
  6     AND column_position = 1
  7     AND NOT EXISTS( SELECT 1
  8                       FROM dba_ind_columns b
  9                      WHERE a.index_owner = b.index_owner
 10                        AND a.index_name  = b.index_name
 11*                       AND b.column_position = 2)
SQL> /

INDEX_NAME
------------------------------
PK_EMP