Geek Answers Handbook

Oracle date column index not used when query is run from java

I have a table containing over 15 million records in oracle. its kind of a log table that has a created_ts column of type date. I have a simple custom type index in the created_ts column.

I have a simple range request:

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

when I run this query from SQLPlus or SQL Developer, etc., for example:

 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 request is returned within 1-2 seconds max.

but when I run the same request in java via JDBC and set the appropriate "?" params using java.sql.Timestamp object. The request takes a lot of time. Analyzing the oracle process, it goes for a full table scan and does not use an index.

The jdbc driver I'm using is ojdbc5 11.1.0.7.0

Can anyone help ... how to properly create an index so that it uses the index.

My problem was solved when I used oracle.sql.DATE objects to set the binding variables instead of "java.sql.timestamp". The query used the index and ran for almost 1-2 seconds.

Thanks to everyone who answered and helped.

But for me this is a problem, since this solution depends on the database, and my application receives the database connection and the request as parameters, loads and process data in a general way. A DB connection can be any RDBMS, such as oracle, mysql, etc.

+4
source share
3 answers

This is the classic behavior for implicit data type conversion. Because the database needs to convert the column data type, it cannot use any index on that column.

In your case, I suspect this is due to the use of java.sql.Timestamp . Is it possible to use the equivalent type from the datatypes Oracle oracle.sql.Timestamp , oracle.sql.Timestamp ? Obviously, this can have some side effects, but I think you should at least test it to see if your problem solves it.

+3
source

The difference can be caused by binding variables with literal values. You are not comparing the same thing.

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 that Oracle will choose when using binding variables. In this case, Oracle must plan before you provide values ​​for the date range. He does not know if you select only a small part of the data or all of this. If you have the same plan (full scan?) As your plan from java, at least you know what is going on.

Then you can consider: -

  • Enabling binding binding (but only after testing this does not mean that something else is bad)
  • Carefully bind dictionary values ​​from java in such a way as to prevent SQL injection
  • By entering a hint in the instructions to indicate that it should use the index you want.
+2
source

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

My assumption is that the optimizer chooses a full table scan because it sees this as the best option in the absence of knowledge of the binding values.

0
source

More articles:


All Articles