Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: benchmarking, which statement is faster
Thomas Kyte wrote:
> In article <3B5DD1ED.F0C996D2_at_attws.com>, "Daniel says...
> >
> >DS wrote:
> >
> >> hi all,
> >>
> >> has anybody benchmarked or has any idea which of the
> >> following statements will execute faster?
> >>
> >> a) INSERT INTO TABLE_X (field1,
> >> field2,
> >> field3,
> >> ...
> >> ...
> >> ...
> >> fieldn)
> >> SELECT source_field1,
> >> source_field2,
> >> source_field3,
> >> ...
> >> ...
> >> ...
> >> source_fieldn)
> >> FROM TABLE_Z, TABLE_A
> >> WHERE xx = yy
> >> AND zz = aa;
> >>
> >> b) CURSOR my_cursor IS
> >> SELECT source_field1,
> >> source_field2,
> >> source_field3,
> >> ...
> >> ...
> >> ...
> >> source_fieldn)
> >> FROM TABLE_Z, TABLE_A
> >> WHERE xx = yy
> >> AND zz = aa;
> >>
> >> my_record my_cursor%rowtype;
> >>
> >> OPEN my_cursor;
> >> LOOP
> >> FETCH my_cursor INTO my_record;
> >> EXIT WHEN my_cursor%NOTFOUND;
> >>
> >> INSERT INTO TABLE_X (field1,
> >> field2,
> >> field3,
> >> ...
> >> ...
> >> ...
> >> fieldn)
> >> VALUES ( my_cursor.source_field1,
> >> my_cursor.source_field2,
> >> my_cursor.source_field3,
> >> ...
> >> ...
> >> my_cursor.source_fieldn);
> >>
> >> END LOOP;
> >>
> >> CLOSE my_cursor;
> >>
> >> Note:
> >>
> >> 1) The select statements are the same.
> >> 2) source_field[1..n] can be a simple field from a table
> >> or the value returned by the invocation of a database function.
> >>
> >> thanks.
> >
> >Explicit cursors are preferable to implicit cursors. If not the first
> >time they execute, thereafter. In addition they also solve space
> >problems with temp and rollback by allowing incremental commits.
> >
>
>
>
>
>
>
>
>
>
>
>> Expert one on one Oracle, programming techniques and solutions for Oracle.
> --
> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
Beg to disagree here Tom. The day I find a developer at any company where I consult using anything other than explicit for pulling records in a loop they will not longer be on the project. And the other DBAs in my group agree.
Daniel A. Morgan Received on Wed Jul 25 2001 - 14:27:51 CDT