CODEWITHSUNDEEP
plsqloracle10g
TonyP
TonyP

Reputation: 5873

Oracle - How to obtain information on index fields etc

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)...

Upvotes: 19

Views: 39433

Answers (7)

Vadim Zverev
Vadim Zverev

Reputation: 508

Script which displays table, index and comma separated list of index columns

set pagesize 50000
set linesize 32000
col table_name format a30
col index_name format a30
col primary_key_name format a30
col uniqueness format a10
col columns format a200

select ui.table_name,
       ui.index_name,
       uc.constraint_name as primary_key_name,
       ui.uniqueness,
       listagg(aic.column_name, ', ') within group (order by aic.column_position) as columns
from user_indexes ui
inner join all_ind_columns aic on aic.index_name = ui.index_name
left outer join user_constraints uc on uc.index_name = ui.index_name and uc.constraint_type = 'P'
group by ui.table_name, ui.index_name, ui.uniqueness, uc.constraint_name
order by table_name;

TABLE_NAME                     INDEX_NAME                     PRIMARY_KEY_NAME               UNIQUENESS COLUMNS                                 
------------------------------ ------------------------------ ------------------------------ ---------- ----------------------------------------
ACTIVEMQ_ACKS                  ACTIVEMQ_ACKS_PK               ACTIVEMQ_ACKS_PK               UNIQUE     CONTAINER, CLIENT_ID, SUB_NAME, PRIORITY
ACTIVEMQ_ACKS                  ACTIVEMQ_ACKS_XIDX                                            NONUNIQUE  XID                                     
ACTIVEMQ_CONNECTION            ACTIVEMQ_CONNECTION_NAME_UK                                   UNIQUE     NAME                                    
ACTIVEMQ_CONNECTION            ACTIVEMQ_CONNECTION_PK         ACTIVEMQ_CONNECTION_PK         UNIQUE     ID                                      
ACTIVEMQ_MSGS                  ACTIVEMQ_MSGS_CIDX                                            NONUNIQUE  CONTAINER                               
ACTIVEMQ_MSGS                  ACTIVEMQ_MSGS_EIDX                                            NONUNIQUE  EXPIRATION                              
ACTIVEMQ_MSGS                  ACTIVEMQ_MSGS_MIDX                                            NONUNIQUE  MSGID_PROD, MSGID_SEQ                   
ACTIVEMQ_MSGS                  ACTIVEMQ_MSGS_PIDX                                            NONUNIQUE  PRIORITY                                
ACTIVEMQ_MSGS                  ACTIVEMQ_MSGS_PK               ACTIVEMQ_MSGS_PK               UNIQUE     ID                                      
ACTIVEMQ_MSGS                  ACTIVEMQ_MSGS_XIDX                                            NONUNIQUE  XID                                     
ACT_EVT_LOG                    SYS_C00444651                  SYS_C00444651                  UNIQUE     LOG_NR_                                 
ACT_GE_BYTEARRAY               ACT_IDX_BYTEAR_DEPL                                           NONUNIQUE  DEPLOYMENT_ID_                          
ACT_GE_BYTEARRAY               SYS_C00444634                  SYS_C00444634                  UNIQUE     ID_                                     
ACT_GE_PROPERTY                SYS_C00444632                  SYS_C00444632                  UNIQUE     NAME_                                   
ACT_HI_ACTINST                 ACT_IDX_HI_ACT_INST_END                                       NONUNIQUE  END_TIME_                               
ACT_HI_ACTINST                 ACT_IDX_HI_ACT_INST_EXEC                                      NONUNIQUE  EXECUTION_ID_, ACT_ID_                  
ACT_HI_ACTINST                 ACT_IDX_HI_ACT_INST_PROCINST                                  NONUNIQUE  PROC_INST_ID_, ACT_ID_

Upvotes: 0

Christian
Christian

Reputation: 1

this was helpful for me to show all indexes, replace with your details:

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

Upvotes: 0

Olivier Citeau
Olivier Citeau

Reputation: 169

As a HorseWithNoName, wrote, you can use all_ind_columns.

Anyway, I would recommend to use Oracle SQL developer : it is a free tool. You can get all index information's in a GUI.

You can zoom on a particular index whith :

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

I do disagree with "You don't have to do anything.." Too often, i see requests written as :

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

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

Do write 

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

Upvotes: 1

Michel de Ruiter
Michel de Ruiter

Reputation: 7954

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

Upvotes: 12

Tijolo Smith
Tijolo Smith

Reputation: 11

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;

Upvotes: 1

Michael Ballent
Michael Ballent

Reputation: 1088

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.

Upvotes: 1

user330315
user330315

Reputation:

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.

Upvotes: 26

Related Questions