OraFAQ Forum: Performance Tuning » How to improve a group by sql stmt (merged)

Home » RDBMS Server » Performance Tuning » How to improve a group by sql stmt (merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to improve a group by sql stmt (merged) [message #279308]

Wed, 07 November 2007 13:17

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

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............

Report message to a moderator

Re: Slow Group By Query... [message #279349 is a reply to message #279348]

Wed, 07 November 2007 20:27

rleishman
Messages: 3728
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

Report message to a moderator

Re: Slow Group By Query... [message #279449 is a reply to message #279349]

Thu, 08 November 2007 08:23

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..........

Report message to a moderator

Re: Slow Group By Query... [message #279452 is a reply to message #279449]

Thu, 08 November 2007 09:26

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 19:38:09 CST 2024