Performance differences with to_date, date literal, and the CBO
431425Oct 19 2004 — edited Oct 19 2004We're in the process of moving from the rule based optimizer to the cost based optimizer on our 9.2.0.4 database, and I ran into a query that is running much slower under the CBO:
select patient_visit.patient_visit_rsn, patients.birthdate
from patients, patient_visit
where patient_visit.patient_rsn = patients.patient_rsn
and patients.birthdate >=
to_date('12/19/1977','mm/dd/yyyy')
and patients.birthdate <
to_date('12/20/1977','mm/dd/yyyy')
(Find all patients born on 12/19/1977)
Under RBO, this ran in .047 seconds; under the CBO it ran in 6.218 seconds. I checked the explain plan, and both plans are identical and both plans use the birthdate index as the leading index.
I tried some variations of the query, and found that I could get the same performance out of the CBO if I replaced the to_date() expressions with the date literal syntax (DATE '2004-12-19' and DATE '2004-12-20') or if I set cursor sharing to EXACT (it had been FORCE before).
I've been hunting through the documentation, MetaLink, and the forums for an explanation for this, and haven't quite hit on it. Does anybody have any ideas why this is happening and how I might be able to fix it without scanning my entire application for queries using to_date?
-- Jeff Beal