How to find duplicated indices

The query below shows indices with the same fields, and also indices with the same starting subsets of fields. Review this result of this query to identify duplicates - i.e., indices with the same fields and the same direction.

Code


with recursive r
as (
        select
            ri.rdb$relation_name tname
            ,ri.rdb$index_name iname
            ,coalesce(ri.rdb$unique_flag,0) is_uniq
            ,coalesce(ri.rdb$index_type,0) is_desc
            ,ri.rdb$segment_count seg_cnt
            ,rs.rdb$field_position fpos
            ,rs.rdb$field_name fname
            ,coalesce(rc.rdb$constraint_name,'') ct_name
            ,coalesce(rc.rdb$constraint_type, '') constraint_type
            ,',' || cast( trim(rs.rdb$field_name) as varchar(8190) ) as idx_key
        from rdb$indices ri
        left join rdb$index_segments rs on ri.rdb$index_name = rs.rdb$index_name
        left join rdb$relation_constraints rc on rs.rdb$index_name = rc.rdb$index_name
        where
        --ri.rdb$relation_name='TEST' and
        ri.rdb$index_inactive is distinct from 1
        and rs.rdb$field_position = 0

        union all

        select
            ri.rdb$relation_name tname
            ,ri.rdb$index_name iname
            ,r.is_uniq
            ,r.is_desc
            ,r.seg_cnt
            ,rs.rdb$field_position
            ,rs.rdb$field_name fname
            ,r.ct_name
            ,r.constraint_type
            ,r.idx_key || ',' || trim(rs.rdb$field_name )
        from rdb$indices ri
        join rdb$index_segments rs on ri.rdb$index_name = rs.rdb$index_name
        join r on ri.rdb$relation_name = r.tname
        and ri.rdb$index_name = r.iname
        and rs.rdb$field_position = r.fpos+1
)
--select * from r
,m as (
    select
        tname
        ,iname
        ,constraint_type
        ,is_uniq
        ,is_desc
        ,seg_cnt
        ,idx_key
    from r where fpos=seg_cnt-1
)
select
     a.tname table_name
    ,a.iname a_index_name
    ,a.is_uniq a_is_uniq
    ,iif(a.is_desc=1,'DESC','ASC') a_sort
    ,a.constraint_type a_constraint_type
    ,b.iname b_index_name
    ,b.is_uniq b_is_uniq
    ,iif(b.is_desc=1,'DESC','ASC') b_sort
    ,b.constraint_type b_constraint_type
    ,substring(a.idx_key from 2) a_index_key
    ,substring(b.idx_key from 2) b_index_key
from m a
join m b on
    a.tname = b.tname
    and a.is_desc = b.is_desc
    and a.seg_cnt >= b.seg_cnt
    and a.iname <> b.iname
    and a.idx_key || ',' starting with b.idx_key || ','
    and not( a.idx_key = b.idx_key
            and (
                a.constraint_type = '' and b.constraint_type in ('PRIMARY KEY', 'FOREIGN KEY', 'UNIQUE')
                or
                a.constraint_type = '' and  b.constraint_type = '' and a.iname > b.iname
            )

    )
order by table_name, a_index_name, b_index_name
;