Re: "GROUP BY" really slowing down query.

In article <3c6htf$c1d_at_horus.mch.sni.de> Jan-Peter Meyer <jan-peter.meyer_at_csg.de> writes:
>From: Jan-Peter Meyer <jan-peter.meyer_at_csg.de>
>Subject: Re: "GROUP BY" really slowing down query.
>Date: 8 Dec 1994 08:59:27 GMT
 

>dhiltz_at_whsun1.whoi.edu (David Hiltz) wrote:
>> And whenever I add the "group by" clause, that you see
>> below, it slows the query down to nothing. Without the
>> "group by" clause the answer comes back in a few seconds.
   

>> select TAB_A.year, TAB_A.lat, TAB_A.lon,
>> TO_CHAR (sysdate,'fmdd-Mon-yy') Today
>> from TAB_B,TAB_A
>> where TAB_A.area = 513 or TAB_A.area = 514
>> and TAB_B.month = TAB_A.month
>> and TAB_B.docn = TAB_A.docn
>> group by TAB_A.year, TAB_A.lat, TAB_A.lon;
 

>It seems to me that you are just trying to get the output
>unique, as you are not doing a "sum" or such operation on the
>group. Have you tried a SELECT DISTINCT instead of the
>GROUP BY apporoach?
 

>Good luck!

There are several areas in this query that would improve performanc. First, if you use DISTINCT you will force a full table scan. This would happen twice because you are using an OR in your where clause.

You shoud get much better results if you have an index on table A area and table B Month or docno. You migh also consider a composit index on month and docno. The order of colums would best be dictate by the most common query access to the table.

Assuming you have the indices above I would struct the query as follows:

SELECT a.year, a.lat, a.lon, TO_CHAR( SYSDATE, 'fmdd-Mon-yy') Today

     FROM TAB_A a, TAB_B b
     WHERE a.area IN ( 513, 514 ) AND
             a.docn = b.docn AND
             a.month = b.month

The execution plan for this query should use the index on the are to filter out all the records in areas 513 & 514. These records will then be joined to the b table by an index on docn (i assume the docn would be a more selective index ) and filterd on the month ( or joind to the month if it is included in the index) The result set will then be sorted to satisfy the group clause.

I assume that what you really wanted was all the records in are 513 & 514 join to table b. I am not shure that is what you will get with your orignal query. Because the binding of AND is greater than OR i beleve that you should have gotton all the records in area 513 and only the record from area 514 joind to table b.

Good luck. Let me know if this works.

APS (sorry for spelling errors. If you know of any windows base news readers that include a spelling checker I will be forever in your debt.)

>jan-peter.meyer_at_scg.de
  Received on Thu Dec 08 1994 - 13:42:39 CET