Re: How to optimize this query?

From: Edward J. Prochak <ed.prochak_at_magicinterface.com>
Date: Wed, 06 Aug 2003 10:44:28 GMT
Message-ID: <3F30E0D2.5080106@magicinterface.com>

Billy Verreynne wrote:
> ed.prochak_at_magicinterface.com (Ed prochak) wrote
>
>

>>Billy, 
>>
>>when testing for performance, don't use COUNT(). Make the query fetch
>>rows. (COUNT() can be optimised such that no rows are fetched, making
>>the query SEEM fast.)

>
>
> Yes Ed. I also did test that with a 'SELECT *'. It was also
> sub-second. Should have mentioned it.
>
> In fact, I usually do as you suggest, however I then wrap the 'SELECT
> *' as a sub-select into a 'SELECT count(*)'.
>
> As for the COUNT(*) - the only thing that I understand is eliminated,
> is the transfer of the actual data to you. The only overhead added is
> Oracle counting the rows. Thus it is IMO a fair technique to use to
> test the basic performance of the query (FIRST_ROWS aside) when not
> taking network traffic into consideration. I often use it when dealing
> with large volume reports (aka detailed reports) - something which I
> btw find very illogical. A report of more than 100 lines ceases to
> provide information and turns into meaningless data.
>
> --
> Billy

My understanding is that the optimizer can decide that if it can answer COUNT(*) from table statistics, then it will do so. I hadn't thought about using it as a wrapper around the actual query. In that case you are right, cause there's no statistics for the optimizer to look at.

Thanks for point that out to me.

-- 
Edward J. Prochak   --- Magic Interface, Ltd.
Ofc: 440-498-3700   --- 7295 Popham Place, Solon, OH 44139
on the web at       --- http://www.magicinterface.com
email: ed.prochak_at_magicinterface.com

Received on Wed Aug 06 2003 - 05:44:28 CDT