Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

date parameter conversion

Status
Not open for further replies.

roberthagan

Technical User
May 9, 2006
27
US
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_DATE:)start_dt,'mm/dd/yyyy') AND TO_DATE:)end_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
 
Never mind. There was another possibility that I didn't test. If you trim coded_rtg, the problem goes away.
 
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.
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top