Re: Bulk Collect without LIMIT

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Thu, 12 Jan 2006 19:13:12 +0000
Message-ID: <rh9ds119tphcr2opr2rbclgpvn7r51hmp6@4ax.com>

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

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Received on Thu Jan 12 2006 - 13:13:12 CST