Oracle - How to get information about indexes, etc.

Question

How can I list index columns that are easily defined in Oracle. and How to use these indices in a select statement (something like Select x, y, z from an index with index (x) ..)

Answer 1

How can I list index columns that are easily 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 to use these indexes in a select statement

You do not have to do anything. If the index speeds up the query, Oracle will use it automatically.

Answer 2

If you want to force the use of a specific index, you would add a tooltip to your query:

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

This will force the use of the indx_name index.

Answer 3

 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; 

Answer 4

This query also shows a 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 5

As the name HorseWithNoName, you can use all_ind_columns.

In any case, I would recommend using an Oracle SQL developer: it is a free tool. You can get all the index information in a graphical interface.

You can increase a specific index with

 SELECT * FROM all_ind_columns WHERE table_name = 'TABLE_NAME' and index_name = 'INDEX_NAME' order by Column_position 

I do not agree with "You do not need to do anything." Too often, I see queries written as:

 WHERE Trim(LastName) ='SMITH' WHERE LastName like '%SMITH%' WHERE trunc(CreationDate) = date'2016-09-23' 

Even if LastName and CreationDate indexes are indexed, Oracle will NOT be able to use them.

Write

 WHERE LastName like 'SMITH%' WHERE CreationDate between date'2016-09-23' and date'2016-09-24' 

Answer 6

It was useful for me to show all indexes, replace with your data:

 SELECT * FROM all_ind_columns WHERE TABLE_NAME IN (SELECT DISTINCT TABLE_NAME from all_indexes where owner = '<INDEX_OWNER>') order by TABLE_NAME;