Home » RDBMS Server » Performance Tuning » How to reduce cost of my query? (sql developer)
How to reduce cost of my query? [message #534129] Sat, 03 December 2011 00:27 Go to next message
Naga_naga_naga
Messages: 45
Registered: August 2011
Location: bangalore
Member
Hi Friends,

I have developed my query according to by business requirement,
but it is taking more to execution.
any one suggest me on this how to reduce cost of this query?

WITH A AS
  (SELECT Cii.Instance_Status_Id Ib_Number,
    Cis.Name Ib_Status,
    Okhb.Contract_Number CONTRACTNO,
    Okhb.Contract_Number_Modifier Contractmodifier,
    Hp.Party_Number Contractpartyno,
    Hp.Party_Name Contractpartyname
  FROM csi.Csi_Item_Instances Cii,
    Csi.Csi_Instance_Statuses Cis,
    csi.csi_i_parties cip,
    Hz_Parties Hp,
    Okc.Okc_K_Items Oki,
    Okc.Okc_K_Lines_B Oklb,
    Okc.Okc_K_Headers_B Okhb,
    okc.Okc_K_Party_Roles_B Okprb
  WHERE Hp.Party_Id         =Cip.Party_Id
  AND Cip.Instance_Id       =Cii.Instance_Id
  AND Cii.Instance_Status_Id=Cis.Instance_Status_Id
  AND Cii.Instance_Id       =Oki.Object1_Id1
  AND Oki.Cle_Id            =Oklb.Id
  AND Oklb.Dnz_Chr_Id       =Okhb.Id
  AND Okhb.Id               =Okprb.Dnz_Chr_Id
  AND Okprb.Object1_Id1     =Hp.Party_Id
  AND Okhb.Sts_Code NOT    IN('ENTERED','CANCELLED','TERMINATED')
  And Okprb.Rle_Code        ='CUSTOMER'
  --And Okhb.End_Date in (Select Max(End_Date) From Okc.Okc_K_Headers_B)
   ),
  B AS
  (SELECT Hp1.Party_Number Ibowner1,
    Hp1.Party_Name Ibowner2,
    Hca.Account_Number Ibowner3,
    Hca.ACCOUNT_NAME IBOWNER4,
    Hl.ADDRESS1 ADDRESS
  FROM HZ_PARTIES HP1,
    Hz_Cust_Accounts Hca,
    Hz_Locations Hl,
    Hz_Party_Sites Hps
  WHERE Hp1.Party_Id =Hca.Party_Id
  AND Hp1.Party_Id   =Hps.Party_Id
  AND Hps.Location_Id=Hl.Location_Id
  ),
  C AS
  (SELECT Hp3.Party_Number Originalibownerpartyno,
    Hp3.Party_Name Originalibownerpartyname,
    Hca1.Account_Name Originalibowneracctname,
    hca1.account_number Originalibowneracctno
  FROM Hz_Parties Hp3,
    Hz_Cust_Accounts Hca1,
    Csi.Csi_I_Parties Cip1,
    Csi.Csi_Item_Instances Cii1,
    CSI.Csi_Item_Instances_H Ciih
  WHERE Ciih.Instance_History_Id =Cii1.Instance_Id
  AND Cii1.Instance_Id           =Cip1.Instance_Id
  AND Cip1.Party_Id              =Hp3.Party_Id
  AND Hp3.Party_Id               =Hca1.Party_Id
  AND Cip1.Relationship_Type_Code='OWNER'
    )
SELECT Aa.Ib_Number,
  Aa.Ib_Status,
  Aa.CONTRACTNO,
  Aa.Contractmodifier,
  Aa.Contractpartyno,
  Aa.Contractpartyname, 
  Bb.Ibowner1,
  Bb.Ibowner2,
  Bb.Ibowner3,
  Bb.IBOWNER4,
  Bb.Address,
  Cc.Originalibownerpartyno,
  Cc.Originalibownerpartyname,
  Cc.Originalibowneracctname,
  Cc.Originalibowneracctno
FROM A Aa,
  B Bb,
  C Cc
WHERE Ibowner3 IN(590476,16602,1570260,677709,690443,607679,589563,613589,730529,
743266,666079,3469260,584829,708846,727584,1633010,670079,747694,
661430,741094,759191)

[Updated on: Sat, 03 December 2011 00:41] by Moderator

Report message to a moderator

Re: How to reduce cost of my query? [message #534130 is a reply to message #534129] Sat, 03 December 2011 00:29 Go to previous messageGo to next message
Naga_naga_naga
Messages: 45
Registered: August 2011
Location: bangalore
Member
Thanks in advance...

Regards,
Raju
Re: How to reduce cost of my query? [message #534136 is a reply to message #534130] Sat, 03 December 2011 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For any peformances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel
Re: How to reduce cost of my query? [message #534138 is a reply to message #534129] Sat, 03 December 2011 01:16 Go to previous message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
You should make your own investigation as nobody here has access to your system.
You may start with detecting, how many rows does each subquery (A, B, C) return and in what time. Also move the main WHERE condition to B subquery and re-check.

Do you really want the cartesian product of those subqueries? Because, the resultset will contain all combinations of those three resulsets, which may be quite big number. Then, you probably have to put up with the fact that its generating will take some time.
Previous Topic: urgent please modify this query frenz sloww performance
Next Topic: How to improve the performance of export job(expdp)?
Goto Forum:
  


Current Time: Thu Mar 28 10:10:38 CDT 2024