Solved

Query Tuning

Posted on 2008-06-24
8
551 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 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 125 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

687 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