Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Performance differences with to_date, date literal, and the CBO

431425Oct 19 2004 — edited Oct 19 2004
We'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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 16 2004
Added on Oct 19 2004
16 comments
1,161 views