how to find indexes on a table in oracle

It is very common that you want to Find indexes status and assigned columns for a oracle table
And also you sometime want to know the columns which are assigned to the indexes.
Following views would be helpful in these cases and to find indexes on a table in oracle

dba_ind_columns
all_ind_columns

How to check index on table in oracle

We can use below query on how to check index column on table in oracle and how to check index on table in oracle

set pagesize 50000 verify off echo off

col table_name head 'Table Name' format a20
col index_name head 'Index Name' format a25
col column_name head 'Column Name' format a30

break on table_name on index_name

select    table_name, index_name, column_name
from    all_ind_columns
where    table_name like upper('&Table_Name')
order by table_name, index_name, column_position
/

how to check index status in oracle

Normal Index

SELECT owner, index_name, tablespace_name,status
FROM dba_indexes;

how to check unusable indexes in oracle

SELECT owner, index_name, tablespace_name,status
FROM dba_indexes
WHERE status = 'UNUSABLE';

Index partitions:

SELECT index_owner, index_name, partition_name, tablespace_name,status
FROM dba_ind_PARTITIONS;

If you want to know unusable ind partitions, then we  can use below query

SELECT index_owner, index_name, partition_name, tablespace_name,status
FROM dba_ind_PARTITIONS
WHERE status = 'UNUSABLE';

Index sub partitions:

SELECT index_owner, index_name, partition_name, subpartition_name, tablespace_name,status
FROM dba_ind_SUBPARTITIONS;

If you want to know unusable ind sub partitions, then we  can use below query

SELECT index_owner, index_name, partition_name, subpartition_name, tablespace_name,status
FROM dba_ind_SUBPARTITIONS
WHERE status = 'UNUSABLE';

Query to check Index Associated with a Constraint

SELECT index_name, table_name, uniqueness
  FROM DBA_INDEXES WHERE table_name = '&1';

List all indexes in a schema

SELECT index_name, table_name, uniqueness
  FROM DBA_INDEXES WHERE owner = '&1';

Query to find the index statistics for the table

set linesize 200
set pages 250
set verify off
col blevel format 99
col table_name format a22 heading 'TABLE NAME'
col u format a1 heading 'U'
col index_name format a25 heading 'INDEX NAME'
col column_name format a23 heading 'COLUMN NAME'
col column_position format 99 heading 'SEQ'
col column_length format 9999 heading 'LEN'
col leaf_blocks format 999990 heading 'LEAF|BLOCKS'
col distinct_keys format 9999990 heading 'DISTINCT|KEYS'
col avg_leaf_blocks_per_key format 999990 heading 'LEAF|BLKS|/KEY'
col avg_data_blocks_per_key format 999990 heading 'DATA|BLKS|/KEY'
rem
break on table_name skip 1 on index_name on u
rem
select i.table_name,i.blevel, i.leaf_blocks, i.distinct_keys,i.avg_leaf_blocks_per_key, i.avg_data_blocks_per_key,
       decode( i.uniqueness, 'NONUNIQUE', null, 'UNIQUE', 'U', 'BITMAP', 'B', '?' ) u,
       i.index_name,i.last_analyzed, c.column_position, c.column_name, c.column_length
  from sys.dba_ind_columns c, sys.dba_indexes i
 where (i.table_owner,i.table_name) in ('&1','&2')
   and i.owner = c.index_owner
   and i.index_name = c.index_name
 order by i.table_owner, i.table_name, i.index_name, c.column_position
/

Query to find index column definition

ttitle 'Index Column Definitions'
rem
set linesize 100
set pages 250
set verify off
col table_name format a22 heading 'TABLE NAME'
col u format a1 heading 'U'
col index_name format a20 heading 'INDEX NAME'
col column_name format a25 heading 'COLUMN NAME'
col column_position format 99 heading 'SEQ'
col column_length format 999 heading 'LEN'
rem
break on table_name skip 1 on index_name on u
rem
select i.table_name,
       decode( i.uniqueness, 'NONUNIQUE', null, 'UNIQUE', 'U', 'BITMAP', 'B', '?' ) u,
       i.index_name, c.column_position, c.column_name, c.column_length
  from sys.dba_ind_columns c, sys.dba_indexes i
 where i.table_owner like upper('&owner')
   and i.table_name like upper('&table')
   and i.index_name like upper('&index')
   and c.column_name like upper('&column')
   and i.owner = c.index_owner
   and i.index_name = c.index_name
 order by i.table_owner, i.table_name, i.index_name, c.column_position
/

With 12c, Oracle has introduced the concept of Partial indexes in Partitioned table.The *_INDEXES view has been modified to include an INDEXING column, which indicates if the index is FULL or PARTIAL.

Check the indexing status of the index.

SELECT index_name,
indexing
FROM dba_indexes
ORDER BY 1;

INDEX_NAME    INDEXING
------------------------- -------
EXP_INDEX_N1 PARTIAL

This happened when we have indexing partial tag in the create index statement.
CREATE INDEX EXP_INDEX_NI ON tab_name(col1) Local INDEXING PARTIAL;

Related Articles

Virtual Index in Oracle

Oracle Index clustering factor

Oracle Partitioned Table

Oracle Partition Index

Types of indexes in oracle

I hope you like this compilation of oracle index related queries and it will help in finding answer to various queries like how to find index on a table, list all indexes in the schema, index status on a table and many more. Please do share with me what else i can add to this list to add more value to this post. Please do provide me the feedback also

Also Read
How to list all tables in Oracle
How to check table size in Oracle
How to manage indexes