| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL Cursor Performance
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.
> >
> >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
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
--
"Some days you're the pigeon, and some days you're the statue." Received on Fri May 28 1999 - 02:54:43 CDT
 |
 |