All Forums General
harshita45 11 posts Joined 08/12
18 Aug 2012
How to see the unused indexes for a query

Hi,

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

Alison 2 posts Joined 04/09
30 Aug 2012

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;

Alison

MaheshJessy 26 posts Joined 12/10
19 Jan 2016

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?

VandeBergB 182 posts Joined 09/06
20 Jan 2016

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

Some drink from the fountain of knowledge, others just gargle.

You must sign in to leave a comment.