Re: benchmarking, which statement is faster

In article <44ff9953.0107241139.496f2ddf_at_posting.google.com>, danny118_at_hotmail.com says...
>
>hi all,
>
>has anybody benchmarked or has any idea which of the
>following statements will execute faster?
>

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

anytime you can do it in a single statement, it will almost certainly be easier to code (thats important), easier to maintain (thats more important), and faster (thats almost incidental)....

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

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