Bulk data manipulation and index rebulding

Sometimes we do need to manipulate large amount of data in database. This manipulation might include INSERT, UPDATE or DELETE operations. We need to analyze tables and indexes after such operation with large data. From my experience, I have seen, up-to-date statistics plays major role in SQL performance.

For example, we might be interested to analyze the table from where I just deleted 500K records and rebuild all it's corresponding indexes for current statistics.

The following scripts takes TABLE NAME as input and generates the necessary scripts for the said purposes.


INPUT:
LB_WORKLIST

OUTPUTS:
ANALYZE TABLE LB_WORKLIST COMPUTE STATISTICS;
ALTER INDEX LB_WORK_EXP_I REBUILD COMPUTE STATISTICS;
ALTER INDEX LB_WORKLIST_NOTIF REBUILD COMPUTE STATISTICS;
ALTER INDEX LB_WORK_FRM_LOGIN_I REBUILD COMPUTE STATISTICS;
ALTER INDEX LB_WORKLIST_LOGIN_TEST REBUILD COMPUTE STATISTICS;


/* scripts- table analyze and index rebuild */
SET SERVEROUT ON;

DECLARE
TYPE alalyze_cur IS REF CURSOR;
ind_cursor alalyze_cur;
TABLE_NAME VARCHAR2(30);
INDEX_NAME VARCHAR2(30);
stmt VARCHAR2(200);

BEGIN
TABLE_NAME:=UPPER('&TABLE_NAME');
stmt := 'SELECT index_name FROM USER_INDEXES WHERE INDEX_TYPE <> ''LOB'' AND TABLE_NAME = :j';
DBMS_OUTPUT.PUT_LINE('ANALYZE TABLE '||TABLE_NAME||' COMPUTE STATISTICS;');
OPEN ind_cursor FOR stmt USING table_name;

LOOP
FETCH ind_cursor INTO index_name;
EXIT WHEN ind_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ALTER INDEX '||index_name||' REBUILD COMPUTE STATISTICS;');
END LOOP;
END;
/