Selectivity and composite indexes

Badly ordered composite keys will prevent you from getting the best out of indexing. Columns with excellent selectivity at the beginning of the index considerably boost its performance. This script will help DBAs

1. get an idea about the selectivity of component columns of all existing indexes
2. make a decision about re-ordering the columns in the indexes based on selectivity
3. make a decision about changing the indexes from B-tree to bitmap
4. extract a script to drop existing composite indexes and to build them in proper order
5. make a decision about scrapping indexes with very low selectivity
6. make slight alterations in it to get complete selectivity information

set serveroutput on size 50000
set linesize 1000

declare
 sql_string varchar2(32767);

 cursor cIndex  is select  
 index_name,table_name,tablespace_name from user_indexes order by table_name   ;
 
 
 mTables  cIndex%ROWTYPE;
 
 TYPE tColArray IS TABLE OF VARCHAR2(2000)INDEX BY BINARY_INTEGER;
 TYPE tCountArray IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 

 mColArray tColArray ; 
 mColArray1 tColArray ;
 mCountArray tCountArray ; 
 mTotCount Number(10,3) := 0.0; 

 toBeSorted boolean :=false;

 PROCEDURE SORTTHEM
 is
 Tem Number;
 CTem varchar2(200); 

 BEGIN
 FOR i IN REVERSE   mColarray.FIRST..mColarray.LAST LOOP
   FOR j IN  mColarray.FIRST..i-1 LOOP

   IF  mCountArray(j) < mCountArray(j+1) THEN 
       Tem   :=  mCountArray(j+1);
       mCountArray(j+1)  := mCountArray(j);
       mCountArray(j)  := Tem;
           cTem  :=  mColArray(j+1);
       mColArray(j+1)  :=  mColArray(j);
       mColArray(j)  :=  cTem; 

   toBeSorted  :=true;  

   End If;
   END lOOP;
 END LOOP;   
 EXCEPTION
  WHEN OTHERS THEN
  toBeSorted  :=true;
  null;
 END;

begin
 FOR mIndex in cIndex Loop
 mColArray := mColArray1;
 sql_string := '';
 EXECUTE IMMEDIATE 'select count (*) from ' || mIndex.table_name INTO mTotCount;
 
 IF mTotCount = 0 then
  dbms_output.put_line('REM No Data in ' || mIndex.table_name || ' to check selectivity' );
 ELSE  
 
  sql_string := 'select column_name from user_ind_columns WHERE index_name = :1 order by column_position';
  
  EXECUTE IMMEDIATE sql_string  BULK COLLECT INTO mColArray USING mIndex.Index_name;
  
  FOR i IN  1..mColArray.count LOOP
   sql_string := 'select NVL(count ( DISTINCT '|| mColArray(i) ||' ),0)'|| ' from ' || mIndex.table_name ;
   EXECUTE IMMEDIATE sql_string INTO mCountArray(i);      
  END lOOP;



  SORTTHEM;
  
  IF not toBeSorted   THEN
   dbms_output.put_line(' REM Component columns of index ' ||
                          mIndex.Index_name ||
                        ' on table ' || mIndex.table_name || 
                        ' is already in proper order ! ' || CHR(10));
  ELSE    
 

   dbms_output.put_line(' REM Proper order of component columns of index ' ||
                          mIndex.Index_name || ' on table ' ||
                          mIndex.table_name || ' is : ');
   sql_string := 'CREATE INDEX ' || mIndex.index_name || ' ON ' || mIndex.table_name || '(' ;        
   
   FOR i IN  1..mColArray.count LOOP
    sql_string := sql_string ||  mColArray(i)  ;
   
    if  i= mColArray.LAST THEN
     sql_string :=sql_string || ')';
    ELSE
     sql_string :=sql_string || ',' ;
    End If;

   dbms_output.put_line(' REM Column  ' || ':'  || rpad(mColArray(i),35) ||
                        ' :  Percentage Selectivity  : '||
                        RPAD('*' ,(mCountArray(i)/mTotCount)*100,'*' ) ||
                        to_char((mCountArray(i)/mTotCount)*100,'999.000') || '%');
   END lOOP;             
  
   sql_string := sql_string || ' TABLESPACE ' || mIndex.tablespace_name || ' ; ';
   dbms_output.put_line(' DROP INDEX ' || mIndex.index_name || ' ; ' ); 

   dbms_output.put_line(sql_string || CHR(10));
  

  END IF; 

END IF;

toBeSorted  := false;
End Loop; 
End;
/

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free Oracle tips and scripts.
• Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our applications, SQL, database administration, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
• Best Web Links: Oracle tips, tutorials, and scripts from around the Web.