SQL in PL/SQL Loop
- From: "Kumar, Arvind IN GGN SISL" <arvind.kumar2@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 19 Oct 2006 13:00:03 +0530
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
Other related posts:
