Re: benchmarking, which statement is faster

In article <wRR77.1888$vN4.5343_at_news11-gui.server.ntli.net>, "Keith says...
>
>
>"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message
>news:9jnb6c0c3k_at_drn.newsguy.com...
>
>>
>> Until and unless you can post an example of any case where explicit
 cursors beat
>> an implicit cursor.... I am suspicious of your conclusion.
>>
>
>You can generate an unreasonable example. The implicit cursor performs a
>second fetch. This means if you look for a single row via a full-table scan,
>the entire table will be scanned every time whereas with an explicit cursor,
>on average, only half the table will be scanned. It is un entirely unreal
>situation, but one for which you can easily engineer a test case.
>

ahh but they you are comparing apples and pigs....

a select into does the following for you:

o ensures you get at least one row
o at most one row

anything else is an error. an explicit cursor must use this code:

open c1
fetch c1
if ( c1%notfound ) then raise no_data_found; end if; fetch c1
if ( c1%found ) then raise too_many_rows; end if; close c1;

in order to emulate a select into. That ensures you don't have garbage in your fetched data (the first if (c1%notfound) and ensures you don't have garbage in your table (select into says "i'm getting THE row" a row)

If you really don't care about the too_many_rows and you wish you avoid your stated issue above code:

select x into l_x from t where <......> AND ROWNUM=1;

it'll stop scanning when it finds the first rows - -the select into will still be faster.

>
>

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