How to find the unused index in database

Query to find the unused  index in database

SELECT owner, table_name ,index_name,index_type ,LAST_ANALYZED FROM DBA_INDEXES WHERE index_name IN (SELECT index_name
FROM
(SELECT
owner,
index_name
FROM
DBA_INDEXES di
WHERE
di.index_type != 'LOB'
AND
owner   =&owner
MINUS
SELECT
index_owner owner,
index_name
FROM
DBA_CONSTRAINTS dc
WHERE
index_owner   =&owner
MINUS
SELECT
p.object_owner owner,
p.object_name index_name
FROM
DBA_HIST_SNAPSHOT sn,
DBA_HIST_SQL_PLAN p
WHERE
sn.snap_id BETWEEN &start_snap_id AND &stop_snap_id
AND
p.object_type = 'INDEX'
))
and owner =&owner
ORDER BY 1


Query to find the frequency  of  index usage

SELECT
p.object_name search_columns,
ROUND (COUNT(*)/15 ,2 ) COUNT
FROM
DBA_HIST_SNAPSHOT sn,
DBA_HIST_SQL_PLAN p,
DBA_HIST_SQLSTAT st
WHERE
st.sql_id = p.sql_id
AND
sn.snap_id = st.snap_id
AND
p.object_type = 'INDEX'
AND sn.snap_id BETWEEN &start_snap_id AND &stop_snap_id
AND p.object_owner =&owner
GROUP BY
p.object_name ORDER BY 2 DESC, 1