RE: SQL in PL/SQL Loop

You may want to try to profile the run on the block with DBMS_PROFILER to see where the time is going.  

Check the PL/SQL Packages and Types Reference and the PL/SQL User's Guide and Reference guides for details on the package, also see Metalink Support Note 243755.1 for a nifty little report script.  

Ric Van Dyke

Hotsos Enterprises

Hotsos Symposium March 4-8, 2007. Be there.

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kumar, Arvind IN GGN SISL
Sent: Thursday, October 19, 2006 3:30 AM To: oracle-l_at_freelists.org
Subject: SQL in PL/SQL Loop  

Hi all,    

     Env - Dev, windows 2000,4gb, oracle 10g rel 1.

     What is the best approach to write this pl/sql loop, it's a part of table function.  

Begin   

   Execute immediate ' Select * from ' || cust_table || ' where paymethod=' || paymethod || ' bulk collect into tab_typ_cln;

   For cntr1 in tab_typ_cln.first.. tab_typ_cln.last

       Loop -- (loop1)

            Something processed....

       Pipe row(...............)

  Execute immediate ' Select * from ' || cmp_table || ' where custid= '
|| tab_typ_cln(cntr1).custid bulk collect into tab_typ_cmp;

      For cntr2 in tab_typ_cmp.first..tat_typ_cmp.last

       Loop -- (loop2)

             Something processed .......

             Pipe row(...............)

       End loop; -- (loop2)  

  Execute immediate ' Select * from ' || tpp_table || ' where custid= '
|| tab_typ_cln(cntr1).custid bulk collect into tab_typ_tpp;

     For cntr3 in tab_typ_tpp.first..tab_typ_tpp.last

    Loop -(loop3)

       Something processed .....

       Pipe row(...............)

    End loop; -(loop3)

  End loop; -- (loop1)

 End;  

For each customer (cust_table) there may be 2 or 3 or 4 components records in components table (cmp_table) and 0, 1 or 2 records in thirdparty (tpp_table).  

Table details :-

Customer - 700000

Components - 2400000

Thirdparty - 300000  

Indexes -

Customer - Paymethod - bitmap

Components - custid - Non unique normal

Thirdparty - custid - Non unique normal  

If I run the above select statements at SQL prompt it returns the data instantly but in loop its taking 35 minute to complete.  

Most Waited event is db file sequential read on Components table .      

Thanks

Arvind Kumar

--
http://www.freelists.org/webpage/oracle-l