Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: BULK COLLECT and QUERY REWRITE

Re: BULK COLLECT and QUERY REWRITE

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: 27 Oct 2006 06:19:28 -0700
Message-ID: <1161955168.643767.190560@i42g2000cwa.googlegroups.com>

chris wrote:
> I understand that Oracle 10g will rewrite your CURSOR FOR LOOP
> statements into a BULK COLLECT statement.
>
> I am contemplating no longer explicitly writing the BULK COLLECT from
> now on as it reduces the number of lines of code and greatly simplifies
> code.
>
> Can anyone see any serious flaws in this strategy?
>
> Kind Regards
> Chris

Not quite. Nothing is rewritten, PL/SQL simply fetches 100 rows at a time from cursors now, which is roughly equivalent to BULK COLLECT LIMIT 100, even a bit more efficient because it is done internally and you avoid target collections memory maintenance and data copy costs. So in the end using FOR LOOPs instead of explicit BULK COLLECTs for forward-only cursors looks like the right thing to do in 10g. There may be some cases where BULK COLLECT may be required or is more efficient, but they are rare. The best option still is doing everything in SQL though, so if you can avoid PL/SQL altogether don't hesitate to do so.

Hth,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Fri Oct 27 2006 - 08:19:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US