date parameter conversion

[roberthagan]

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

 

[roberthagan]

Never mind. There was another possibility that I didn't test. If you trim coded_rtg, the problem goes away.

 

[Dagon]

You would have to look at the plans of the respective queries to see what the differences are. If you have histogram data on the table, then it is possible for a query with a specific date to give better performance than one with bind variables because Oracle is able to determine that this is a rarely used value whereas it would not be able to do this with the bind variable query. I'm not sure why trimming coded_rtg would help unless you've got a function-based index on it.

 

[ChrisHunt]

If coded_rtg can only have values of "YES" or "NO", building an index on it can cause more harm than good. Other than that observation, it's hard to tell what's going on without knowing more.

EXPLAIN PLAN is your friend...

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[roberthagan]

This got pushed down the priority list, but I will come back to it. Our DBA thought also there might be something strange about bind variables. Trimming works when some people run the query and not others.

Thanks