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 Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #377526 is a reply to message #377523] |
Tue, 23 December 2008 05:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 Go to previous message](/forum/theme/orafaq/images/up.png) |
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
|
|
|
Goto Forum:
Current Time: Mon Sep 09 21:32:20 CDT 2024
|