I have a SQL query which joins three huge tables. (given below)
insert /*+ append */ into final_table (oid, rmeth, id, expdt, crddt, coupon, bitfields, processed_count)
select /*+ full(t2) parallel(t2,31) full(t3) parallel(t3,31)*/
seq_final_table.nextval, '200', t2.id, t3.end_date, '1/jul/2009',123,t2.bitfield, 0
from table1 t1, table2 t2, table3 t3 where
t3.end_date is not null and
(trunc(t1.expiry_date) != trunc(t3.end_date) or trim(t1.expiry_date) is null);
Below are some statistics of the three tables.
Table_Name RowCount Size(MB)
table1 36469938 532
table2 242172205 39184
table3 231756758 29814
The above query ran for 30+ hours, and returned with no rows inserted into final_table. I didn't get any error message also.
But when I ran the query with table1 containing just 10000 records, the query completed succesfully within 20 minutes.
Can any one please optimize the above query?