I am posting this to share our solution to a problem ...no help is requested.
We had a problem in production where some of our queries weren't using the oracle date index (full table scan) and so were taking 12 sec and a hogging the disk I/O. Our DBAs asked me to fix it. After mocking up a lot of fake data, I found problem.
This is the kind of problem we don't see in development since we don't have 1 million records in a dev database.
The call to find() wasn't specifying the Hibernate type. The query ran fine, but indexes weren't used:
This doesn't use the index:
Code:
getHibernateTemplate().find("from TestResult testResult " +
"where testResult.dateCreated >= ? and testResult.dateCreated < ?",
new Object[]{startDate, endDate});
This uses the index:
Code:
getHibernateTemplate().find("from TestResult testResult " +
"where testResult.dateCreated >= ? and testResult.dateCreated < ?",
new Object[]{startDate, endDate},
new Type[]{Hibernate.DATE, Hibernate.DATE});
We are using:
Oracle 9i 9.2
Oracle thin driver 9.2.0.5
Hibernate version 2.1.2, 4 February 2003
Login
FAQ
Search