UnUsed Secondary Indexes

Database
Teradata Employee

UnUsed Secondary Indexes

Hi All,

 

I need SQL to extract all tables in a database whose secondary indexes have not been accessed in the last one year. Can someone provide SQL to check the secondary indexes and their last accesstimestamp.

 

Thanks in advance

 

Regards

Nof

2 REPLIES 2
Teradata Employee

Re: UnUsed Secondary Indexes

You need to have object logging enabled.

 

Then, you can just check the dbqlobjtbl or pdcr equivalend for objecttype = idx

 

thanks

 

dave

Enthusiast

Re: UnUsed Secondary Indexes

If objectusage is enabled, you may try something like below,

 

select	a.databaseid,a.objectid,a.fieldid,a.useraccesscnt,b.indextype,a.lastaccesstimestamp
from	dbc.objectusage a join dbc.indexes b on a.databaseid=b.databaseid and a.objectid=b.tableid 
where	b.indextype='S' 
	and a.lastaccesstimestamp < date-360 
	and a.useraccesscnt < 3 ;

Map above query result with tvm and tvfields to determine the tablename and fieldname.

 

Best Regards,

Sandeep.

 

GANGA SANDEEP KUMAR