Query tuning help

From: Scott Klemens <sklemens_at_gsu.edu>
Date: Tue, 16 Nov 1999 17:32:17 -0500
Message-ID: <4WkY3.7$pI1.13@tattler>

I am having a horrible time tuning this query. It really bothers me that I can't figure this out.

Can anyone tell me what I should look for first? This is the explain plan.

SORT GROUP BY
    FILTER

      TABLE ACCESS FULL PS_DISTRIB_LINE
      NESTED LOOPS
        INDEX RANGE SCAN PS_JRNL2
        TABLE ACCESS BY ROWID PS_VCHR_ACCTG_LINE
          INDEX RANGE SCAN PS_VCHR2

Based on the where clause can you tell what you think should be indexed for this query?

The output from tkprof shows an obvious problem so....

SELECT /*+ FULL(PS_DISTRIB_LINE) CACHE(PS_DISTRIB_LINE) */

  2    A.ACCOUNT, A.FUND_CODE, A.DEPTID,
  3    A.PROGRAM_CODE, A.APPROPRIATION_NBR, A.BUDGET_YEAR,
  4    A.PROJECT_ID, SUM( A.MONETARY_AMOUNT)

  5 FROM PS_DISTRIB_LINE A
  6 WHERE A.ACCOUNT BETWEEN '751000' AND '751999'

  7       AND A.FUND_CODE IN ('10','12','13','14','20','50','51')
  8       AND A.BCM_LINE_STATUS = 'V'
  9       AND NOT EXISTS (SELECT 'X'

 10 FROM PS_VCHR_ACCTG_LINE B, PS_JRNL_HEADER C  11 WHERE A.VOUCHER_ID = B.VOUCHER_ID AND C.JRNL_HDR_STATUS = 'P'

 12       AND C.POSTED_DATE IS NOT NULL
 13       AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
 14       AND B.JOURNAL_ID = C.JOURNAL_ID
 15       AND B.JOURNAL_DATE = C.JOURNAL_DATE)

 16* GROUP BY A.ACCOUNT, A.FUND_CODE, A.DEPTID, A.PROGRAM_CODE, A.APPROPR
IATION_NBR, A.BUDGET_YEAR, A.PROJECT_ID SELECT /*+ FULL(PS_DISTRIB_LINE) CACHE(PS_DISTRIB_LINE) */

  2    A.ACCOUNT, A.FUND_CODE, A.DEPTID,
  3    A.PROGRAM_CODE, A.APPROPRIATION_NBR, A.BUDGET_YEAR,
  4    A.PROJECT_ID, SUM( A.MONETARY_AMOUNT)

  5 FROM PS_DISTRIB_LINE A
  6 WHERE A.ACCOUNT BETWEEN '751000' AND '751999'

  7       AND A.FUND_CODE IN ('10','12','13','14','20','50','51')
  8       AND A.BCM_LINE_STATUS = 'V'
  9       AND NOT EXISTS (SELECT 'X'

 10 FROM PS_VCHR_ACCTG_LINE B, PS_JRNL_HEADER C  11 WHERE A.VOUCHER_ID = B.VOUCHER_ID AND C.JRNL_HDR_STATUS = 'P'

 12       AND C.POSTED_DATE IS NOT NULL
 13       AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
 14       AND B.JOURNAL_ID = C.JOURNAL_ID
 15       AND B.JOURNAL_DATE = C.JOURNAL_DATE)

 16* GROUP BY A.ACCOUNT, A.FUND_CODE, A.DEPTID, A.PROGRAM_CODE, A.APPROPR
IATION_NBR, A.BUDGET_YEAR, A.PROJECT_ID Received on Tue Nov 16 1999 - 16:32:17 CST