OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Group by clause

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

Group by clause [message #291108]

Thu, 03 January 2008 00:57

deepak samal
Messages: 29
Registered: February 2005

Junior Member

HI,

I am using oracle 10g. i have written this below query which is taking a lot of time to execute in my database. here the cost is 32349 .

SELECT F.F_ID, 
COUNT(L.L_ID) LCOUNT, 
COUNT(P.P_ID) PCOUNT
FROM F 
LEFT JOIN L ON F.F_ID = L.F_ID
LEFT JOIN P ON F.F_ID = P.F_ID
GROUP BY F.F_ID

is there any alternate way of writting this query.
Any suggestions.

thanks
deepak

[Updated on: Thu, 03 January 2008 01:12] by Moderator

Report message to a moderator

Re: Group by clause [message #291113 is a reply to message #291108]

Thu, 03 January 2008 01:15

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

SELECT F.F_ID, LCOUNT, PCOUNT
FROM F,
     (select L.F_ID, count(*) lcount
      from l
      group by L.F_ID) l,
     (select P.F_ID, count(*) pcount
      from p
      group by P.F_ID) p
WHERE F.F_ID = L.F_ID
  and F.F_ID = P.F_ID

But it depends on the number of rows your query will return.

Regards
Michel

Report message to a moderator

Previous Topic:	latch: session allocation and CPU
Next Topic:	Update Statement is too slow

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 03 05:50:47 CDT 2024