How to find out which indexes have never been used in Oracle

Editor to share with you how to find out which indexes have never been used in Oracle, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's learn about it!

After the database has gone through N hands, the index is randomly built. The following SQL will find out which indexes have never been used within a certain time range. It is easy to test for yourself. Note that if you execute the generated SQL, it will disappear from the script.

SELECT X.OWNER, X.TABLE_NAME, X.INDEX_NAME, C.COLUMN_POSITION, C.COLUMN_NAME, S.BYTES / 1024 / 1024 M, Q' [select count (*), count (distinct]'| | C.COLUMN_NAME | | Q' [) from]'| | X.OWNER | |. | X.TABLE_NAME | | Q' [ ]'AS QUERY_Q FROM (SELECT A.OWNER, A.TABLE_NAME, A.INDEX_NAME FROM DBA_INDEXES A WHERE A.OWNER IN (SELECT USERNAME FROM DBA_USERS WHERE CREATED > DATE '2018-10-30') AND (A.OWNER A.INDEX_NAME) NOT IN (SELECT / * + unnest*/ B.OBJECT_OWNER, B.OBJECT_NAME FROM GV$SQL A GV$SQL_PLAN B WHERE A.SQL_ID = B.SQL_ID AND A.CHILD_NUMBER = B.CHILD_NUMBER AND B.OBJECT_OWNER IN (SELECT USERNAME FROM DBA_USERS WHERE CREATED > DATE '2018-10-30') AND B.OBJECT_TYPE LIKE'% INDEX%' AND B.TIMESTAMP > DATE '2018-10-10') AND A.TABLE_NAME NOT LIKE 'SYS%' AND A.UNIQUENESS' UNIQUE') X INNER JOIN DBA_IND_COLUMNS C ON C.INDEX_OWNER = X.OWNER AND C.INDEX_NAME = X.INDEX_NAME AND C.TABLE_NAME = X.TABLE_NAME INNER JOIN DBA_ SEGMENTS S ON S.SEGMENT_NAME = C.TABLE_NAME AND S.OWNER = C.INDEX_OWNER AND S.BYTES / 1024 / 1024 > 10 ORDER BY 1 2, 3, 4 The above is all the contents of the article "how to find out which indexes have never been used in Oracle". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!