Re: benchmarking, which statement is faster

In article <3B5F1DB7.42F4A196_at_attws.com>, "Daniel says...
>
>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?
>> >>
>> >
>> >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
>
>Beg to disagree here Tom. The day I find a developer at any company where I
>consult
>using anything other than explicit for pulling records in a loop they will not
>longer be on the project. And the other DBAs in my group agree.
>
>Daniel A. Morgan
>

Everyone is free to disagree, doesn't make them right. Even if they have a quorum.

however, if you read:

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

It conclusively shows that

insert into select * from

beats (kills, outperforms, etc)

loop

   fetch
   insert
end

It makes sense. Why in the heck would you even consider pulling the records back to PLSQL only to send them back to the database.

If you read:

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

it conclusively shows that SELECT ... INTO from t is faster, better, cheaper, easier, more readable then

open
fetch
close

Until and unless you can post an example of any case where explicit cursors beat an implicit cursor.... I am suspicious of your conclusion.

What you believe to be true is fine.

What is truly true and real -- thats a different matter all together.

What is the basis in reality and fact for your conclusion? Where is the test case, the sample, the empirical proof?

I myself would take the developer that demands on using explicit cursors, educate them as to the power and beauty of the implicit cursor -- and only upon finding them ignoring fact remove them.

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