Re: How to optimize this query?

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