Re: How to optimize this query?

ed.prochak_at_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