SQL Group By - Tooooo Slow

From: Michael R <michael_rothwell_at_non-hp-usa-om46.om.hp.com>
Date: 1997/05/13
Message-ID: <5la56i$ikb1@hpcc883.corp.hp.com>#1/1

I have the following SQL statement that is taking over a minute to execute. The three tables involved are quite small, and they are indexed properly. Can anyone out there help me pick up the speed????

select a.col1,a.col2,

       sum(nvl(b.col2,0) - nvl(c.col2,0) +
           nvl(b.col3,0) - nvl(c.col3,0) ) total,
       sum(nvl(b.col2,0) - nvl(c.col2,0)) sub_total2,
       sum(nvl(b.col3,0) - nvl(c.col3,0)) sub_total3

from tableA a, tableB b, tableC c

where  a.col3 = 'XXXX'
  and  b.col1 = a.col4
  and  c.col1 = a.col4

group by a.col1, a.col2;

I have included hints in this statement to force the use of the proper indexes. According to the execution query plan, it is using the indexes and not performing any full table scans.

Any help would be greatly appreciated.

Thanks

Michael. Received on Tue May 13 1997 - 00:00:00 CDT