Re: fastest way to determine if 0 or more records are present in table?

From: Chuck <chuckh_nospam_at_softhome.net>
Date: 19 Feb 2004 13:50:41 GMT
Message-ID: <Xns949459F8C48A5chuckhsofthomenet@130.133.1.4>

flavio_at_tin.it (FC) wrote in
news:2bd78ddf.0402190536.53e702c2_at_posting.google.com:

> Chuck <chuckh_nospam_at_softhome.net> wrote in message
> news:<Xns94937D64E2FFchuckhsofthomenet_at_130.133.1.4>...

>>
>> ...
>> 
>> But you probably don't want to query the entire table of there are 
>> millions of rows. I'd use a simple query like this...
>> 
>> select count(*) from mytable where rownum < 2;
>> 
>> Returns 0 if no rows exist, or 1 if 1 or more rows exit.

>
>
> I agree, after comparing the output of autotrace, the simple query
> above guarantees the quickest answer, even when you specify further
> conditions in the where clause.
>
> I took for granted what was stated 4 years ago, but it doesn't seem to
> hold any more.
> May be the optimizer of versions prior to 8.1.7 was not that smart.
>
> Bye,
> Flavio
>

The optimizer in 8.1.7 is noticably smarter than what was available 4 years ago. The 9i optimizer is supposed to be even better. Not sure about 10g as it's way to new for me to be comfortable with it. I prefer to give new versions at least a year before running any of my databases with them. We are just now starting the move to 9.2.

-- 
Chuck
Remove "_nospam" to reply by email

Received on Thu Feb 19 2004 - 07:50:41 CST