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

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

Answers


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

Need Your Help

Unterminated string literal in escaped html within JavaScript string

javascript string escaping string-literals double-quotes

I'm seeing an issue with some javascript string literals, when encoding this value:

Invalidate without OnPaint

c# winforms animation graphics invalidate

I'm trying to make an animation in Win Forms, and I have tried a different approach than calling Invalidate() in the timer_elapsed handler and then doing things in the OnPaintHandler.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.