Re: Bulk Collect without LIMIT

Andy Hassall wrote: 
On Thu, 12 Jan 2006 08:53:16 -0800, DA Morgan <damorgan@xxxxxxxxx> 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@xxxxxxxxxxxxxxxx
(replace x with u to respond)
.

Relevant Pages

  • Re: Calling a SP inside a cursor loop..
    ... every loop iteration of the cursor. ... Fetch next From EffectiveDate_Cursor Into @FLD1,@FLD2 ... and If the Fetch stmt is below the begin Stmt, the loop iterations are ... the proper way to program a cursor loop is: ...
    (comp.databases.ms-sqlserver)
  • Re: BULK COLLECT - Can it be implemented for a few rows .
    ... So we designed a NEW PROC using BULK COLLECT. ... CURSOR CUR2IS SELECT DISTINCT ... COMMIT; ... FOR I IN CUR1LOOP ...
    (comp.databases.oracle.tools)
  • Re: Fetch out of sequence in cursor
    ... end loop; ... I have a cursor which loops through a table and does some processing. ... Towards the end of the loop I update another table and I want to COMMIT each ... I'm getting a fetch out of sequence message when i try to ...
    (comp.databases.oracle.misc)
  • Re: What is wrong with this????
    ... cursor c_triggers is ... fetch c_triggers into v_trgname; ... end loop; ... As you can see there is no rocket science in the script. ...
    (comp.databases.oracle.server)
  • Re: using t-sql cursors ...
    ... You are right, use WHILE loop... ... See this from the BOL: ... DECLARE authors_cursor CURSOR FOR SELECT au_lname, ... I know that i can't fetch ...
    (microsoft.public.sqlserver.programming)