Re: benchmarking, which statement is faster

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