How to view index history usage with one click in the database

This article is about how to check the historical usage of the index with one click in the database. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Suppose such a scenario, there is a table built a lot of indexes, how can we observe the index history to use records to see if we can delete some redundant indexes? Here are two practical scripts:

View current index usage

SELECT p.object_name, p.operation, p.options, COUNT (1) FROM v$sql_plan p, v$sql 's WHERE p.object_owner 'SYS' AND p.OBJECT_NAME in (select index_name from dba_indexes where table_name =' Seven SHIPTINE') AND p.sql_id = s.sql_id GROUP BY p.object_name, p.operation, p.options ORDER BY 1, 2, 3

View historical index usage

SELECT p.object_name, p.operation, p.options, COUNT (1) FROM dba_hist_sql_plan p, dba_hist_sqlstat 's WHERE p.object_owner 'SYS' AND p.object_name in (select index_name from dba_indexes where table_name =' Seven SHIPTINE') AND p.sql_id = s.sql_id GROUP BY p.object_name, p.operation, p.options ORDER BY 1, 2, 3

Thank you for reading! This is the end of the article on "how to check the history of the index in the database with one click". I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it out for more people to see!