时间戳

首页 > 解决方案 > 索引的重叠

oracle - 索引的重叠

问题描述

根据我的理解，索引的重叠如下：-

CREATE INDEX idx1 ON TabA ( Col1, Col2, Col3 );
CREATE INDEX idx2 ON TabA ( Col1, Col2 );
CREATE INDEX idx3 ON TabA ( Col1 DESC, Col2 DESC );

在上面的表结构中，索引 idx1 是索引 idx2 的超集（重叠），因此是冗余的。所以 index1 是 Index2 的重叠。删除此类重叠索引可以提高性能。

但是我们如何在 oracle 11gR2 的整个模式中找到所有重叠索引？有人可以指导我吗？在此先感谢。

标签： oracleindexingoracle11g

解决方案

您可以查询user_ind_columns排序和连接列column_position，然后进行自连接以进行比较。

WITH indx AS ( SELECT table_name,index_name,LISTAGG(column_name,',') WITHIN GROUP(
                    ORDER BY column_position
               ) AS cols
FROM user_ind_columns a
               GROUP BY table_name,index_name
)
SELECT a.table_name,a.index_name AS sup_index,b.index_name sub_indx,
a.cols as super_set,b.cols AS sub_set
FROM indx a
JOIN indx b ON a.table_name = b.table_name
               AND a.index_name != b.index_name 
               where ','|| a.cols||',' like ',%'|| b.cols|| '%,';

结果

TABLE_NAME    SUP_INDEX                   SUB_INDX            SUPER_SET                SUB_SET       
TABA           IDX1                       IDX2                 COL1,COL2,COL3            COL1,COL2      
JOB_HISTORY    JHIST_EMP_ID_ST_DATE_PK    JHIST_EMPLOYEE_IX    EMPLOYEE_ID,START_DATE    EMPLOYEE_ID    


2 rows selected.

注意：Oracle 数据库将降序索引视为基于函数的索引，因此无法通过此方法直接比较列名。

另请注意，您的这一说法是有争议的。

删除此类重叠索引（即 Index2）可以提高性能。

推荐阅读