(5.6)
select t1.table_name,t1.indkey,group_concat(index_name order by index_name) index_names, count(*) kensu
from (
select table_name,index_name,group_concat(column_name order by seq_in_index) indkey
from information_schema.statistics
where table_schema='test'
group by table_name,index_name
) t1
group by t1.table_name,t1.indkey
having count(*) > 1
order by t1.table_name,t1.indkey
;
(8.0.18)
select * from sys.schema_redundant_indexes;
(12cR2)
重複インデックス作成時は下記エラーとなる
ORA-01408: 列リストはすでに索引付けされています
(13)
select indrelid::regclass,indkey,array_agg(indexrelid::regclass order by indexrelid::regclass ) index_names,count(*) kensu
from pg_index
group by indrelid,indkey
having count(*) > 1
order by indrelid,indkey
;
(2017)
select t1.table_name,t1.indkey
,string_agg(t1.index_name ,',') within group (order by t1.index_name) index_names,count(*) kensu
from (
select object_name(ic.object_id) table_name,i.name index_name,string_agg(ic.column_id ,',') within group (order by ic.key_ordinal) indkey
from sys.index_columns ic
inner join sys.objects o
on ic.object_id = o.object_id
and o.type = 'u'
inner join sys.indexes i
on ic.object_id = i.object_id
and ic.index_id = i.index_id
group by object_name(ic.object_id),i.name
) t1
group by t1.table_name,t1.indkey
having count(*) > 1
order by t1.table_name,t1.indkey
;
