Oracle - How to obtain information on index fields etc

Question

How can I list indexes columns readily defined in Oracle?

And how do I use those indexes in a select statement? Something like:

SELECT x, y, z FROM a WITH INDEX(x)...

Answer 1

How can I list indexes columns readily defined in Oracle

SELECT *
FROM all_ind_columns
WHERE table_name = 'YOUR_TABLE'

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_1064.htm#i1577532

How do I use those indexes in a select statement

You don't have to do anything. If the index will speed up the query, Oracle will use it automatically.

Answer 2

This query also shows the column expression for function based indexes, if any:

SELECT
 i.table_owner,
 i.table_name,
 i.index_name,
 i.uniqueness,
 c.column_name,
 f.column_expression
FROM      all_indexes i
LEFT JOIN all_ind_columns c
 ON   i.index_name      = c.index_name
 AND  i.owner           = c.index_owner
LEFT JOIN all_ind_expressions f
 ON   c.index_owner     = f.index_owner
 AND  c.index_name      = f.index_name
 AND  c.table_owner     = f.table_owner
 AND  c.table_name      = f.table_name
 AND  c.column_position = f.column_position
WHERE i.table_owner LIKE UPPER('%someuserpattern%')
 AND  i.table_name  LIKE UPPER('%sometablepattern%')
ORDER BY i.table_owner, i.table_name, i.index_name, c.column_position

Answer 3

If you want to force the use of a particular index you would add a hint to your query:

select /*+ index(tble_name indx_name) */
col1, col2
from tble_name

That will force the use of the index called indx_name.

Answer 4

SELECT table_name AS TABELA,
  index_name      AS INDICE,
  column_position AS POSICAO,
  column_name     AS COLUNA
FROM dba_ind_columns
WHERE (TABLE_OWNER LIKE upper('%&proprietario.%'))
AND (table_name LIKE upper('%&tabela.%'))
ORDER BY TABELA,
  INDICE,
  POSICAO,
  COLUNA;