Cách tìm các chỉ mục trùng lặp

Truy vấn dưới đây hiển thị các chỉ mục có cùng trường, và cũng hiển thị các chỉ mục có cùng tập con trường bắt đầu. Hãy xem xét kết quả của truy vấn này để xác định các chỉ mục trùng lặp - tức là các chỉ mục có cùng trường và cùng hướng.

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
;