Maintaining indexes 1

[commatom]

I need to write a script to help maintain indexes while doing our nightly ETL process. The following is what I came up with. I thought I would post it here for others to use and to be critiqued by some of my more experienced colleagues.

A couple things to know.

1) I create a table called maint_indexes which has a table_name, index_name, and rebuild_sql columns.

2) The procedure is executed by passing 2 parameters. The first is the table_name and the second is the action to be taken. You can do 'DROP' which drops the indexes. 'REBUILD_INDEXES' does just that and TRUNCATE was added because I have some database linked tables that I truncate from time to time.

CREATE OR REPLACE PROCEDURE DW.OBJ_MAINT  (
  p_table_name         IN  varchar2,
  p_action             IN  varchar2)
AS
--=====================================================
-- Control variables used in most programs
--=====================================================
v_sql       varchar2(4000);
v_counter   number;
v_col    varchar2(100);
v_state1 varchar2(255);
v_state2 varchar2(255);
     
v_ind_name varchar2(100);

--=====================================================
-- Cursor for Dropping indexes
--=====================================================
CURSOR c_drop IS
SELECT 'drop index '||owner||'.'||index_name v_statement 
  FROM all_indexes
 WHERE table_name = p_table_name
 and index_name not in (select constraint_name from all_constraints where constraint_type = 'P');
 
 
  CURSOR c_rebuild_script IS
     select i.table_name, i.index_name, 'create'||decode(UNIQUENESS, 'UNIQUE', ' UNIQUE', NULL)||' index '||owner||'.'||i.index_name||' on '||
 i.table_name s1, 
  decode(column_position,1,'(',',')||column_name||
 DECODE(lead(i.index_name) over (partition by i.index_name order by column_position), NULL, ')', NULL ) c1
  ,' pctfree  '||pct_free||' initrans '||ini_trans||
 '   maxtrans '||max_trans||'   storage (initial '||initial_extent||'  minextents  '||min_extents||'  maxextents  '||max_extents||') tablespace '||tablespace_name S2
     from all_indexes i, all_ind_columns c
     where i.table_name = p_table_name
     and i.index_name = c.index_name
     and i.index_name NOT in (select constraint_name from all_constraints where constraint_type = 'P')
     ORDER BY i.INDEX_NAME, c.COLUMN_POSITION;


--=====================================================
-- Control for creating indexes
--===================================================== 
CURSOR c_create IS
SELECT DISTINCT rebuild_sql FROM dw.maint_indexes 
WHERE table_name = p_table_name;

BEGIN
--=====================================================
-- DROP INDEXES
--===================================================== 
IF p_action = 'DROP'
THEN
--=====================================================
-- REBUILD CREATE INDEX SCRIPTS AND STORE THEM IN 
-- MAINT_INDEXES TABLE
--===================================================== 

FOR ind IN c_rebuild_script LOOP
 IF v_ind_name = ind.index_name
  
  THEN 
    v_col := v_col||ind.c1;
          
  ELSE
    IF v_ind_name IS NULL
      THEN
         v_col := ind.c1;
            ELSE     
            INSERT INTO dw.maint_indexes VALUES (ind.table_name, v_ind_name, v_state1||v_col||v_state2);
            v_col := ind.c1;
    END IF;
   END IF;
    v_state1 := ind.s1;
    v_state2 := ind.s2;
    v_ind_name := ind.index_name;
  END LOOP;

  INSERT INTO dw.maint_indexes VALUES (p_table_name, v_ind_name, v_state1||v_col||v_state2);
  COMMIT;


FOR rec IN c_drop loop

v_sql := rec.v_statement;

execute immediate (v_sql); 

END LOOP;
COMMIT;
END IF;

--=====================================================
-- CREATE INDEXES
--===================================================== 

IF p_action = 'REBUILD_INDEXES'
THEN
DELETE dw.maint_indexes where index_name in (select index_name from all_indexes)
or index_name is null;
commit;

FOR rec IN c_create loop

v_sql := rec.rebuild_sql;
execute immediate (v_sql); 
END LOOP;
DELETE dw.maint_indexes where table_name = p_table_name;
COMMIT; 
END IF;


IF p_action = 'TRUNCATE'
THEN
v_sql := 'TRUNCATE TABLE '||p_table_name;
execute immediate (v_sql);
END IF;
END;

 

[Dagon]

a) There isn't any real need to format the SQL statement in the select. In PL/SQL, you can just select the columns you need and then build the statement using a variable. This gives more flexibility in that the same data set can be used for many different statements. People only generally use the technique of selecting the SQL in SQL*Plus scripts where it has to get spooled to a file.

b) You are using all_* tables but not handling the possibility of the same table/index in different schemas properly. Your second SQL doesn't join on the schema name.

c) You might need to handle partitioned indexes as well.

 

[commatom]

Dagon-

a) True, i "borrowed" the sql from a script that spooled to a file

b) Good point, while in most cases it would be ok, I could see getting into trouble if there were a test index out there.

c) didnt think about that. I am working on a new data warehouse and will need to partition tables. I think I would probably be doing something different for partitioned tables though.