Menu
I have some old tables with, what I think, are nearly worthless indexes. How can I easily be sure that are not ever being used before I drop them.
690
In Oracle SQL Developer, when you have SQL in the worksheet, there is a button "Explain Plan", you can also hit F10. After you execute Explain plan, it will show in the bottom view of SQL Developer. There is a column "OBJECT_NAME", it will tell you what index is being used.
Best Answer There is only the last_analyzed column. So, if you issued alter index x_index rebuild and that finished running, then it means that the index was rebuilt. For improving performance you may also call dbms_stats.
Duplicate indexes are those that exactly match the Key and Included columns. That's easy. Possible duplicate indexes are those that very closely match Key/Included columns.
Based on Joe's answer I came up with this:
SELECT row_number() over(order by user_seeks,user_lookups,user_scans), [Database] = d.name, [Schema]= s.name, [Table]= o.name, [Index]= x.name, [Scans] = user_scans, [Seeks] = user_seeks, [Lookups] = user_lookups, [Last Scan] = last_user_scan, [System Scans] = system_scans FROM sys.dm_db_index_usage_stats u INNER JOIN sys.sysdatabases d on u.database_id = d.dbid INNER JOIN sys.sysindexes x on u.object_id = x.id and u.index_id = x.indid INNER JOIN sys.objects o on u.object_id = o.object_id INNER JOIN sys.schemas s on s.schema_id = o.schema_id where x.name is not null order by 1 desc If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With