Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!cyclone.bc.net!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: vslabs@onwe.co.za (Billy Verreynne)
Newsgroups: comp.databases.oracle.misc
Subject: Re: How to optimize this query?
Date: 7 Aug 2003 23:47:26 -0700
Organization: http://groups.google.com/
Lines: 24
Message-ID: <1a75df45.0308072247.7841a57d@posting.google.com>
References: <bgn6st$civ@netnews.proxy.lucent.com> <1a75df45.0308050245.39bf6ad4@posting.google.com> <4b5394b2.0308050758.263bd44c@posting.google.com> <1a75df45.0308052224.5da053d0@posting.google.com> <3F30E0D2.5080106@magicinterface.com> <1223jvomkc43md42gi5rem94mk32uvltkp@4ax.com> <4b5394b2.0308071222.3a084a4c@posting.google.com>
NNTP-Posting-Host: 198.54.206.91
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1060325247 16108 127.0.0.1 (8 Aug 2003 06:47:27 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 8 Aug 2003 06:47:27 GMT
Xref: newssvr20.news.prodigy.com comp.databases.oracle.misc:130354

ed.prochak@magicinterface.com (Ed prochak) wrote i

> option 2 Query "without" data transfer
>          select count(*)
>             from <tables>
>            where <conditions/joins>;
> is IMHO potentially misleading. (IOW, this one is a gamble, agreed?)
> scanning the index is just as misleading as using the stats info. IOW
> you get burned in production when you change the select clause back to
> the actual columns.

Not sure if I agree that this is a gamble Ed. I can not recall ever
seeing a different execution plan when mucking about with tuning and
using hints and doing a COUNT(*) instead of a * for all columns.

Where it can be misleading though is when you are dealing with a
FIRST_ROW hint in order to get the first set of data userside asap. In
that case the COUNT(*) will be "slower" from a user perspective.

But option 3 is a safer bet I agree and I usually use that (also makes
copy & paste easier ;-).

--
Billy