Comment trouver des indices dupliqués

Запрос ниже показывает индексы с одинаковыми полями, а также индексы с одинаковыми начальными подмножествами полей. Просмотрите результат этого запроса, чтобы выявить дубликаты - т.е. индексы с одинаковыми полями и одинаковым направлением.

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
;