Hi Tom,

Good morning.
We have a dynamic view which is growing day by day due to the combinations are increasing.

SO we have decided to create a table from the view , for which we have created a procedure which runs fine but it takes lot of time like 3 hour 40 minutes to insert all the records from view to table.


Here is the code i am using for the insert the reccords.
FOR i IN 1..vn_loopctr LOOP
vn_cnt := vn_cnt + 1;
V_SQL := 'INSERT INTO ' || COMB_TABLE_NAME || ' SELECT /*+ parallel (c,15) */ c.* FROM ' || VS_COPY_TABLE|| ' c WHERE c.comb_id between ' || VN_LAST || ' and ' || CHUNK_SIZE * VN_CNT ;
EXECUTE IMMEDIATE V_SQL;
COMMIT;
v_sql := 'analyze table '||COMB_TABLE_NAME||' compute statistics';
EXECUTE IMMEDIATE V_SQL;


vn_last := (CHUNK_SIZE * vn_cnt) + 1;

v_sql := 'INSERT INTO ' || TARGET_TABLE || ' SELECT /*+ parallel (pd,15) */ pd.* FROM ' || BIEO_TABLE || ' pd';
execute immediate v_sql;
commit;

v_sql:= 'truncate table ' || COMB_TABLE_NAME;

execute immediate v_sql;
commit;

END LOOP;

Only the insert section seems to be taking very long time. for each chuck it is taking 4 min. and we have to process 144 chunks.

Kindly suggest if we do do something to run it faster than this.

Regards,
Giri

FOR i IN 1..vn_loopctr LOOP
dbms_output.put_line(systimestamp);
vn_cnt := vn_cnt + 1;
V_SQL := 'INSERT INTO ' || COMB_TABLE_NAME || ' SELECT /*+ parallel (c,15) */ c.* FROM ' || VS_COPY_TABLE|| ' c WHERE c.comb_id between ' || VN_LAST || ' and ' || CHUNK_SIZE * VN_CNT ;
EXECUTE IMMEDIATE V_SQL;
COMMIT; 
dbms_output.put_line(systimestamp);
v_sql := 'analyze table '||COMB_TABLE_NAME||' compute statistics'; 
EXECUTE IMMEDIATE V_SQL;