"GROUP BY" really slowing down query.

From: David Hiltz <dhiltz_at_whsun1.whoi.edu>
Date: Tue, 6 Dec 1994 21:42:48 GMT
Message-ID: <D0EsBD.8JG_at_netnews.whoi.edu>

   I'm doing a select statement between two tables:

        TAB_A =  60,000 rows
        TAB_B = 400,000 rows

   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;

    All columns involved in the query are indexed. I moved things around     in the query, but nothing seems to work.

    I'm running this on an Indigo (IRIX 5.2) with 96Megs of memory, Oracle     7.0.15.

    I've set the SORT_AREA_SIZE to 15Megs.

    The TEMP tablespace is setup as:

       TBLSPC_NAME INIT_EXT NEXT_EXT MIN_EXT MAX_EXT PCT_INC
       ----------- -------- -------- ------- ------- -------
       TEMP          262144   262144       1     249       0

    These should be plenty big for what I'm trying to do.

    Any ideas?

    Is simply that it takes a lot of time to sort through all those records?

    Thanks for any help.

---

    David Hiltz
    dhiltz_at_whsun1.wh.whoi.edu
    Network System Administrator
    Northeast Fisheries Science Center
    ######&_at_&###### Received on Tue Dec 06 1994 - 22:42:48 CET