Home » RDBMS Server » Performance Tuning » One query with two where Conditions on same column (Oracle 10g)
One query with two where Conditions on same column [message #377486] Tue, 23 December 2008 04:03 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have to write a query with two different where condition on same table something as below:-

Select col1,col2,
(SELECT USERNAME FROM USER_HD WHERE USERID = A.LOGINAUTHUSERID) Verified_By,       
 (SELECT USERNAME FROM USER_HD WHERE USERID = A.CLEXECUSERID) Exec_By
FROM CLLOG A 


The above query is wrong ...just wanted to show as what is needed in the query....kindly tell me a way to display the 4 columns col1,col2,Verified_By and Exec_By.

Regards,
Mahi
Re: One query with two where Conditions on same column [message #377489 is a reply to message #377486] Tue, 23 December 2008 04:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why is that wrong? It's vertainly one way of doing it.Another way would be to join the CLLOG table to USER_HD twice, and add lines in the WHERE clause:
SELECT <columns>
      ,UH1.USERNAME  verified_by
      ,UH2.USERNAME  exec_by
FROM CLLOG   A
    ,USER_HD UH1
    ,USER_HD UH2
WHERE UH1.USERID = A.LOGINAUTHUSERID
AND   UH2.USERID = A.CLEXECUSERID
Re: One query with two where Conditions on same column [message #377504 is a reply to message #377489] Tue, 23 December 2008 04:59 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Actually I have a big query where this would only be a part of it and I want the query to run fast.
SELECT P.CityCode City_Code,
	CASE B.CALLTYPE WHEN 'CSH'
	THEN '-P'
	ELSE B.CALLTYPE END Call_Type, 
	A.CLNATURE B_R, 
	A.CLCALLNO Call_Id, 
	A.CLEXPCLTM TAT_Time, P.CustBrCode Branch_Code,  
	CASE B.CALLTYPE WHEN 'ATMREPL' THEN A.CLATMID ELSE ABC.CUSTCUSTNAME END Client_Name,  
	CASE B.CALLTYPE WHEN 'ATMREPL' THEN ISNULL(PQR.LOCDESC,ABC.LOCDESC) ELSE ABC.LOCDESC END_Area,  
	ISNULL(A.CLAMT,0) Amount_INR,  
	CASE 	WHEN A.CLCALLSTATUS = 'OP' THEN 0 
		WHEN A.CLCALLSTATUS = 'AS' THEN 0 
		ELSE  
		CASE B.CALLTYPE+A.CLCALLACTION 
			WHEN 'CSHPKP' THEN (ISNULL(A.CLAMT,0) + ISNULL(A.CLDIFFAMT,0)) 
			WHEN 'CSHCHQPKP' THEN (ISNULL(A.CLAMT,0) + ISNULL(A.CLDIFFAMT,0)) 
			WHEN 'CSHDLY' THEN (ISNULL(A.CLAMT,0) - ISNULL(A.CLDIFFAMT,0))  
			WHEN 'ATMREPLATM' THEN (ISNULL(A.CLAMT,0) + ISNULL(A.CLDIFFAMT,0)) 
			WHEN 'CSHWBNKPKP' THEN (ISNULL(A.CLAMT,0) + ISNULL(A.CLDIFFAMT,0)) 
			WHEN 'CSHDBNKDLY' THEN (ISNULL(A.CLAMT,0) - ISNULL(A.CLDIFFAMT,0)) 
			ELSE 0 
			END  
		END Actual_Amt,  
	ISNULL(A.CLDIFFAMT,0) Diff_Amt,  X.RtCode Route_Code, A.REQSLNO Ack_No, 
	CASE A.CLCALLSTATUS 	WHEN 'OP' THEN 'Open' 
				WHEN 'AS' THEN 'Assigned' 
				WHEN 'AT'THEN 'Attended' 
				WHEN 'SK' THEN 'Skipped'  
				WHEN 'CN' THEN 'Cancelled' 
				WHEN 'CL' THEN 'Closed' 
				END Call_Status, 
	A.LOGINAUTHUSERID Auth1, 
	B.CALLTYPE Call Type_Code, 
	CONVERT(CHAR(10),A.CLGENDATE ,'+ @SQLDateFormat +') Gen_Date,  
	A.CLCALLSTATUS Status, 
	A.CLCALLACTION Call_Action, 
	ABC.CUSTCUSTOMERCODE Client_Code, 
	A.CLBNKLGNO Bank_Login, 
	A.CLBNKFXLGTM Fax_Time, 
	A.CLNOOFCHQS No_Of_Chqs, 
	A.CLCHQNO Chq No,A.CLDDTCNO DDTC_No, 
	A.CLDDTCAMT DDTC_Amt,  
	(SELECT USERNAME FROM USER_HD WHERE USER_HD.USERID = A.LOGINAUTHUSERID) Verified_By, 
	(SELECT USERNAME FROM USER_HD WHERE USER_HD.USERID = A.CLEXECUSERID) Exec_By, 
	A.CLGENTIME Gen_Tm,P.CUSTCODE Bank_Code, 
	A.CLATMID ATM_ID, PQR.LOCDESC ATM_Area, 
	A.CLCALLSTATUS Status_Of_Call, 
	B.CALLTYPE Type_of_Call  , 
	CASE WHEN XYZ.vn_cnt > 0 THEN 'True' 
		else 'False' 
	END MultiAcs, 
	CASE WHEN XYZ.vn_cnt > 0 THEN 'True' 
	else 'False' 
	END MultiAcYN  , 
	A.clOFFCD OFF_Cd FROM CLLOG A  
	INNER JOIN USER_CLTYPE B ON (A.CLCALLTYPE=B.CALLTYPE ) 
	INNER JOIN CALLMASTER C ON (B.CALLNAME=C.CALLNAME AND A.CLCALLTYPE=C.CALLTYPE AND A.CLCALLACTION = C.CALLACTION)  
	LEFT OUTER JOIN (
			SELECT T.ATMID,T.ATMLOCCD,ATMCOMPCD,ATMOFFCD,LOCDESC 
			FROM MATM T
			INNER JOIN MLOCATION K ON T.ATMLOCCD=K.LOCCODE AND T.ATMOFFCD=K.OFFCODE AND T.ATMCITY=K.CITYCODE 
			)PQR  ON ( A.CLATMID=PQR.ATMID AND A.COMPCODE=PQR.ATMCOMPCD AND A.CLOFFCD=PQR.ATMOFFCD) 
	LEFT OUTER JOIN (
			SELECT CUSTCODE,CUSTBRCODE,CUSTCUSTOMERCODE,COMPCODE,E.OFFCODE,E.CITYCODE,E.LOCCODE,LOCDESC,
			CUSTCUSTNAME,CALLTYPECODE 
			FROM MCUSTCUSTOMER E
			INNER JOIN MLOCATION G ON E.LOCCODE =G.LOCCODE AND E.OFFCODE=G.OFFCODE AND E.CITYCODE=G.CITYCODE 
			)ABC ON ( A.CLCUSTCD=  ABC.CUSTCODE AND  A.CLCUSTBRCD=ABC.CUSTBRCODE AND A.COMPCODE=ABC.COMPCODE AND A.CLOFFCD=ABC.OFFCODE  AND A.CLCUSTCUSTCD=ABC.CUSTCUSTOMERCODE)  
	INNER JOIN (
			SELECT F.CITYCODE, F.CITYDESC,CUSTBRNAME,CUSTCODE,CUSTBRCODE,COMPCODE,OFFCODE 
			FROM MCUSTOMERBRANCH D 
			INNER JOIN MCITY F ON D.CITYCODE=F.CITYCODE
		)P  ON (A.CLCUSTCD=P.CUSTCODE AND A.CLCUSTBRCD=P.CUSTBRCODE AND A.COMPCODE=P.COMPCODE AND A.CLOFFCD=P.OFFCODE)
	LEFT OUTER JOIN (
			SELECT COUNT(*) VN_CNT, CUSTCUSTOMERCODE FROM MCUSTCUSTACCOUNT 
			GROUP BY CUSTCUSTOMERCODE 
			) XYZ on XYZ.custcustomercode =ABC.CUSTCUSTOMERCODE 
	LEFT OUTER JOIN (
			SELECT R.RTCODE,Q.FIX,Q.CLCALLNO,Q.CLACTCD,Q.CLCUSTCD,Q.CLCUSTBRCD,Q.CLOFFCD,Q.COMPCODE,
			Q.CLGENDATE,Q.CLCUSTCUSTCD FROM CLEXEC Q
			INNER JOIN MROUTE R ON Q.RTCODE=R.RTCODE AND Q.COMPCODE=R.COMPCODE AND Q.CLOFFCD=R.OFFCODE
			)X ON (A.CLCALLNO=X.CLCALLNO AND A.CLACTCD=X.CLACTCD  AND A.CLCUSTCD=X.CLCUSTCD 
			AND A.CLCUSTBRCD=X.CLCUSTBRCD AND  A.CLOFFCD=X.CLOFFCD 
			AND A.COMPCODE=X.COMPCODE AND A.CLGENDATE=X.CLGENDATE 
			AND (A.CLCUSTCUSTCD=X.CLCUSTCUSTCD OR A.CLCUSTCUSTCD IS NULL)
	) 
	WHERE A.COMPCODE=CompCode 
	AND B.USERID=UserID  
	AND P.CITYCODE IN (CityCode) 
	AND B.CALLNAME IN (CallTypeCode) AND A.CLCUSTBRCD IN (BankCode)
	AND A.CLNATURE IN(Frequency) AND A.CLCALLSTATUS IN (CallStatus) 
	AND RouteCode IS NOT NULL 
	AND A.CLCALLSTATUS <> 'CL' 
	AND A.ClOffCd IN (ClOffCd)
	AND A.ClCustCd IN (ClCustCd)
	ORDER BY CLACTDATE, B.CALLNAME, A.CLCALLNO


The whole query is taking around 1 minute and its on an online system. When many users get online together, it becomes very slow. Please ive some hints where changes can be done to make it run faster.

Regards,
Mahi
Re: One query with two where Conditions on same column [message #377523 is a reply to message #377504] Tue, 23 December 2008 05:36 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
See the section on Correlated sub-query expressions in this article http://www.orafaq.com/node/1981

Ross Leishman
Re: One query with two where Conditions on same column [message #377524 is a reply to message #377504] Tue, 23 December 2008 05:38 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
How about reading the very first post in this section and post the details which are requested in that thread.

Regards

Raj
Re: One query with two where Conditions on same column [message #377526 is a reply to message #377523] Tue, 23 December 2008 05:48 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
There are many inner queries .... many joins....I have been given the task to optimize this query. Please give me some hints as where should I start on this query. Also it seems very complex to me as I am a beginner in tuning. The joins are making me afraid.
Please give me a start head....
Thanks for looking into this.

Regards,
Mahi
Re: One query with two where Conditions on same column [message #377678 is a reply to message #377526] Wed, 24 December 2008 03:20 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
I removed the two subquerys
(SELECT USERNAME FROM USER_HD WHERE USER_HD.USERID = A.LOGINAUTHUSERID) Verified_By, 
	(SELECT USERNAME FROM USER_HD WHERE USER_HD.USERID = A.CLEXECUSERID) Exec_By,

into left outer join as
A.clOFFCD [OFF Cd] FROM CLLOG A 
 LEFT OUTER JOIN USER_HD UH1 ON UH1.USERID = A.LOGINAUTHUSERID
  LEFT OUTER JOIN USER_HD UH2 ON UH2.USERID = A.CLEXECUSERID


Also thinking of creating views for subqueries XYZ, ABC and PQR.
Will converting subqueries like these to views is a good idea?
Please advice and help me this.

Regards,
Mahi
Re: One query with two where Conditions on same column [message #377687 is a reply to message #377678] Wed, 24 December 2008 04:53 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Sorry to be blunt.

It's no point rewriting the query without understanding what it is doing and where it is spending most of its time. To understand this we need to see the explain plan and the tkprof output of the query. Rewriting the query may seem to work for few cases. Either it could be the permanent solution or a temporary solution I don't know ? But that's not the way to approach a problem.

So I repeat again please read the very first post in this section and post what is required to analyse a Performance Issue.

Hope this helps.

Regards

Raj
Re: One query with two where Conditions on same column [message #378157 is a reply to message #377687] Mon, 29 December 2008 03:09 Go to previous message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I am thinking of making temporary tables for the inner queries used in the big query.
I found that the table 'CLEXEC' used in the inner query has 12 lakh records and to fetch only this table takes around 1 minute.
LEFT OUTER JOIN (
			SELECT R.RTCODE,Q.FIX,Q.CLCALLNO,Q.CLACTCD,Q.CLCUSTCD,Q.CLCUSTBRCD,Q.CLOFFCD,Q.COMPCODE,Q.CLGENDATE,Q.CLCUSTCUSTCD FROM CLEXEC Q
INNER JOIN MROUTE R ON Q.RTCODE=R.RTCODE AND Q.COMPCODE=R.COMPCODE AND Q.CLOFFCD=R.OFFCODE
			)X

Even if I run the simple select statement seperately that is
SELECT Q.FIX,Q.CLCALLNO,Q.CLACTCD,Q.CLCUSTCD,Q.CLCUSTBRCD,
Q.CLOFFCD,Q.COMPCODE,Q.CLGENDATE,Q.CLCUSTCUSTCD FROM CLEXEC Q

It takes around 1 minute 10 seconds to show 12 lakh records.
There is a clustered composite index on 6-7 columns.
Please advice what to do to make the selection faster when there are so many records.
I was planning to create a temporary table for the inner query 'X'. But inserting lakh records into temp table and then creating clustered index on temp table alone will take long time. I am confused as what to do.

Please advice me on this.

Thanks again for looking into this.
Mahi
Previous Topic: performance of delete/insert deteriorate over time
Next Topic: Thread 1 cannot allocate new log
Goto Forum:
  


Current Time: Tue Dec 06 04:51:34 CST 2016

Total time taken to generate the page: 0.26439 seconds