Hoe dubbele indices te vinden

De onderstaande query toont indexen met dezelfde velden, en ook indexen met dezelfde startende subsets van velden. Bekijk het resultaat van deze query om duplicaten te identificeren - d.w.z. indexen met dezelfde velden en dezelfde richting.

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
;