Re: How to optimize this query?

From: Ed prochak <ed.prochak_at_magicinterface.com>
Date: 7 Aug 2003 13:22:22 -0700
Message-ID: <4b5394b2.0308071222.3a084a4c@posting.google.com>

Andy Hassall <andy_at_andyh.co.uk> wrote in message news:<1223jvomkc43md42gi5rem94mk32uvltkp_at_4ax.com>...
> On Wed, 06 Aug 2003 10:44:28 GMT, "Edward J. Prochak"
> <ed.prochak_at_magicinterface.com> wrote:
>
> >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.
>
> But optimiser statistics (user_tables.num_rows) cannot be used as the basis
> for COUNT(*), since they are only refreshed periodically; so after the first
> insert/delete after gathering stats, it would give the wrong answer.
>
> It's going to have to scan _something_; most likely an index.

If you run performance test of queries in a production environment, then your stats will go "out of date" quickly. Lots of us do our performance testing on production instances right? 8^)

You only have to get burned ONCE to decide to play it safe. Besides transferring the data to the client can be a significant portion of query execution time in some instances (report generation for example).

And I thing the COUNT(*) wrapper suggestion is a great alternative. So, we have three ways:

option 1 Original query under test

        select <columns>
           from <tables>
           where <conditions/joins>;

is what I would always use for testing.

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.

option 3 wrapper query (embedding the original query as a view)

    select count(*)

     from ( select count(*)
            from <tables>
           where <conditions/joins> ) ;

seems to be a great alternative to option 1 and avoids issues of option 2

in Option 3 it should be easy to sort out the extra work done for the count(*) from the explain plan and the trace should have reasonable info on data moves too (except for the actual data transfer to the client process). It looks like a win-win here. Received on Thu Aug 07 2003 - 15:22:22 CDT