Index on date type column in oracle not used when query is run from java

Question

Hi,

i have a table containing 15+ million records in oracle. its sort of a log table which has a created_ts column of type "date" . i have a simple "non-unique" type index on created_ts column.

i have a simple range query :

select * from table1 where created_ts >= ? and created_ts <= ?;

when i run this query from SQLPlus or SQL Developer etc like this :

select * from table1 
where created_ts >= TO_DATE( '2009-11-10 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 
and created_ts <= TO_DATE( '2009-11-10 23:59:59', 'YYYY-MM-DD HH24:MI:SS');

the query returns within 1-2 second max.

but when I run the exact same query in java over JDBC and set the corresponding "?" params using java.sql.Timestamp object . the query takes long time . Analyzing the oracle process it goes for full table scan and doesnt use the index.

the jdbc driver i am using is ojdbc5 11.1.0.7.0

Can any one please help .. how to create the index correctly so that it uses the index.

---

My problem was resolved when i used "oracle.sql.DATE" objects to set the bind variables instead of "java.sql.timestamp" . The query used the index and executed almost within 1-2 seconds.

Thanks to all who replied and helped.

But its problematic for me as this solution is DB dependent and my app receives DB connection and query as param and load and process data in a generic way. The DB connection can be of any RDBMS like oracle, mysql, etc.

Answer 1

You should try a hint of the form /*+ USE_INDEX(table_name, index_name) */

My guess is that the optimizer is choosing a full table scan because it sees that as the best option in absence of knowing the bind values.

Answer 2

The difference may because of bind variables vs. literal values. You are not comparing the same things.

Try this in SQL*Plus:-

explain plan for
select * from table1 where created_ts >= :1 and created_ts <= :2;

set markup html preformat on
set linesize 100
set pagesize 0  
select plan_table_output 
from table(dbms_xplan.display('plan_table',null,'serial'));

This will show you the plan Oracle will pick when using bind variables. In this scenario, Oracle has to make up a plan before you have provided values for your date range. It does not know if you are selecting only a small fraction of the data or all of it. If this has the same plan (full scan?) as your plan from java, at least you konw what is happening.

Then, you could consider:-

1. Enabling bind peeking (but only after testing this does not cause anything else to go bad)
2. Carefully binding literal values from java in a way that does not allow SQL injection
3. Putting a hint in the statement to indicate it should use the index you want it to.

Answer 3

This is classic behaviour for an implicit datatype conversion. Because the database is having to convert the datatype of the column it cannot use any index on that column.

In your case I suspect this is due to your use of java.sql.Timestamp. Would it be possible to use the equivalent type from the Oracle datatypes package, oracle.sql.Timestamp? Obviously that may have some knock-on effects but I think you should at least test it, to see whether that solves your problem.