Re: benchmarking, which statement is faster

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.

[Quoted] 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.

Daniel A. Morgan Received on Tue Jul 24 2001 - 21:52:13 CEST