Re: PL/SQL Cursor Performance

Hi Salman,

Generally speaking every statements passes through three phases parse
execute
fetch
Parse and execute are done on the server once. In the first example, the select is done once, but rows are fetched individually.
The second example sql is done at the server only, no rows are fetched by the client.
This will be always much much faster.
Indexes can have positive and negative effects. If a table has many indexes, it is quite likely that all your indexes need to be modified upon insertion also. Rule of thumb is have a max of 5 indexes on any table. You may want to read about the buffer cache in the Oracle Server Concepts manual. Also you may want to read bout how to trace a session and interpret the trace files being generated.

Hth,

Sybrand Bakker, Oracle DBA

salman_aziz_at_my-deja.com wrote in message <7ik5mc$4u8$1_at_nnrp1.deja.com>...
>Hi
>I have a performance question on PL/SQL cursors. Please have a look at
>following piece of code.
>
>for crs in
>(select a, b, c
>from x_table )
>loop
> insert into y_table(a, b, c)
> values
> (crs.a, crs.b, crs.c);
>
>end loop;
>
>Questions:
>1. Does the cursor get all the rows in one go from the database?
>OR
>2. It is selects a row and inserts it and then gets the next row for
>next insertion?
>
>If there are 1 million rows in source table x_table then according to 1
>the select is performed once fetching all rows before inserting into the
>target table y_table.
>But if 2 is right then insert of each row into target table is preceded
>by a select form the database.
>Or there is third possibility that a set of rows is selected and placed
>in some kind of buffer of a set size. Once buffer is full the rows are
>inserted and then another SELECT is performed to get the next set for
>the insert.
>
>3. How is the following insert different from the one above.
> insert into y_table (a,b,c)
> select * from x_table;
>
>4. How does the indexes on the source table will be helpful in terms of
>performing the insert in both scenarios.
>
>I will appreciate your comments and suggestion on further reading
>material.
>
>Thanks
>
>Salman
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Thu May 27 1999 - 15:45:05 CDT