Re: PL/SQL Cursor Performance

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 28 May 1999 19:45:21 GMT
Message-ID: <375cf08a.31243846@newshost.us.oracle.com>

A copy of this was sent to Connor McDonald <connor_mcdonald_at_yahoo.com> (if that email address didn't require changing) On Fri, 28 May 1999 15:54:43 +0800, you wrote:

>Thomas Kyte wrote:
>>
>> A copy of this was sent to salman_aziz_at_my-deja.com
>> (if that email address didn't require changing)
>> On Thu, 27 May 1999 19:16:32 GMT, you wrote:
>>
>> >Hi
>> >I have a performance question on PL/SQL cursors. Please have a look at
>> >following piece of code.
>> >

[snip]

>
>Are the new array fetch options in 8i "equivalent" to the kind of array
>fetch that we're all used to with SQL Plus, is it implemented in a
>different manner ?
>
>Kind Regards

for inserts/updates and deletes -- yes, they are equivalent. Here is an example:

SQL> create table insert_into_table ( x int )   2 /

Table created.

SQL> set timing on
SQL> begin

  2      for j in 1 .. 5000 loop
  3          insert into insert_into_table values ( j );
  4      end loop;
  5      commit;

  6 end;
  7 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.89
SQL>
SQL> declare

  2      type numTab is table of number(4) index by binary_integer;
  3      data numTab;
  4  begin
  5      for j in 1 .. 5000 loop
  6          data(j) := j;
  7      end loop;
  8  
  9      forall i in 1 .. 5000
 10          insert into insert_into_table values( data(i) );
 11      commit;

 12 end;
 13 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.32
SQL> The first loop did it the old fashioned way -- loop/insert. The second one did it the new way. Fill up an array -- insert the array.

The differences are huge.

You can (as Jonathan L. pointed out) use dbms_sql but its a heap of code.

For selects, its "the same but different". You cannot control the array fetch size - it always gets all of the rows. For example:

  2 as

  3      type cnameTab    is table of user_tab_columns.column_name%type;
  4      type datatypeTab is table of user_tab_columns.data_type%type;
  5  
  6      cnames        cnameTab;
  7      datatypes    datatypeTab;

  8 begin
  9

 10      select column_name, data_type BULK COLLECT into cnames, datatypes
 11        from user_tab_columns
 12       where table_name = 'T'
 13       order by column_id;

that gets the result set for the query into the 2 plsql tables for names and types.

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri May 28 1999 - 14:45:21 CDT