SQL – Reformatting to Improve Performance 8

December 4, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Some time ago the following question appeared in a forum:
http://groups.google.com/group/oracle_dba_experts/browse_thread/thread/deb2e3f18be836c0

I am using Oracle 9i.
I have some reports developed in Reports 6i which run very slow.
When I run the query in TOAD, it’s taking too much time for displaying
result.
Can anybody suggest me how to tune this query ?

SELECT DISTINCT c.AccName, SUBSTR (a.VouNo, 3) VouNo,
                a.VouDate, a.TranType, b.ChequeNo,
                b.ChequeDate,
                DECODE (BalType, 'Debit', Amount, 0) Debit,
                DECODE (BalType, 'Credit', Amount, 0) Credit,
                d.DocNo, d.DocDate, a.Remark
           FROM TranDetails a,
                ChequeDetails b,
                AccMaster c,
                TranDocDetails d
          WHERE c.AccCode <> :p_BankAccCode
            AND a.VouDate BETWEEN :st_date AND :end_date
            AND a.CompanyNo = c.CompanyNo
            AND a.CompanyNo = b.CompanyNo(+)
            AND a.VouNo = b.VouNo(+)
            AND a.VouDate = b.VouDate(+)
            AND a.nu_serial_no = b.nu_serial_no(+)
            AND a.nu_serial_no = d.nu_serial_no(+)
            AND a.AccCode = c.AccCode
            AND a.TranType IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')
            AND NVL (a.SecFlag, 'N') = 'Y'
            AND d.VouNo(+) = a.VouNo
            AND d.VouDate(+) = a.VouDate
            AND c.CompanyNo = :p_CompanyNo
            AND d.CompanyNo(+) = :p_CompanyNo
            AND    a.CompanyNo
                || a.VouNo
                || TO_CHAR (a.VouDate, 'dd-mm-yyyy') IN (
                   SELECT    d.CompanyNo
                          || d.VouNo
                          || TO_CHAR (d.VouDate, 'dd-mm-yyyy')
                     FROM TranDetails d
                    WHERE d.AccCode = :p_BankAccCode
                      AND NVL (d.SecFlag, 'N') = 'Y'
                      AND d.CompanyNo = :p_CompanyNo)
       ORDER BY 3, 2

The Oracle version number (for example 9.2.0.4) is important, as it determines what types of transformations Oracle may use to help improve the query performance.  First, a slight change in the formatting to make it easier for me to read (you will need to fix the DECODE statements):

SELECT DISTINCT
  AM.ACCNAME,
  SUBSTR (TD.VOUNO, 3) VOUNO,
  TD.VOUDATE,
  TD.TRANTYPE,
  CD.CHEQUENO,
  CD.CHEQUEDATE,
  DECODE (BALTYPE, 'DEBIT', AMOUNT, 0) DEBIT,
  DECODE (BALTYPE, 'CREDIT', AMOUNT, 0) CREDIT,
  TDD.DOCNO,
  TDD.DOCDATE,
  TD.REMARK
FROM
  TRANDETAILS TD,
  CHEQUEDETAILS CD,
  ACCMASTER AM,
  TRANDOCDETAILS TDD
WHERE
  AM.ACCCODE <> :V_BANKACCCODE
  AND TD.VOUDATE BETWEEN :ST_DATE AND :END_DATE
  AND TD.COMPANYNO = AM.COMPANYNO
  AND TD.COMPANYNO = CD.COMPANYNO(+)
  AND TD.VOUNO = CD.VOUNO(+)
  AND TD.VOUDATE = CD.VOUDATE(+)
  AND TD.NU_SERIAL_NO = CD.NU_SERIAL_NO(+)
  AND TD.NU_SERIAL_NO = TDD.NU_SERIAL_NO(+)
  AND TD.ACCCODE = AM.ACCCODE
  AND TD.TRANTYPE IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')
  AND NVL (TD.SECFLAG, 'N') = 'Y'
  AND TDD.VOUNO(+) = TD.VOUNO
  AND TDD.VOUDATE(+) = TD.VOUDATE
  AND AM.COMPANYNO = :V_COMPANYNO
  AND TDD.COMPANYNO(+) = :V_COMPANYNO
  AND TD.COMPANYNO || TD.VOUNO || TO_CHAR (TD.VOUDATE, 'DD-MM-YYYY') IN
    (SELECT
      TD2.COMPANYNO || TD2.VOUNO || TO_CHAR (TD2.VOUDATE, 'DD-MM-YYYY')
    FROM
      TRANDETAILS TD2
    WHERE
      TD2.ACCCODE = :V_BANKACCCODE
      AND NVL (TD2.SECFLAG, 'N') = 'Y'
      AND TD2.COMPANYNO = :V_COMPANYNO)
ORDER BY
  3,
  2;

In the above, you are concatenating three columns into a single value, and then trying to determine if the same concatenated value exists in a table.  A DBMS_XPLAN would probably show many, many full table scans of the TRANSDETAILS table.

If we just list the three columns, and use TRUNC rather than TO_CHAR, we may be able to take advantage of an index on those columns, and avoid the CPU overhead of a data type conversion of a DATE column to a character data type.  You could use an EXISTS syntax instead, which might be more efficient (not shown):

SELECT DISTINCT
  AM.ACCNAME,
  SUBSTR (TD.VOUNO, 3) VOUNO,
  TD.VOUDATE,
  TD.TRANTYPE,
  CD.CHEQUENO,
  CD.CHEQUEDATE,
  DECODE (BALTYPE, 'DEBIT', AMOUNT, 0) DEBIT,
  DECODE (BALTYPE, 'CREDIT', AMOUNT, 0) CREDIT,
  TDD.DOCNO,
  TDD.DOCDATE,
  TD.REMARK
FROM
  TRANDETAILS TD,
  CHEQUEDETAILS CD,
  ACCMASTER AM,
  TRANDOCDETAILS TDD
WHERE
  AM.ACCCODE <> :V_BANKACCCODE
  AND TD.VOUDATE BETWEEN :ST_DATE AND :END_DATE
  AND TD.COMPANYNO = AM.COMPANYNO
  AND TD.COMPANYNO = CD.COMPANYNO(+)
  AND TD.VOUNO = CD.VOUNO(+)
  AND TD.VOUDATE = CD.VOUDATE(+)
  AND TD.NU_SERIAL_NO = CD.NU_SERIAL_NO(+)
  AND TD.NU_SERIAL_NO = TDD.NU_SERIAL_NO(+)
  AND TD.ACCCODE = AM.ACCCODE
  AND TD.TRANTYPE IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')
  AND NVL (TD.SECFLAG, 'N') = 'Y'
  AND TDD.VOUNO(+) = TD.VOUNO
  AND TDD.VOUDATE(+) = TD.VOUDATE
  AND AM.COMPANYNO = :V_COMPANYNO
  AND TDD.COMPANYNO(+) = :V_COMPANYNO
  AND (TD.COMPANYNO,TD.VOUNO,TRUNC(TD.VOUDATE)) IN
    (SELECT
      TD2.COMPANYNO,
      TD2.VOUNO,
      TRUNC(TD2.VOUDATE)
    FROM
      TRANDETAILS TD2
    WHERE
      TD2.ACCCODE = :V_BANKACCCODE
      AND NVL (TD2.SECFLAG, 'N') = 'Y'
      AND TD2.COMPANYNO = :V_COMPANYNO)
ORDER BY
  3,
  2;

Let’s transform the subquery into an inline view, which will sometimes help improve performance (usually a very noticeable on Oracle 8i).  Such a transformation may be performed by Oracle automatically:

SELECT DISTINCT
  AM.ACCNAME,
  SUBSTR (TD.VOUNO, 3) VOUNO,
  TD.VOUDATE,
  TD.TRANTYPE,
  CD.CHEQUENO,
  CD.CHEQUEDATE,
  DECODE (BALTYPE, 'DEBIT', AMOUNT, 0) DEBIT,
  DECODE (BALTYPE, 'CREDIT', AMOUNT, 0) CREDIT,
  TDD.DOCNO,
  TDD.DOCDATE,
  TD.REMARK
FROM
  TRANDETAILS TD,
  CHEQUEDETAILS CD,
  ACCMASTER AM,
  TRANDOCDETAILS TDD,
  (SELECT DISTINCT
    TD2.COMPANYNO,
    TD2.VOUNO,
    TRUNC(TD2.VOUDATE) VOUDATE
  FROM
    TRANDETAILS TD2
  WHERE
    TD2.ACCCODE = :V_BANKACCCODE
    AND NVL (TD2.SECFLAG, 'N') = 'Y'
    AND TD2.COMPANYNO = :V_COMPANYNO) TD2
WHERE
  AM.ACCCODE <> :V_BANKACCCODE
  AND TD.VOUDATE BETWEEN :ST_DATE AND :END_DATE
  AND TD.COMPANYNO = AM.COMPANYNO
  AND TD.COMPANYNO = CD.COMPANYNO(+)
  AND TD.VOUNO = CD.VOUNO(+)
  AND TD.VOUDATE = CD.VOUDATE(+)
  AND TD.NU_SERIAL_NO = CD.NU_SERIAL_NO(+)
  AND TD.NU_SERIAL_NO = TDD.NU_SERIAL_NO(+)
  AND TD.ACCCODE = AM.ACCCODE
  AND TD.TRANTYPE IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')
  AND NVL (TD.SECFLAG, 'N') = 'Y'
  AND TDD.VOUNO(+) = TD.VOUNO
  AND TDD.VOUDATE(+) = TD.VOUDATE
  AND AM.COMPANYNO = :V_COMPANYNO
  AND TDD.COMPANYNO(+) = :V_COMPANYNO
  AND TD.COMPANYNO=TD2.COMPANYNO
  AND TD.VOUNO=TD2.VOUNO
  AND TRUNC(TD.VOUDATE)=TD2.VOUDATE
ORDER BY
  3,
  2;

Is it really necessary to reference the TRANDETAILS table twice?  Will the following work?:

SELECT DISTINCT
  AM.ACCNAME,
  SUBSTR (TD.VOUNO, 3) VOUNO,
  TD.VOUDATE,
  TD.TRANTYPE,
  CD.CHEQUENO,
  CD.CHEQUEDATE,
  DECODE (BALTYPE, 'DEBIT', AMOUNT, 0) DEBIT,
  DECODE (BALTYPE, 'CREDIT', AMOUNT, 0) CREDIT,
  TDD.DOCNO,
  TDD.DOCDATE,
  TD.REMARK
FROM
  TRANDETAILS TD,
  CHEQUEDETAILS CD,
  ACCMASTER AM,
  TRANDOCDETAILS TDD
WHERE
  AM.ACCCODE <> :V_BANKACCCODE
  AND TD.VOUDATE BETWEEN :ST_DATE AND :END_DATE
  AND TD.COMPANYNO = AM.COMPANYNO
  AND TD.COMPANYNO = CD.COMPANYNO(+)
  AND TD.VOUNO = CD.VOUNO(+)
  AND TD.VOUDATE = CD.VOUDATE(+)
  AND TD.NU_SERIAL_NO = CD.NU_SERIAL_NO(+)
  AND TD.NU_SERIAL_NO = TDD.NU_SERIAL_NO(+)
  AND TD.ACCCODE = AM.ACCCODE
  AND TD.TRANTYPE IN ('JV', 'BR', 'BP', 'CN', , 'CR', 'CP')
  AND NVL (TD.SECFLAG, 'N') = 'Y'
  AND TDD.VOUNO(+) = TD.VOUNO
  AND TDD.VOUDATE(+) = TD.VOUDATE
  AND AM.COMPANYNO = :V_COMPANYNO
  AND TDD.COMPANYNO(+) = :V_COMPANYNO
  AND TD.ACCCODE = :V_BANKACCCODE
  AND NVL (TD.SECFLAG, 'N') = 'Y'
  AND TD.COMPANYNO = :V_COMPANYNO
ORDER BY
  3,
  2;

Why do you need to use the DISTINCT clause – is that a sign that you do not have sufficient joins between the tables?  It might be helpful to post a DBMS_XPLAN for the query.