Home » RDBMS Server » Performance Tuning » How to improve a group by sql stmt (merged)
How to improve a group by sql stmt (merged) [message #279308] Wed, 07 November 2007 13:17 Go to next message
dasgupta.amitava@gmail
Messages: 32
Registered: November 2007
Member
Hi,
I have a qry of the following type (with more SUM clause), running on a table with 1.5 Lac data:

Select XMLElement("Data", XMLAttributes(X, 
SUM(Case When Band < 0 Then 1 Else 0 End ) As "B1",
SUM(Case When Band Between 0 and 2 Then 1 Else 0 End ) As "B2",
SUM(Case When Band Between 2 and 6 Then 1 Else 0 End ) As "B3",
SUM(Case When Band Between 6 and 12 Then 1 Else 0 End ) As "B4")).getStringVal() 
From (Select MKT As X, Id, (Select Count(1) From Exp.HOLIDAY
                Where DT Between Start_DT AND sysdate)) As Band 
      From Exp.TAB1
      Where TYPE_NUM IN (3, 10, 14, 15, 16) 
             AND MKT IN (0, 1, 2, 3, 4)
      )
Group By X;


The table TAB1 has index on TYPE_NUM and MKT and Id column. It is taking around 25 secs to run. Can anybody suggest me a different way of forming the same qry????

Thanks in advance
Amitava............

mod-edit: color removed, code tags added

[Updated on: Wed, 07 November 2007 14:06] by Moderator

Report message to a moderator

Slow Group By Query... [message #279348 is a reply to message #279308] Wed, 07 November 2007 20:07 Go to previous messageGo to next message
dasgupta.amitava@gmail
Messages: 32
Registered: November 2007
Member
Hi,
I have a qry of the following type (with more SUM clause), running on a table with 1.5 Lac data:

Select XMLElement("Data", XMLAttributes(X,
SUM(Case When Band < 0 Then 1 Else 0 End ) As "B1",
SUM(Case When Band Between 0 and 2 Then 1 Else 0 End ) As "B2",
SUM(Case When Band Between 2 and 6 Then 1 Else 0 End ) As "B3",
SUM(Case When Band Between 6 and 12 Then 1 Else 0 End ) As "B4")).getStringVal()
From (Select MKT As X, Id, (Select Count(1) From Exp.HOLIDAY
Where DT Between Start_DT AND sysdate)) As Band
From Exp.TAB1
Where TYPE_NUM IN (3, 10, 14, 15, 16)
AND MKT IN (0, 1, 2, 3, 4)
)
Group By X;

The table TAB1 has index on TYPE_NUM and MKT and Id column. It is taking around 25 secs to run. Can anybody suggest me a different way of forming the same qry????

Thanks in advance
Amitava............
Re: Slow Group By Query... [message #279349 is a reply to message #279348] Wed, 07 November 2007 20:27 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Get rid of the scalar sub-query from the SELECT clause and merge it into the FROM clause as a join.

Ross Leishman
Re: Slow Group By Query... [message #279449 is a reply to message #279349] Thu, 08 November 2007 08:23 Go to previous messageGo to next message
dasgupta.amitava@gmail
Messages: 32
Registered: November 2007
Member
Thanks Ross,
for your reply. But sorry could you please re-write the same qry as you as saying, 'coz I can't get how you are saying to join in the from clause....

Thanks again
Amitava..........
Re: Slow Group By Query... [message #279452 is a reply to message #279449] Thu, 08 November 2007 09:26 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
This time I will help you. But from next time you should not say i don't know. Try try try until you succeed. That's the only you learn a lot. Google it or search in oracle documentation. There are loads of examples there. After all these efforts if you are stuck put what you have tried and we will try to help you out. Rather than spoon-feeding like what i am doing now.

SQL> select deptno, dname, loc, (select count(*) from emp where hiredate < sysdate) count from dept;


    DEPTNO DNAME          LOC                COUNT
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK              14
        20 RESEARCH       DALLAS                14
        30 SALES          CHICAGO               14
        40 OPERATIONS     BOSTON                14

SQL> select deptno, dname, loc, cnt from dept, (select count(*) cnt from emp where hiredate < sysdate) dept;

    DEPTNO DNAME          LOC                  CNT
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK              14
        20 RESEARCH       DALLAS                14
        30 SALES          CHICAGO               14
        40 OPERATIONS     BOSTON                14


Still i am not able to understand what you are trying to achieve ?

Regards

Raj

[Updated on: Fri, 09 November 2007 04:36]

Report message to a moderator

Previous Topic: INDEX CREATION
Next Topic: Index overhead
Goto Forum:
  


Current Time: Fri Dec 02 14:28:37 CST 2016

Total time taken to generate the page: 0.17458 seconds