1. Basic index information:
sql
SELECT index_name, table_name, uniqueness, status, visibility
FROM all_indexes
WHERE index_name = '****'
AND owner = '********';
2. Index column details:
sql
SELECT index_name, column_name, column_position, descend
FROM all_ind_columns
WHERE index_name = '*******'
AND index_owner = '******'
ORDER BY column_position;
3. Index usage statistics:
sql
SELECT i.index_name, i.table_name,
s.leaf_blocks, s.distinct_keys, s.clustering_factor,
s.last_analyzed, s.num_rows
FROM all_indexes i
JOIN all_ind_statistics s ON i.index_name = s.index_name AND i.owner = s.owner
WHERE i.index_name = '*********'
AND i.owner = '*******';
4. Check if the index is actually being used:
sql
SELECT o.owner, o.object_name, o.object_type,
o.last_ddl_time, s.executions, s.last_active_time
FROM all_objects o
LEFT JOIN v$segment_statistics s ON o.object_name = s.object_name AND o.owner = s.owner
WHERE o.object_name = '*********'
AND o.owner = '********'
AND s.statistic_name = 'logical reads';
5. To see index monitoring (if enabled):
sql
SELECT * FROM v$object_usage
WHERE index_name = '*********'
AND owner = '***********';
