CODEWITHSUNDEEP
oracle-databaseindexingoracle11g
Santu.
Santu.

Reputation: 33

Overlapping of Index

As per my understanding Overlap of index is as follows:-

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

In the table structure above, the index idx1 is a superset (overlap) of the index idx2 and therefore redundant.So index1 is overlap of Index2. Droping of such overlap indexes can improve performance.

But How can we find all the overlap index in entire schema of oracle 11gR2? Can anybody please guide me?Thanks in advance.

Upvotes: 1

Views: 617

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

You may query user_ind_columns ordering and concatenating columns by column_position and then do a self-join to do a comparison.

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|| '%,';

Result

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.

Note: Oracle Database treats descending indexes as if they were function-based indexes and hence the column names can't be compared directly through this method.

Also note that this statement by you is disputable.

Droping of such overlap indexes (ie Index2) can improve performance.

Upvotes: 1

Related Questions