How To Check Index Created On Table In Oracle

How to Check Indexes on a Table

Lets create a table named Dummy and insert few rows in it.

CREATE TABLE Dummy (     ID int,     LastName varchar(255),     FirstName varchar(255),     City varchar(255) );  insert into Dummy values (1, 'l_1', 'f_1','c_1'); insert into Dummy values (2, 'l_2', 'f_2','c_2'); insert into Dummy values (3, 'l_3', 'f_3','c_3'); insert into Dummy values (4, 'l_4', 'f_4','c_4'); insert into Dummy values (5, 'l_5', 'f_5','c_5'); insert into Dummy values (6, 'l_6', 'f_6','c_6');

Select * from Dummy

ID	LastName	FirstName	City
1	l_1	f_1	c_1
2	l_2	f_2	c_2
3	l_3	f_3	c_3
4	l_4	f_4	c_4
5	l_5	f_5	c_5
6	l_6	f_6	c_6

We can see below 4 different queries to know about indexes on a table. Lets run all 4 below queries before creating an index on our table 'Dummy'.

1)
First query to know the indexes on a particular table

select * from sys.indexes where object_id in (select object_id                      from sys.objects                      where name = 'Dummy')

Result:

2)
Second query to know indexes on a table

EXEC sys.sp_helpindex @objname = N'Dummy'

Result:
The object 'Dummy' does not have any indexes, or you do not have permissions.

3)
Third query to know all the tables without an index.

SELECT name FROM sys.tables  WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0

Result:

Name
Dummy

4)
Fourth query to get indexes for User Created Tables.

SELECT             so.name AS TableName             , si.name AS IndexName             , si.type_desc AS IndexType FROM             sys.indexes si             JOIN sys.objects so ON si.[object_id] = so.[object_id] WHERE             so.type = 'U'    --Only get indexes for User Created Tables             AND si.name IS NOT NULL             AND so.name = 'Dummy' ORDER BY             so.name, si.type

Result:

TableName	IndexName	IndexType
-	-	-

Now lets create an Index on 'LastName' column of table 'Dummy':

CREATE INDEX idx_lastname ON Dummy (LastName);

Lets run all the 4 queries again after creating an index on a table.

1)
First query to know the indexes on a particular table

select * from sys.indexes where object_id in (select object_id                      from sys.objects                      where name = 'Dummy')

Result:

2)
Second query to know indexes on a table

EXEC sys.sp_helpindex @objname = N'Dummy'

Result:

index_name	index_description	index_keys
idx_lastname	nonclustered located on PRIMARY	LastName

3)
Third query to know all the tables without an index.

SELECT Name FROM sys.tables  WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0

Result:

Name
-

4)
Fourth query to get indexes for User Created Tables.

SELECT             so.name AS TableName             , si.name AS IndexName             , si.type_desc AS IndexType FROM             sys.indexes si             JOIN sys.objects so ON si.[object_id] = so.[object_id] WHERE             so.type = 'U'    --Only get indexes for User Created Tables             AND si.name IS NOT NULL             AND so.name = 'Dummy' ORDER BY             so.name, si.type

Result:

TableName	IndexName	IndexType
Dummy	idx_lastname	NONCLUSTERED

Also See:
Return indexes for a particular table
Query to check index on a table