HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Rebuild unusable indexes and enable key constraints
ORACLE STUFFS
04-Aug-2008
Simple useful script to rebuild unusable indexes (both global and local) in defined scope.

Declare 
  p_owner_mask Varchar2(30 char):='^'||USER||'$'; --regular expression owner mask  
  p_table_mask Varchar2(30 char):='.';            --regular expression table mask  
Begin 
  for r1 in (   
    Select 'Alter index '||IX.OWNER||'.'||IX.INDEX_NAME||' rebuild '||regexp_replace(PIX.PARTITION_NAME,'(^.+$)','partition \1')||regexp_replace(SIX.SUBPARTITION_NAME,'(^.+$)','subpartition \1') as STMT 
    from ALL_INDEXES IX 
    left join ALL_IND_PARTITIONS PIX on PIX.INDEX_OWNER=IX.OWNER and PIX.INDEX_NAME=IX.INDEX_NAME and IX.PARTITIONED='YES' and PIX.STATUS='UNUSABLE' 
    left join ALL_IND_SUBPARTITIONS SIX on SIX.INDEX_OWNER=IX.OWNER and SIX.INDEX_NAME=IX.INDEX_NAME and IX.PARTITIONED='YES' and SIX.STATUS='UNUSABLE' 
    where regexp_like(IX.OWNER,p_owner_mask) 
      and regexp_like(IX.TABLE_NAME,p_table_mask) 
      and (IX.STATUS='UNUSABLE' or (IX.PARTITIONED='YES' and (PIX.INDEX_NAME is not null or SIX.INDEX_NAME is not null))) 
  )loop 
     execute immediate r1.STMT; 
  end loop; 
end;     
/

[Download]

Latest Oracle version can use superset indexes both UNIQUE and NOT UNIQUE as a technical mean to enforce constraint.
When your infrastructure counts on unique indexes under primary and unique key constraints, you can create scripts that way. The problem starts in bulk processing when you have to disable constraints during loads and after enabling them they take other indexes than you expected.
First idea is "why we need an superset index for unique one." In large databases you can use so called "lookup indexes" designed for FAST FULL SCAN INDEX operations to avoid full scanning of wide dimensional tables in bulk processing etc.
So, if you want to enforce standard while disabling and enabling constraint, use something like this:

Declare  
    p_owner_mask Varchar2(30 char):='^'||USER||'$'; --regular expression owner mask   
    p_table_mask Varchar2(30 char):='.';            --regular expression table mask   
    --
    v_stmt Varchar2(4000);
    v_cols Varchar2(4000); 
    v_loc  Varchar2(20); 
BEGIN
 for r1 in (
   select OWNER,CONSTRAINT_NAME,TABLE_NAME 
   from ALL_CONSTRAINTS 
   where regexp_like(OWNER,p_owner_mask) 
     and regexp_like(TABLE_NAME,p_table_mask) 
     and CONSTRAINT_TYPE in ('P','U') 
     and STATUS='DISABLED'
 )loop
     Select listagg(COLUMN_NAME,',')within group(order by POSITION) into v_cols from ALL_CONS_COLUMNS where OWNER=r1.OWNER and CONSTRAINT_NAME=r1.CONSTRAINT_NAME and TABLE_NAME=r1.TABLE_NAME;
     --
     Select Case when T.PARTITIONED='YES' and count(C.COLUMN_NAME)=0 then ' local' end into v_loc
     from ALL_TABLES T
     left join ALL_PART_KEY_COLUMNS C on C.OWNER=T.OWNER and C.NAME=T.TABLE_NAME and not regexp_like(C.COLUMN_NAME,'^(('||replace(v_cols,',',')|(')||'))$')
     where T.PARTITIONED='YES'
     group by T.PARTITIONED;
     -- 
     v_stmt := 'Alter table '||r1.OWNER||'.'||r1.TABLE_NAME||' enable constraint '||r1.CONSTRAINT_NAME||' using index(create unique index '||r1.OWNER||'.'||r1.CONSTRAINT_NAME||' on '||r1.OWNER||'.'||r1.TABLE_NAME||'('||v_cols||')'||v_loc||')';
     execute immediate(v_stmt);
 end loop;
END;
/

[Download]
Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky