The following sql query retrieves data in about 15 minutes.
select distinct o.col1, o.col2, o.col3, o.col4, o.col5,
o.col6, o.col7, o.col8, s.col1, a.col1, a.col2
from w_table1 o, w_table2 s, w_table3 a
where o.col1 = s.col1 and
o.col2 = s.col2 and
o.col4 = a.col4 and
o.col5 = a.col1 and
o.col6 between sysdate -10 and sysdate
and o.col7 like '%599QT%';
The above script gives a rough idea of what I need. ( May not
be right in syntax).
However when I run a similar query to the one given above the output
is very fast ( about 15 mins ). I had tuned the query using explain
plan.
Now when I run the same query using explicit cursor in PL/SQL
the process takes hours to run.
Here is the sample code
DECLARE
CURSOR dbase_cursor IS
select distinct o.col1, o.col2, o.col3, o.col4, o.col5,
o.col6, o.col7, o.col8, s.col1, a.col1, a.col2
from w_table1 o, w_table2 s, w_table3 a
where o.col1 = s.col1 and
o.col2 = s.col2 and
o.col4 = a.col4 and
o.col5 = a.col1 and
o.col6 between sysdate -10 and sysdate
and o.col7 like '%599QT%';
dbase_cursor_rec dbase_cursor%ROWTYPE;
BEGIN
OPEN dbase_cursor;
LOOP
FETCH dbase_cursor into dbase_cursor_rec;
EXIT WHEN dbase_cursor%NOTFOUND;
INSERT INTO temp values(......); /Not exact code/
END LOOP;
CLOSE dbase_cursor;
END;
/
My question is why is there such a huge difference in performance.
I had been told that there with regard to performance there is
a marginal difference between implicit and explicit cursors.
When I ran the above two processes, I was the only user, so there
was not any load on the server.
