Re: Implicit vs Explicit cursors

Rajeev Shankar wrote:
>
> Hello all:
>
> I have a question regarding implicit vs explicit cursors.
>
> 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.
>
> I work on Unix Platform ( SUNOS) with version 7.2 of oracle.
>
> I would appreciate a reply on the above matter.
>
> Thanks
>
> Rajeev

I have found that using the "For rec in cursor loop" processing is much faster than the "Open, Fetch, Close" processing.... Received on Thu May 16 1996 - 00:00:00 CEST