Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> 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@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 CorpReceived on Thu Jul 26 2001 - 08:35:39 CDT