Home » SQL & PL/SQL » SQL & PL/SQL » How To optimize this complex Query
How To optimize this complex Query [message #235137] Fri, 04 May 2007 03:00 Go to next message
rahul_johari
Messages: 1
Registered: May 2007
Junior Member
Plz guide me how to optimize this query :

Update ODS_Subscriber_Header A
Set A.Active_Phone_Count =
(
Select Count(1)
From ODS_CIMS_PHONE_MASTER B , ODS_Product_Service_Headers C
Where B.Ph_Account_Num = A.Subh_Account_Num
And B.Psh_Skey = C.Psh_Skey
And B.PHONE_STATUS = 'U'
And C.Rev_Type_Ind = 1
And Exists
(Select 1
From ODS_CIMS_PHONE_MASTER D
Where B.Ph_Account_Num = D.Ph_Account_Num
And D.LOAD_ID = $G_LOAD_ID) b
)
Where Exists
(
Select 1
From ODS_CIMS_PHONE_MASTER B , ODS_Product_Service_Headers C
Where B.Ph_Account_Num = A.Subh_Account_Num
And B.Psh_Skey = C.Psh_Skey
And B.PHONE_STATUS = 'U'
And C.Rev_Type_Ind = 1
And Exists
(Select 1
From ODS_CIMS_PHONE_MASTER D
Where B.Ph_Account_Num = D.Ph_Account_Num
And D.LOAD_ID = $G_LOAD_ID)
)


1. In this query all the tables used, have more than 6 million records.

2. All the fields used in the Where Clause have been properly indexed.

3. This query is running forever.

Any suggestions will be helpful,

Thanks,
Rahul

Re: How To optimize this complex Query [message #235222 is a reply to message #235137] Fri, 04 May 2007 06:55 Go to previous message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Please provide the info described in Performance Tuning Sticky at http://www.orafaq.com/forum/t/51267/42504/
Previous Topic: Call Web Service (WSDL) in Oracle 8i
Next Topic: Temporary Tabelspace Issue (merged)
Goto Forum:
  


Current Time: Thu Dec 08 06:17:05 CST 2016

Total time taken to generate the page: 0.10496 seconds