Home » RDBMS Server » Performance Tuning » Group by clause
Group by clause [message #291108] Thu, 03 January 2008 00:57 Go to next message
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 Go to previous message
Michel Cadot
Messages: 64103
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
Previous Topic: latch: session allocation and CPU
Next Topic: Update Statement is too slow
Goto Forum:
  


Current Time: Sat Dec 03 12:16:10 CST 2016

Total time taken to generate the page: 0.11510 seconds