PL/SQL Cursor Performance

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 - 14:16:32 CDT