Query improvement on 9.2.0.4

Giganews Newsgroups
Subject: Query improvement on 9.2.0.4
Posted by:  jwilli…@aglresources.com
Date: 15 Jan 2004

Running on a 9.2.0.4 database. Its a vendor app that can't support CBO
yet!

SQL runs inside new PL/SQL routine. It runs in about 5 seconds. A
function-based index might be the trick for the query but since I am
RBO that won't work.
Some other good hints?

SELECT APPOINTMENT_DTE,AW.DESCRIPTION
FROM UPI.AR_AVAILABILITY A, APPOINTMENT_WINDOW AW
WHERE (APPOINTMENT_DTE = STARTDATE
      OR TO_DATE(APPOINTMENT_DTE,'YYYYMMDD') BETWEEN
TO_DATE(STARTDATE,'YYYYMMDD') AND TO_DATE(STARTDATE,'YYYYMMDD') +
DAYSOUT)
AND A.LEVEL_NBR = AW.LEVEL_NBR
AND AW.APT_WINDOW_HRS = WINDOW
AND MIN_AVAIL = 0
AND DEAD_MIN_AVAIL > 0
AND DEAD_MIN_AVAIL >= getOrderDuration(GEO,ORDER_TYPE)
AND SUBSTR(GEO_AREA_CD,1,2) = SUBSTR(GEO,1,2)
AND JOB_GROUP = JOBGROUP
AND ROWNUM <= MRESULT
AND APPOINTMENT_DTE NOT IN (SELECT EXCEPTION_DTE FROM AT_EXCEPTION
WHERE GEO_AREA_CD = GEO)
GROUP BY GEO_AREA_CD,APPOINTMENT_DTE,AW.DESCRIPTION
ORDER BY GEO_AREA_CD, APPOINTMENT_DTE;
--

SELECT APPOINTMENT_DTE,AW.DESCRIPTION
FROM UPI.AR_AVAILABILITY A, APPOINTMENT_WINDOW AW
WHERE (APPOINTMENT_DTE = TO_DATE('20040101','YYYYMMDD')
    OR TO_DATE(APPOINTMENT_DTE,'YYYYMMDD') BETWEEN
TO_DATE('20040101','YYYYMMDD') AND TO_DATE('20040115','YYYYMMDD') + 5)
AND A.LEVEL_NBR = AW.LEVEL_NBR
AND AW.APT_WINDOW_HRS = 2
AND MIN_AVAIL = 0
AND DEAD_MIN_AVAIL > 0
AND DEAD_MIN_AVAIL >=
casavailability_pkg.getOrderDuration('6VC00','ICO')
AND SUBSTR(GEO_AREA_CD,1,2) = '6VC00'
AND JOB_GROUP = 'FTECH'
AND ROWNUM <= 2
AND APPOINTMENT_DTE NOT IN (SELECT EXCEPTION_DTE FROM AT_EXCEPTION
WHERE GEO_AREA_CD = '6VC00')
GROUP BY GEO_AREA_CD,APPOINTMENT_DTE,AW.DESCRIPTION
ORDER BY GEO_AREA_CD, APPOINTMENT_DTE;

Elapsed: 00:00:03.76

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE
  1    0  SORT (GROUP BY)
  2    1    COUNT (STOPKEY)
  3    2      FILTER
  4    3        MERGE JOIN
  5    4          SORT (JOIN)
  6    5            TABLE ACCESS (FULL) OF 'APPOINTMENT_WINDOW'
  7    4          SORT (JOIN)
  8    7            TABLE ACCESS (FULL) OF 'AR_AVAILABILITY'
  9    3        TABLE ACCESS (FULL) OF 'AT_EXCEPTION'
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      10690  consistent gets
      10592  physical reads
          0  redo size
        290  bytes sent via SQL*Net to client
        368  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          0  rows processed

Replies