How to see the unused indexes for a query

General
Enthusiast

How to see the unused indexes for a query

Hi,

Can anybody tell me how to see the unused indexes after running the query.

3 REPLIES
Teradata Employee

Re: How to see the unused indexes for a query

You can see which indexes were used by looking at the EXPLAIN.
To see when an index was last used, you can run the following query. It is not speific to a query.

SELECT DatabaseName
, TableName
, IndexName
, ColumnName
, IndexType,
CASE IndexType
WHEN 'P' THEN 'Nonpartitioned Primary'
WHEN 'S' THEN 'Secondary'
WHEN 'K' THEN 'Primary Key'
WHEN 'U' THEN 'Unique Constraint'
WHEN 'Q' THEN 'Partitioned Primary'
WHEN 'V' THEN 'Value Ordered Secondary'
WHEN 'J' THEN 'Join Index'
WHEN 'N' THEN 'Hash Index'
WHEN 'O' THEN 'Value Ordered (All) covering secondary'
WHEN 'H' THEN 'Hash Ordered (All) covering secondary'
WHEN 'I' THEN 'Ordering column of composite secondary'
WHEN 'M' THEN 'Multi-Column statistics'
WHEN 'D' THEN 'Derived column partition statistics'
ELSE IndexType
END (TITLE 'Type')
,UniqueFlag AS "Unique"
,IndexNumber
,ColumnPosition
,AccessCount
,LastAccessTimeStamp AS "Last Access"
,CreatorName
,CreateTimeStamp
,LastAlterName
,LastAlterTimeStamp
FROM DBC.IndicesV
WHERE TRIM(ReplaceWithYourDataBaseName) LIKE 'PD'
AND IndexType NOT IN ('1','2','I','M','D')
ORDER BY DatabaseName, TableName, IndexNumber, ColumnPosition;
Enthusiast

Re: How to see the unused indexes for a query

Can you please tell us know if you have any queries or approach which we can use them to find out unused SI index from Teradata?

Enthusiast

Re: How to see the unused indexes for a query

Mahesh,

You need to flip your perspective and look at the indexes that are used, either in the explain plan or by having your DBA turn on object use tracking.  Allison's query gives you the index types to check.  If you compare a list of indexes vs. the object use tracking, you can find the unused indexes.

Cheers