[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query Tuning

Posted on 2008-06-24
8
Medium Priority
?
554 Views
Last Modified: 2013-12-18
SELECT DISTINCT BR.CUSTOMERCODE,
                DECODE(CUS.CUSTOMERTYPENUM,
                       1,
                       CUS.LASTNAME || ' ' || CUS.FIRSTNAME || ' ' ||
                       CUS.MIDDLENAME,
                       2,
                       CUS.COMPANYNAME,CUS.COMPANYNAME) 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.BILLDUEDATE)
                        OVER(PARTITION BY BRD.BILLSREGISTERID),
                        'DD-MON-YYYY') FIRSTDUEDATE,
                TO_CHAR(MAX(BRD.BILLDUEDATE)
                        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.
0
Comment
Question by:PHILIP233
5 Comments
 
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 21853050
hi,

creating indexes on the tables will improve the performance...

please check it...

all the best...
0
 
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 21853564
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...
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 21853990
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.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 21857643

Actually it's SQL*Plus (and badly written).

0
 
LVL 3

Accepted Solution

by:
gajmp earned 500 total points
ID: 21863857
try this one.... if this also give perf probs then join the global temp table temp_overdueamnt and temp_undueamount in the from clasuse..instead of creating inline view for the same... If that also not satified then post the explain plan of ur original SQL..


-- CREATE GLOBAL TEMP TABLE FOR OVERDUEAMOUNT (ONE TIME EXECUTION)  
CREATE GLOBAL TEMPORARY TABLE temp_overdueamount (
  billsregisterid  datatype,
  overdueamount  datatype
) ON COMMIT DELETE ROWS;

-- CREATE GLOBAL TEMP TABLE FOR UNDUEAMOUNT (ONE TIME EXECUTION)
CREATE GLOBAL TEMPORARY TABLE temp_undueamount (
  billsregisterid  datatype,
  undueamount  datatype
) ON COMMIT DELETE ROWS

-- BEFORE EXECUTING UR SQL INSERT ROW INTO ABOVE TWO TEMP TABLE (THIS SHOULD BE EXECUTED BEFOR WHEN EVER UR EXECUTING UR SQL)  
INSERT INTO temp_overdueamount
SELECT brd1.billsregisterid, SUM (brd.billsamounttc)
FROM billsregisterdetails brd1
WHERE (((brd1.billduedate <= '&PDATE') AND (brd1.settlementdate > '&PDATE' OR brd1.settlementdate IS NULL )))
GROUP BY brd1.billsregisterid;

INSERT INTO temp_undueamount
SELECT br2.billsregisterid, 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'
GROUP BY br2.billsregisterid

-- UR MODIFIED SQL
SELECT DISTINCT br.customercode,
         DECODE (cus.customertypenum, 1, cus.lastname||' '||cus.firstname||' '||cus.middlename, 2, cus.companyname, cus.companyname ) 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 overdueamount
          FROM temp_overdueamount o
            WHERE br.billsregisterid = o.billsregisterid ) overdueamount,
         (SELECT overdueamount
          FROM temp_undueamount u
            WHERE br.billsregisterid = u.billsregisterid ) undueamount,
            TO_CHAR (br.currentsetdate, 'DD-MON-YYYY') setdate,
        (br.totalloanamount / br.exchangerate) loanamount,
        TO_CHAR(MIN (brd.billduedate) OVER (PARTITION BY brd.billsregisterid),'DD-MON-YYYY') firstduedate,
        TO_CHAR(MAX (brd.billduedate) 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
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question