(5.6)
select object_name,index_name
from performance_schema.table_io_waits_summary_by_index_usage
where object_schema = 'test'
and index_name is not null
and count_read = 0
order by object_name,index_name
;
(8.0.18)
select * from sys.schema_unused_indexes;
(12cR1)
alter index ind1 monitoring usage;
alter index ind2 monitoring usage;
select *
from dba_object_usage
where index_name like 'IND%';
alter index ind1 nomonitoring usage;
alter index ind2 nomonitoring usage;
※(12cR2)からの新機能「DBA_INDEX_USAGE」は動作確認不可
(9.4)
select relname,indexrelname
from pg_stat_user_indexes
where idx_scan = 0
order by relname,indexrelname
;
(2014)
select object_name(i.object_id),i.*
from sys.indexes i
inner join sys.objects o
on i.object_id = o.object_id
and o.type = 'u'
where not exists (
select 1 from sys.dm_db_index_usage_stats d
where d.database_id = db_id('test')
and d.object_id = i.object_id
and d.index_id = i.index_id
)
;
