roberthagan
Technical User
I have a query which take a minimum of 30 seconds to run:
SELECT C.SITE_CD,CD.DESCRIP,COUNT(DISTINCT C.CLIENT_ID)
FROM CLIENT C, ASSESS A, CODES CD
WHERE C.CLIENT_ID = A.CLIENT_ID AND
A.ASSESS_CD = CD.CDVALUE AND
A.START_DT BETWEEN TO_DATEstart_dt,'mm/dd/yyyy') AND TO_DATEend_dt','mm/dd/yyyy') AND
A.ASSESS_CD IN ('CIU-014', 'CIU-015',...'CIU-045B') AND
(A.CODED_RTG = 'YES')
GROUP BY C.SITE_CD, CD.DESCRIP
There are indexes on start_dt, assess_cd, and coded_rtg.
Reducing Asess_cd to = one of the codes makes no difference, nor does any other change I've tried, except
if I change the start_dt/end_dt params to hard-coded values, either using TO_DATE or directly in Oracle date format. Then the query takes about 1 second. I have not noticed this with any other use of date parameters.
Any idea why this might be happening?
Thanks,
Bob Hagan
SELECT C.SITE_CD,CD.DESCRIP,COUNT(DISTINCT C.CLIENT_ID)
FROM CLIENT C, ASSESS A, CODES CD
WHERE C.CLIENT_ID = A.CLIENT_ID AND
A.ASSESS_CD = CD.CDVALUE AND
A.START_DT BETWEEN TO_DATEstart_dt,'mm/dd/yyyy') AND TO_DATEend_dt','mm/dd/yyyy') AND
A.ASSESS_CD IN ('CIU-014', 'CIU-015',...'CIU-045B') AND
(A.CODED_RTG = 'YES')
GROUP BY C.SITE_CD, CD.DESCRIP
There are indexes on start_dt, assess_cd, and coded_rtg.
Reducing Asess_cd to = one of the codes makes no difference, nor does any other change I've tried, except
if I change the start_dt/end_dt params to hard-coded values, either using TO_DATE or directly in Oracle date format. Then the query takes about 1 second. I have not noticed this with any other use of date parameters.
Any idea why this might be happening?
Thanks,
Bob Hagan