PROVE IT !!If you know it then PROVE IT !! Skill Proficiency Test

How to generate the list of Unused Index in Teradata warehouse ?

Q 33) How to generate the list of Unused Index in Teradata warehouse ?

Answer >

SELECT –ACT.LOGDATE,ACT.USERNAME,
IND.DATABASENAME,IND.TABLENAME,IND.INDEXTYPE,IND.INDEXNAME,ACT.OBJECTCOLUMNNAME,COUNT(ACT.OBJECT_CNT) AS ACC_COU
FROM EDW1_UTIL_BASEVIEW.DBQLOBJTBLSUM_HST ACT
INNER JOIN DBC.INDICES IND ON ACT.OBJECTDATABASENAME=IND.DATABASENAME AND ACT.OBJECTTABLENAME=IND.TABLENAME AND ACT.OBJECTCOLUMNNAME=IND.COLUMNNAME
WHERE ACT.OBJECTTYPE IN (‘Idx’,’JIx’)
AND ACT.OBJECTDATABASENAME LIKE <***** List the databasename for which you want to find SET Tables ****>
AND ACT.OBJECTCOLUMNNAME IS NOT NULL
AND IND.INDEXTYPE NOT IN (‘P’,’Q’)
GROUP BY 1,2,3,4,5
HAVING ACC_COU <2

Add a Comment

Your email address will not be published. Required fields are marked *