PHILIP233
asked on
Query Tuning
SELECT DISTINCT BR.CUSTOMERCODE,
DECODE(CUS.CUSTOMERTYPENUM ,
1,
CUS.LASTNAME || ' ' || CUS.FIRSTNAME || ' ' ||
CUS.MIDDLENAME,
2,
CUS.COMPANYNAME,CUS.COMPAN YNAME) CUSTOMERNAME,
BR.BILLCURRENCYCODE CURRENCYCODE,
BR.FRANCHISECODE FRANCHISE,
DECODE(I.ITEMDESC, NULL, BR.VARIANTCODE, I.ITEMDESC) VARIANTDESCRIPTION,
BR.VIN VIN,
SN.MODELYEAR MY,
SUM(BRD.BILLSAMOUNTTC) OVER(PARTITION BY BRD.BILLSREGISTERID) SETAMOUNT,
SUM(BRD.INTERESTAMOUNTTC) OVER(PARTITION BY BRD.BILLSREGISTERID) INTAMT,
(SUM(BRD.BILLSAMOUNTTC)
OVER(PARTITION BY BRD.BILLSREGISTERID) +
SUM(BRD.INTERESTAMOUNTTC)
OVER(PARTITION BY BRD.BILLSREGISTERID)) SETNET,
BRD.BILLDUEDATE,
BR.CURRENTSETNUMBER SETNUMBER,
(SELECT SUM(BRD.BILLSAMOUNTTC)
FROM BILLSREGISTERDETAILS BRD1
WHERE BR.BILLSREGISTERID = BRD1.BILLSREGISTERID
AND (((BRD1.BILLDUEDATE <= '&PDATE') AND
(BRD1.SETTLEMENTDATE > '&PDATE' or
BRD1.SETTLEMENTDATE is null)))
GROUP BY BRD1.BILLSREGISTERID) OVERDUEAMOUNT,
(SELECT SUM(BRD2.BILLSAMOUNTTC)
FROM BILLSREGISTERDETAILS BRD2
INNER JOIN BILLSREGISTER BR2 ON BR2.BILLSREGISTERID =
BRD2.BILLSREGISTERID
AND ((BRD2.BILLDUEDATE >
'&PDATE' AND
(BRD2.SETTLEMENTDATE >
'&PDATE' OR
BRD2.SETTLEMENTDATE IS NULL)))
AND BR2.CURRENTSETDATE <=
'&PDATE'
WHERE BR2.BILLSREGISTERID = BR.BILLSREGISTERID
GROUP BY BRD2.BILLSREGISTERID) UNDUEAMOUNT,
TO_CHAR(BR.CURRENTSETDATE, 'DD-MON-YYYY') SETDATE,
(BR.TOTALLOANAMOUNT / BR.EXCHANGERATE) LOANAMOUNT,
TO_CHAR(MIN(BRD.BILLDUEDAT E)
OVER(PARTITION BY BRD.BILLSREGISTERID),
'DD-MON-YYYY') FIRSTDUEDATE,
TO_CHAR(MAX(BRD.BILLDUEDAT E)
OVER(PARTITION BY BRD.BILLSREGISTERID),
'DD-MON-YYYY') LASTDUEDATE,
BRD.BILLSAMOUNTTC,
TO_CHAR(BRD.SETTLEMENTDATE , 'MON,YYYY') PAYMENTDATE,
BRD.SETTLEMENTDATE BILLDUEDATE
FROM BILLSREGISTER BR
INNER JOIN BILLSREGISTERDETAILS BRD ON BR.BILLSREGISTERID = BRD.BILLSREGISTERID
INNER JOIN CUSTOMER CUS ON CUS.CUSTOMERCODE = BR.CUSTOMERCODE
LEFT OUTER JOIN ITEM I ON I.ITEMCODE = BR.VARIANTCODE
LEFT OUTER JOIN SERIALNUMBER SN ON SN.VIN = BR.VIN
WHERE BRD.BILLDUEDATE <= '&PDATE'
ORDER BY BRD.BILLDUEDATE
Kindly verigy the above query.
Kindly suggest something to improve the performance.
DECODE(CUS.CUSTOMERTYPENUM
1,
CUS.LASTNAME || ' ' || CUS.FIRSTNAME || ' ' ||
CUS.MIDDLENAME,
2,
CUS.COMPANYNAME,CUS.COMPAN
BR.BILLCURRENCYCODE CURRENCYCODE,
BR.FRANCHISECODE FRANCHISE,
DECODE(I.ITEMDESC, NULL, BR.VARIANTCODE, I.ITEMDESC) VARIANTDESCRIPTION,
BR.VIN VIN,
SN.MODELYEAR MY,
SUM(BRD.BILLSAMOUNTTC) OVER(PARTITION BY BRD.BILLSREGISTERID) SETAMOUNT,
SUM(BRD.INTERESTAMOUNTTC) OVER(PARTITION BY BRD.BILLSREGISTERID) INTAMT,
(SUM(BRD.BILLSAMOUNTTC)
OVER(PARTITION BY BRD.BILLSREGISTERID) +
SUM(BRD.INTERESTAMOUNTTC)
OVER(PARTITION BY BRD.BILLSREGISTERID)) SETNET,
BRD.BILLDUEDATE,
BR.CURRENTSETNUMBER SETNUMBER,
(SELECT SUM(BRD.BILLSAMOUNTTC)
FROM BILLSREGISTERDETAILS BRD1
WHERE BR.BILLSREGISTERID = BRD1.BILLSREGISTERID
AND (((BRD1.BILLDUEDATE <= '&PDATE') AND
(BRD1.SETTLEMENTDATE > '&PDATE' or
BRD1.SETTLEMENTDATE is null)))
GROUP BY BRD1.BILLSREGISTERID) OVERDUEAMOUNT,
(SELECT SUM(BRD2.BILLSAMOUNTTC)
FROM BILLSREGISTERDETAILS BRD2
INNER JOIN BILLSREGISTER BR2 ON BR2.BILLSREGISTERID =
BRD2.BILLSREGISTERID
AND ((BRD2.BILLDUEDATE >
'&PDATE' AND
(BRD2.SETTLEMENTDATE >
'&PDATE' OR
BRD2.SETTLEMENTDATE IS NULL)))
AND BR2.CURRENTSETDATE <=
'&PDATE'
WHERE BR2.BILLSREGISTERID = BR.BILLSREGISTERID
GROUP BY BRD2.BILLSREGISTERID) UNDUEAMOUNT,
TO_CHAR(BR.CURRENTSETDATE,
(BR.TOTALLOANAMOUNT / BR.EXCHANGERATE) LOANAMOUNT,
TO_CHAR(MIN(BRD.BILLDUEDAT
OVER(PARTITION BY BRD.BILLSREGISTERID),
'DD-MON-YYYY') FIRSTDUEDATE,
TO_CHAR(MAX(BRD.BILLDUEDAT
OVER(PARTITION BY BRD.BILLSREGISTERID),
'DD-MON-YYYY') LASTDUEDATE,
BRD.BILLSAMOUNTTC,
TO_CHAR(BRD.SETTLEMENTDATE
BRD.SETTLEMENTDATE BILLDUEDATE
FROM BILLSREGISTER BR
INNER JOIN BILLSREGISTERDETAILS BRD ON BR.BILLSREGISTERID = BRD.BILLSREGISTERID
INNER JOIN CUSTOMER CUS ON CUS.CUSTOMERCODE = BR.CUSTOMERCODE
LEFT OUTER JOIN ITEM I ON I.ITEMCODE = BR.VARIANTCODE
LEFT OUTER JOIN SERIALNUMBER SN ON SN.VIN = BR.VIN
WHERE BRD.BILLDUEDATE <= '&PDATE'
ORDER BY BRD.BILLDUEDATE
Kindly verigy the above query.
Kindly suggest something to improve the performance.
I would like to know the reason to delete this quetion... I am already working on this issue... if there is no valid reason he should distribute the points...
It is a very vague question and the only answer given so far is a very vague answer. I don't see a problem deleting.
It would be more helpful if the author posted some of the table DDL, gave some examples of how it is poorly performing and the main business goal of the query.
It is also listed as a SQL Server and Oracle question but looking at what the query is doing, it is obviously written in PL/SQL and is really an Oracle question.
It would be more helpful if the author posted some of the table DDL, gave some examples of how it is poorly performing and the main business goal of the query.
It is also listed as a SQL Server and Oracle question but looking at what the query is doing, it is obviously written in PL/SQL and is really an Oracle question.
Actually it's SQL*Plus (and badly written).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
creating indexes on the tables will improve the performance...
please check it...
all the best...