Hi Experts,
I am on Oracle 11.2.0.3 on Linux. I have a sql that inserts data in a history/archive table from a main application table based on date. The application table has 3 million rows in it. and all rows that are older then 6 months should go into a history/archive table. this was recently decided and we have 1 million rows that satisfy this criteria. This insert into archive table is taking about 3 minutes. The explain plan shows a full table scan on the main table - which is the right thing as we are pulling out 1 million rows from main table into history table.
My question is that, is there a way I can make this sql go faster?
Here is the query plan (I changed the table names etc.)
INSERT INTO EMP_ARCH
SELECT *
FROM EMP M
where HIRE_date < (sysdate - :v_num_days);
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 96.22 165.59 92266 147180 8529323 1441230
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 96.22 165.59 92266 147180 8529323 1441230
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 166
Rows Row Source Operation
------- ---------------------------------------------------
1441401 TABLE ACCESS FULL EMP (cr=52900 pr=52885 pw=0 time=21189581 us)
I heard that there is a way to use opt_param hint to increase the multiblock read count but didn't seem to work for me....I will be thankful for suggestions on this. also can collections and changing this to pl/sql make it faster?
Thanks,
OrauserN