Re: PL/SQL Cursor Performance

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.
>
>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

No (although in Oracle8i, release 8.1 there is an array fetch that would allow you to get all of the rows. In 8.0 and before -- no such option)

>2. It is selects a row and inserts it and then gets the next row for
>next insertion?
>

fetch
insert
fetch
insert....

is the way it goes.

>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.

preceded by a FETCH -- not a select (semantic difference but relevant none the less).

>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.
>

not in plsql.

>3. How is the following insert different from the one above.
> insert into y_table (a,b,c)
> select * from x_table;
>

it'll be many times faster. The end result will be the same. You will write much less code.

>4. How does the indexes on the source table will be helpful in terms of
>performing the insert in both scenarios.
>

not enough information. If x_table is a TABLE and not a view and there are no predicates on x_table -- indexes won't be relevant. We will full scan x_table.

OTOH -- if x_table is a view with complex joins and predicates -- indexes my play a big role.

If x_table really is a million rows -- you will have an issue with rollback segments. You may want to look into the SQLPLUS copy command -- its a very fast way to copy data from one table to another and allows you to set up a batch 'commit count' (commit after every N fetches of M rows). It does have the option 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 FETCH is performed".

>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.

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 - 07:13:00 CDT