Re: benchmarking, which statement is faster

From: Daniel A. Morgan <Daniel.Morgan_at_attws.com>
Date: Wed, 25 Jul 2001 12:27:51 -0700
Message-ID: <3B5F1DB7.42F4A196_at_attws.com>


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

>

> explicit cursors in PLSQL rot.
>

> They are slower.
> They are harder to code.
> There is NO performace benefit to them the first time, the second time or any
> time.
>

> If an insert into is to be a single transaction (as they almost always are), you
> need to have sufficient rollback configured. Incremental commits are a time
> waster and introduce bugs (how many people make their "incremental" things
> "restartable" -- they always seem to forget that. When they blow up, they are
> left with a mess)
>

> Temp space -- well, you would need EXACTLY the same amount for an explicit
> cursor as you would an implicit -- there would be no difference there (if we
> gotta sort one of them, we gotta sort the other).
>

> INSER INTO SELECT * FROM
>

> Over
>

> a cursor
>

> wins anytime.....
>

> see
> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1017644816229
> for more details
>

> >Daniel A. Morgan
> >
>

> --
> Thomas Kyte (tkyte_at_us.oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp

[Quoted] Beg to disagree here Tom. The day I find a developer at any company where I consult [Quoted] using anything other than explicit for pulling records in a loop they will not [Quoted] longer be on the project. And the other DBAs in my group agree.

Daniel A. Morgan Received on Wed Jul 25 2001 - 21:27:51 CEST

Original text of this message