Hmmm, di 9i dan 10g saya, udah pernah mengalami menggunakan bulk collect,tapi untuk rows processes yang lebih dari 50jt - 500jt rows dengan kolom more then 300 - 1024 columns, saya merasa kurang puas karena hit long query terlalu lama dan juga jika di gabung untuk proses merge, update dan insert ke table ... membuat ... snapshot to old error.
akhirnya saya tetap menggunakan cara tradisional ... for i in ( select ...) loop dml .... end loop; namun untuk dml saya kirim ke global temporary table (commit preserve rows) (trigger insert / update / delete) ke table tujuan, lebih menyelesaikan masalah terutama untuk staging antar database dengan database link. proses yang selamanya bisa ... 4 - jam bisa cuma 1 jam 9 untuk insert, update bisa 2 jam. namun jika prose di barengain dengan "_disable_logging"=TRUE lebih cepat lagi. Tapi di 11g anda bisa gunakan RESULT_CACHE contoh table t SQL> create table t 2 as 3 select * 4 from all_objects; Table created. tradisional ... SQL> create or replace procedure 2 my_function 3 as 4 begin 5 for x in 6 (select owner, 7 object_type, 8 count(*) cnt 9 from t 10 group by owner, object_type 11 order by owner, object_type ) 12 loop 13 -- do_something 14 null; 15 end loop; 16 end; 17 / Procedure created. test ... SQL> set timing on SQL> exec my_function PL/SQL procedure successfully completed. Elapsed: 00:00:01.54 SQL> exec my_function PL/SQL procedure successfully completed. Elapsed: 00:00:00.10 SQL> exec my_function PL/SQL procedure successfully completed. Elapsed: 00:00:00.11 SQL> set timing off cara baru ... ============== SQL> create or replace procedure 2 my_function 3 as 4 begin 5 for x in 6 (select /*+ result_cache */ 7 owner, 8 object_type, 9 count(*) cnt 10 from t 11 group by owner, object_type 12 order by owner, object_type ) 13 loop 14 -- do_something 15 null; 16 end loop; 17 end; 18 / Procedure created. test .... SQL> set timing on SQL> exec my_function PL/SQL procedure successfully completed. Elapsed: 00:00:00.10 SQL> exec my_function PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 SQL> exec my_function PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 SQL> set timing off hmmm lebih cepat ... namun ini berhubungan dengan parameter PGA_AGGREGATE_TARGET allocation, karena stack space allocation ada di PGA (read more in oracle architect) . Mudah2an bisa membantu. Thanks, Andri L. Vicko, OCP --- In [email protected], "Moch Firman N" <[EMAIL PROTECTED]> wrote: > > hmmm,,, > boleh di perjelas parameter-nya... > alasan dari , bulk collect is the best... ? > > karna menurut gw, > klw kita mu ngomongin soal performance, parameter nya mesti jelas duluu.... > jadi gak bisa karna di satu kondisi bulk collect is the best... > truss.. langsung di sikat abisss .. untuk semua kondisi di sarankan pakai > bulk collect.... > > thnx.... > > 2008/7/22 .:Sofhal Jamil:. <[EMAIL PROTECTED]>: > > > IMHO, bulk collect is the best... > > > > > > 2008/7/22 dony widiotomo <[EMAIL PROTECTED] <Don_wid%40yahoo.com>>: > > > Dear Oracle Master.. > > > > > > ada ga ya perbedaan performance antara menggunakan : > > > > > > cursor select... > > > loop > > > .. > > > end loop > > > > > > or using : > > > > > > select ... bulk collect into ... > > > for i in cur.first .. cur.last > > > loop > > > ... > > > end loop > > > > > > query yg digunakan adalah query yg sama.. > > > > > > thanx for all respons... > > > > > > regards, > > > Dony Wid > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > -- > > Best regards, > > Sofhal Jamil > > PT Datainfo Milenium Perkasa > > Jl. Mundu Raya No. 2, Jati, Pulo Gadung > > Jakarta Timur, 13220 > > Telp./Faks (021) 489-0705 > > http://sofhaljamil.com > > > > > > > [Non-text portions of this message have been removed] >
