Home » RDBMS Server » Performance Tuning » Raplace Group by clause (Oracle 9.2, Windows)
icon6.gif  Raplace Group by clause [message #443922] Thu, 18 February 2010 00:41 Go to next message
visuorac
Messages: 10
Registered: February 2010
Location: chennai
Junior Member
Hi, Laughing

I used Count(*) function in my query .So i need to put groupby clause for every selected fields.I want to replace group by clause because performance issue.Is there any other way to replace group by clause and put count(*) function.

For ex:


SELECT

/*+PARALLEL(a,4)*/

a.uabscon_prem_code,
a.uabscon_cust_code,
a.uabscon_cancellation_date,
COUNT(*) NumApps ------------------------Aggrecate function
FROM
uimsmgr.uabscon a,
uimsmgr.uarserq b,
(
SELECT
/*+PARALLEL(b,4)*/
b.utrjapp_srvc_code,
b.utrjapp_styp_code,
b.utrjapp_visit_months,
b.utrjapp_li_ind,
c.utvsrvc_guarantee
FROM
uimsmgr.utrjapp b,
uimsmgr.utvsrvc c
WHERE
c.utvsrvc_bus_sector_id = 1
AND b.utrjapp_srvc_code = c.utvsrvc_code
AND b.utrjapp_visit_months <> 0
AND rownum<100
) japp1
WHERE
b.uarserq_cust_code = a.uabscon_cust_code
AND b.uarserq_prem_code = a.uabscon_prem_code
AND a.uabscon_status_ind = 'C'
AND a.uabscon_cancellation_date BETWEEN
TO_DATE('01-FEB-2009')
AND TO_DATE('31-JAN-2010'||' 23:59:59','DD-MON-YYYY HH24:MI:SS')
AND b.uarserq_srvc_code = japp1.utrjapp_srvc_code
AND b.uarserq_styp_code = japp1.utrjapp_styp_code
AND b.uarserq_serv_num > 0
-- No other active CONTRACT at the PREMISES
AND NOT EXISTS (
SELECT
'X'
FROM
uimsmgr.uabscon f
WHERE
f.uabscon_prem_code = a.uabscon_prem_code
AND f.uabscon_status_ind != 'P'
AND f.uabscon_status_ind != 'C'
AND rownum<100
)
-- Most recent quote
AND b.uarserq_quote_number = (
SELECT MAX(t.uabletq_quote_number)
FROM
uimsmgr.uabletq t
WHERE
t.uabletq_cust_code = a.uabscon_cust_code
AND t.uabletq_prem_code = a.uabscon_prem_code
AND rownum<100
)
AND rownum<100
GROUP BY
a.uabscon_prem_code,---------------------group by clause.
a.uabscon_cust_code,
a.uabscon_cancellation_date
Re: Raplace Group by clause [message #443926 is a reply to message #443922] Thu, 18 February 2010 00:45 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Raplace Group by clause [message #443937 is a reply to message #443922] Thu, 18 February 2010 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Very funny Laughing

Regards
Michel
Re: Raplace Group by clause [message #443983 is a reply to message #443937] Thu, 18 February 2010 03:50 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
@ Michel
Is it like Tit for Tat? your smiley indicates like that.

sriram Smile
Re: Raplace Group by clause [message #443985 is a reply to message #443983] Thu, 18 February 2010 04:03 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just to make him understand that a smiley is not just a picture it has a meaning and putting it in inappropriate place is like my answer: (put here the adjective you want).

It seems there is a new fashion here to put smileys everywhere above all when they are irrelevant (I precise this is not the case of yours in your signature).

Regards
Michel
Previous Topic: Pls tell me, what I need to check before tuning the SQL code?
Next Topic: 11g Result Cache
Goto Forum:
  


Current Time: Sun Dec 11 06:28:20 CST 2016

Total time taken to generate the page: 0.12977 seconds