Oracle Pipeline (table function)
Posted on 2012-04-11
I have a procedure in Oracle (11gR2) that takes several hour to run. If possible, could you let me know how I could improve the performance of the query?
The query of the procedure is:
Procedure MyProc IS
WITH t AS (SELECT to_date('01-DEC-2010') AS dt_init, sysdate AS dt_final FROM dual)
SELECT TO_CHAR(add_months(TRUNC(dt_init, 'mm'), level - 1), 'RRRRMM') AS y_mon,
to_char(add_months(TRUNC(dt_init, 'mm'), level - 1), 'J') AS dt_start,
to_char(add_months(TRUNC(dt_init, 'mm'), level) - 1, 'J') AS dt_end
CONNECT BY level <= months_between(dt_final, dt_init) + 1;
FOR rec IN cur1
INSERT /*+ PARALLEL */
(f1, f2, f3, f4)
SELECT MyTab2.x1, SUM(MyTab2.x2), SUM(MyTab2.x3), SUM(MyTab2.x4) FROM MyTab2
INNER JOIN MyTab3 ON MyTab2.x1 = MyTab3.y1
AND MyTab2.x1 BETWEEN rec.dt_start AND rec.dt_end
GROUP BY MyTab2.x1;
The cursor1 has fixed 17 records. And the loop is run thus 17 times. Table MyTab2 has 8million records. MyTab3 has about 115 records.
In the end the table MyTab1 contains about 27000 records.
MyTab3.y1 has the bitmap index. MyTab2.x1 has the unique index.
The above query takes 3 hours to run and is not usable. Is there any way I could make it fasted by Oracle pipelines or table functions? Thanks a lot.