Re: count(*) vs. a cursor when determining the existing record on primary key

Originally posted by Sybrand Bakker
> On 20 Jun 2003 10:15:56 -0700, javapda_at_yahoo.com (jgk) wrote:
>
> >With regard to the example without a cursor above,
> >won't you get an exception thrown any time you do the query
> >and there is no matching data?
>
> sure you will. no_data_found
> And the approach is inefficient to as a select into always performs 2
> fetches
> 1 to cater for 'NO DATA FOUND'
> 2 the second to cater for 'TOO MANY ROWS'
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Sorry, but the SELECT INTO it is MORE efficient than using an explicit cursor, as I went to the trouble of demonstrating earlier in this thread. Oracle is smart enough not to try the second fetch if there is a unique key on the predicate.

Of course, if the select could fail, you would need an exception handler. Ditto, if the FETCH returns NOTFOUND you ought to be testing for it. That is irrelevant to the point, which was about performance: SELECT INTO beats explicit cursor for performance, period, contrary to the myth so widely believed even by Senior DBAs apparently ;)

--
Posted via http://dbforums.com