Re: benchmarking, which statement is faster

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

[Quoted] explicit cursors in PLSQL rot.

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

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

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