How to check index on the table in Oracle

• Commonly, you want to Find indexes status and assigned columns for an Oracle table
• And also you sometimes 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 : This is to used if login with user having DBA role
all_ind_columns : This is to used if login with user having normal role
user_ind_columns : This is to used if login with user having normal role

How to check the index on the table in Oracle

We can use the below query on how to check the index column on the table in Oracle and how to check the index on the table in Oracle. Here is the query on how to find indexes on a 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
/

or
if you are just interested in index name

SELECT index_name FROM all_indexes WHERE table_name = '&table_name';

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 index partitions, then we  can use the below query

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

Index subpartitions:

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 the below query

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

Query to check the 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;

How to check index size in Oracle

Size of INDEX

select segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from user_segments where segment_name='INDEX_NAME' group by segment_name;

OR
select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_name='INDEX_NAME' group by owner,segment_name;

 

List of Size of all INDEXES of a USER
select segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from user_segments where segment_type='INDEX' group by segment_name order by "SIZE in GB" desc;

OR

select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_type='INDEX' group by owner,segment_name order by "SIZE in GB" desc;

Sum of sizes of all indexes

select owner,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_type='INDEX' group by owner;

Related Articles
Virtual Index in Oracle: What is Virtual Index in Oracle? Uses, limitations, advantages and how to use it check explain plan in Oracle database, Hidden parameter _USE_NOSEGMENT_INDEXES
Oracle Index clustering factor: How the Oracle Index clustering factor is calculated and how it impacts the explain plan
Oracle Partitioned Table: What is an Oracle partitioned table?. How to move the existing object to the Partitioned object?, Partitioning for Performance – Partition Pruning
Oracle Partition Index: Understanding Oracle partition index, What is Global Non-partitioned Indexes? What are local prefixed indexes, non-prefixed local index
Types of indexes in Oracle: This page consists of Oracle indexes information, different types of indexes in Oracle with an example, How to create/drop/alter the index in Oracle

I hope you like this compilation of Oracle index-related queries and it will help in finding answers to various queries like how to find an 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: we can get the List All Tables in Oracle by either querying all_tables or user_tables or dba_tables. we can select the column and where clause as per the need
How to check table size in Oracle: Find out the Query to check table size in Oracle database, top ten big tables in particular schema or particular tablespace in oracle
How to manage indexes

Recommended Courses

Here is the nice Udemy Course for Oracle SQL
Oracle-Sql-Step-by-step : This course covers basic sql, joins, Creating Tables and modifying its structure, Create View, Union, Union -all and much other stuff. A great course and must-have course for SQL starter
The Complete Oracle SQL Certification Course : This is a good course for anybody who wants to be Job ready for SQL developer skills. A nice explained course
Oracle SQL Developer: Essentials, Tips and Tricks : Oracle Sql developer tool is being used by many developers. This course gives us tricks and lessons on how to effectively use it and become a productive sql developer
Oracle SQL Performance Tuning Masterclass 2020 : Performance tuning is one of the critical and most sought skills. This is a good course to learn about it and start doing sql performance tuning