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 without LIMIT

Re: Bulk Collect without LIMIT

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 12 Jan 2006 14:35:16 -0800
Message-ID: <1137105314.212337@jetspin.drizzle.com>


Andy Hassall wrote:
> On Thu, 12 Jan 2006 08:53:16 -0800, DA Morgan <damorgan_at_psoug.org> wrote:
>
>

>>> There might also be an upper limit of 2147483647 rows according to the bit in
>>>the PL/SQL manual about collection type numeric subscript limits.
>>>
>>
>>Just had a thought on this but don't know the answer. As I understand it 
>>Oracle, with 10g, merged the BULK COLLECT and FETCH to use the same 
>>mechanism. If you just say OPEN CURSOR ... FETCH cursor INTO record it
>>is as fast as BULK COLLECT without the LIMIT clause. Is it essentially
>>doing the same thing and thus subject to disaster with a very large table?

>
>
> Can't spot a reference right away in the manual, but this Tom Kyte article has
> a note:
>
> http://www.oracle.com/technology/oramag/oracle/04-sep/o54asktom.html
> "This is because Oracle 10g includes an optimization in PL/SQL that silently
> turns your single row fetches into BULK COLLECTs of 100 rows each. That is,
> PL/SQL is silently fetching as many as 100 rows on the first fetch and then
> doling them out as you request them."
>
> So it should be comparable performance to using BULK COLLECT ... LIMIT 100.
>
> The following demonstrates that prefetch is definitely 100 rows:
>
> SQL> create table t1 (c number);
>
> Table created
>
> SQL> create table t2 (c number);
>
> Table created
>
> SQL> begin
> 2 for i in 1..500 loop
> 3 insert into t1 values (i);
> 4 end loop;
> 5 end;
> 6 /
>
> PL/SQL procedure successfully completed
>
> SQL> select count(*) from t1;
>
> COUNT(*)
> ----------
> 500
>
> SQL> commit;
>
> Commit complete
>
> SQL> create or replace function p (c number)
> 2 return number
> 3 is
> 4 pragma autonomous_transaction;
> 5 begin
> 6 /* Every time this is called, write a row
> 7 * to table t2. */
> 8 insert into t2 values (c);
> 9 commit;
> 10 return c;
> 11 end;
> 12 /
>
> Function created
>
> SQL> declare
> 2 begin
> 3 /* Open up a cursor for loop, also selecting
> 4 * the "p" function which will write rows to
> 5 * t2 for every row fetched from t1. */
> 6 for c in (select c, p(c) from t1)
> 7 loop
> 8 /* Break out of the loop immediately. */
> 9 exit;
> 10 end loop;
> 11 end;
> 12 /
>
> PL/SQL procedure successfully completed
>
> SQL> select count(*) from t2;
>
> COUNT(*)
> ----------
> 100
>

100 rows each ... thanks ... that was what I was missing.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Thu Jan 12 2006 - 16:35:16 CST

Original text of this message

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