Oracle Database Discussions

Dear All,
Any Ideas to speedup the rebuilding indexes process on a oracle table. I have 14 indexes on a table, which have around 200 million records.


*****The following is my code to rebuild indexes.******


PROCEDURE sls_f_trans_create_indexes (p_run_key INTEGER) AS
-- Set constants
c_stat_running CONSTANT VARCHAR2 (30) NOT NULL := 'RUNNING';
c_stat_complete CONSTANT VARCHAR2 (30) NOT NULL := 'COMPLETED';
c_stat_error CONSTANT VARCHAR2 (30) NOT NULL := 'ERROR';
v_proc_err EXCEPTION;
v_proc VARCHAR2 (30);
v_proc_task_desc VARCHAR2 (255);
v_task_desc VARCHAR2 (255);
v_err_msg VARCHAR2 (255);
v_idx_storage VARCHAR2 (100) := ' NOLOGGING PARALLEL(DEGREE 4 INSTANCES 1) TABLESPACE HPSUP_IDX_2';
v_run_key INTEGER;
v_proc_key INTEGER;
v_proc_task_key INTEGER;
v_row_cnt INTEGER;
v_func_ret INTEGER;
b_success BOOLEAN;
BEGIN
v_run_key := p_run_key;
v_proc := 'SLS_F_TRANS_CREATE_INDEXES';
v_proc_task_desc := 'Process: Create SLS_F_TRANSACTION indexes';
v_proc_key := sls_l_process_upsert (v_run_key, v_proc, v_proc_task_desc, c_stat_running);
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
v_err_msg := 'OTHERS: CREATE indexes SLS_F_TRANSACTION table';
v_proc_task_key := sls_l_process_upsert (p_run_key, v_proc, v_task_desc, c_stat_running);

EXECUTE IMMEDIATE 'CREATE BITMAP INDEX IDX_SLS_F_TRANS_01 ON SLS_F_TRANSACTION (SYS_CD)' ||v_idx_storage ;

EXECUTE IMMEDIATE 'CREATE BITMAP INDEX IDX_SLS_F_TRANS_02 ON SLS_F_TRANSACTION (CUST_KEY)' ||v_idx_storage ;

EXECUTE IMMEDIATE 'CREATE BITMAP INDEX IDX_SLS_F_TRANS_03 ON SLS_F_TRANSACTION (TRANS_DT_KEY)' ||v_idx_storage ;

EXECUTE IMMEDIATE 'CREATE BITMAP INDEX IDX_SLS_F_TRANS_04 ON SLS_F_TRANSACTION (GL_DT_KEY)' ||v_idx_storage ;

EXECUTE IMMEDIATE 'CREATE BITMAP INDEX IDX_SLS_F_TRANS_05 ON SLS_F_TRANSACTION (USER_KEY)' ||v_idx_storage ;

EXECUTE IMMEDIATE 'CREATE BITMAP INDEX IDX_SLS_F_TRANS_06 ON SLS_F_TRANSACTION (PRDCT_KEY)' ||v_idx_storage ;

EXECUTE IMMEDIATE 'CREATE BITMAP INDEX IDX_SLS_F_TRANS_07 ON SLS_F_TRANSACTION (GL_CO_KEY)' ||v_idx_storage ;

EXECUTE IMMEDIATE 'CREATE BITMAP INDEX IDX_SLS_F_TRANS_08 ON SLS_F_TRANSACTION (SYS_KEY)' ||v_idx_storage ;

EXECUTE IMMEDIATE 'CREATE BITMAP INDEX IDX_SLS_F_TRANS_09 ON SLS_F_TRANSACTION (OFC_KEY)' ||v_idx_storage ;

EXECUTE IMMEDIATE 'CREATE BITMAP INDEX IDX_SLS_F_TRANS_10 ON SLS_F_TRANSACTION (SRCH_TYPE_KEY)' ||v_idx_storage ;

EXECUTE IMMEDIATE 'CREATE BITMAP INDEX IDX_SLS_F_TRANS_11 ON SLS_F_TRANSACTION (VERTICAL_KEY)' ||v_idx_storage ;

EXECUTE IMMEDIATE 'CREATE BITMAP INDEX IDX_SLS_F_TRANS_12 ON SLS_F_TRANSACTION (SCNRO_KEY)' ||v_idx_storage ;

EXECUTE IMMEDIATE 'CREATE BITMAP INDEX IDX_SLS_F_TRANS_13 ON SLS_F_TRANSACTION (TRN_TYPE_KEY)' ||v_idx_storage ;

EXECUTE IMMEDIATE 'CREATE BITMAP INDEX IDX_SLS_F_TRANS_14 ON SLS_F_TRANSACTION (SALES_REP_KEY)' ||v_idx_storage ;

COMMIT;

END;