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