How to use Java Date in driver running of different Oracle versions

This article describes how to use Java Date in the driver running of different Oracle versions, if you encounter a similar problem when using Java Date in different Oracle versions during driver running during computer learning, you can click the following article.

I have been tuning an SQL statement for two days. This SQL statement is not complex, similar

Select... from some_view union all select... from some_table where datetime> = d1 and datetime <d2 and... the underlying layer uses ibatis2.1.6 + Oracle 10g.

Today, it took some time to continue studying this problem. The cause of this problem is indeed that "Oracle implicitly converted the datetime field, and CBO failed to use the global index of this column ", however, the problem is not ibatis, But Oracle driver.

Let's use this SQL statement to query the database by binding variables (type: java. util. date), where end_date is of the date type and an index is created.

 
 

  
  
“select count(*) from table1 where end_date >
= :1 and end_date <= :2” 
 
 

Generally, in the face of such an SQL statement, we want its execution plan to go through index range scan. However, by default, Oracle CBO does not select the index location. For example, Oracle uses table full scan. Why?

This type of problem occurs only when TIMESTAMP is introduced in different Oracle versions after 9.2.

Before 9.2, Oracle only had DATE, but not TIMESTAMP. When jdbc preparedStatement. setTimestamp is used, the type of the variable to be bound is set to DATE correctly. After 9.2, Oracle began to support TIMESTAMP, both of which support the precision of yyyy-MM-dd hh24: mi: ss, of course, TIMESTAMP can support to the nanosecond level ), however, the jdbc driver api is not changed to the preparedStatement. when setTimestamp is used, the Oracle driver must choose whether to set the binding variable type to DATE or TIMESTAMP?

It is estimated that the TIMESTAMP precision is higher, and Oracle finally chooses to set the type of the bound variable to TIMESTAMP by default. In this case, if the actual attribute is DATE, Oracle will implicitly convert the format like "TO_TIMESTAMP (date_column) = parameter_timestamp, you must know that Oracle CBO does not select the index of the column to which a function applies, unless it is a function index.

Therefore, table full scan instead of index range scan is used at the top of the page.

Does Oracle provide other methods to correctly provide variable binding? Oracle provides several methods to solve this problem.

1. Upgrade to 11 GB and use the new correct driver api.

2. Change all DATE columns to the TIMESTAMP column.

3. Use V8Compatible flag.

The above content is an introduction to the use of Java Date in drivers of different Oracle versions.