使用 jdbc Timestamp 或 Date 时与 Oracle 的执行计划差异不可忽略

Question

我在分析 Oracle 执行计划时发现了一个令人惊讶的事实。查看此查询。提示只是显示我有一个索引，并且我希望 Oracle 将其用于范围扫描：

// execute_at is of type DATE.
PreparedStatement stmt = connection.prepareStatement(
    "SELECT /*+ index(my_table my_index) */ * " + 
    "FROM my_table " +
    "WHERE execute_at > ? AND execute_at < ?");

这两个绑定导致完全不同的行为（为了排除绑定变量窥视问题，我实际上强制执行了两次硬解析）：

// 1. with timestamps
stmt.setTimestamp(1, start);
stmt.setTimestamp(2, end);

// 2. with dates
stmt.setDate(1, start);
stmt.setDate(2, end);

1 ）使用时间戳，我得到一个INDEX FULL SCAN，从而得到一个过滤谓词

--------------------------------------------------------------
| Id  | Operation                    | Name                  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |
|*  1 |  FILTER                      |                       |
|   2 |   TABLE ACCESS BY INDEX ROWID| my_table              |
|*  3 |    INDEX FULL SCAN           | my_index              |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(:1<:2)"
   3 - filter((INTERNAL_FUNCTION(""EXECUTE_AT"")>:1 AND 
               INTERNAL_FUNCTION(""EXECUTE_AT"")<:2))

2）使用日期，我得到更好的INDEX RANGE SCAN和一个访问谓词

--------------------------------------------------------------
| Id  | Operation                    | Name                  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |
|*  1 |  FILTER                      |                       |
|   2 |   TABLE ACCESS BY INDEX ROWID| my_table              |
|*  3 |    INDEX RANGE SCAN          | my_index              |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(:1<:2)"
   3 - access(""EXECUTE_AT"">:1 AND ""EXECUTE_AT""<:2)

现在我的例子是只是一个例子。真正的查询要复杂得多，必须使用RANGE SCANS或UNIQUE SCANS（取决于谓词）而不是FULL SCANS 。

我在这里有什么误解吗？有人可以指出我最好的解决方案/实践吗？因为在Java世界中，我认为java.sql.Timestamp更合适，但我们的大多数列都是Oracle的DATE类型。我们使用 Java 6 和 Oracle 11g

Answer 1

事实是，Oracle 时间戳和 Oracle 日期是两种不同的数据类型。为了将时间戳与日期进行比较，Oracle 必须运行转换 - INTERNAL_FUNCTION()。有趣的设计决策是 Oracle 转换表列而不是传递的值，这意味着查询不再使用索引。

我已经能够在 SQL*Plus 中重现您的场景，因此使用 java.sql.Timestamp 不是问题。将传递的时间戳转换为日期确实可以解决问题...

SQL> explain plan for
  2      select * from test1
  3      where d1 > cast(to_timestamp('01-MAY-2011 00:00:00.000', 'DD-MON-YYYY Hh24:MI:SS.FF') as date)
  4       and d2 > cast(to_timestamp('01-JUN-2011 23:59:59.999', 'DD-MON-YYYY Hh24:MI:SS.FF') as date)
  5  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
-----------------------------------------------------------
Plan hash value: 1531258174

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    25 |   500 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1 |    25 |   500 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_I  |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------    
   2 - access("D1">CAST(TO_TIMESTAMP('01-MAY-2011 00:00:00.000','DD-MON-YYYY
              Hh24:MI:SS.FF') AS date) AND "D2">CAST(TO_TIMESTAMP('01-JUN-2011
              23:59:59.999','DD-MON-YYYY Hh24:MI:SS.FF') AS date) AND "D1" IS NOT NULL)
       filter("D2">CAST(TO_TIMESTAMP('01-JUN-2011 23:59:59.999','DD-MON-YYYY
              Hh24:MI:SS.FF') AS date))

18 rows selected.

SQL>

但我认为这对您没有任何帮助：仅传递日期会更容易。

---

有趣的是，构建基于函数的索引将日期列转换为时间戳并没有帮助。 INTERNAL_FUNCTION() 调用未被识别为 CAST() 并且索引被忽略。尝试使用 INTERNAL_FUNCTION() 构建索引会抛出 ORA-00904。

Answer 2

虽然APC的答案已经充分解释了为什么会发生这种情况，但如果您想解决这个问题，以下博客文章很有趣JPA 和 Hibernate 的问题：

• http://blog.jooq.org/2014/12/29/leaky-abstractions-or-how-to-bind-oracle-date- Correctly-with-hibernate/

或者使用 JDBC 或 jOOQ :

• <一href="http://blog.jooq.org/2014/12/22/are-you-binding-your-oracle-dates- Correctly-i-bet-you-arent/" rel="nofollow noreferrer">http ://blog.jooq.org/2014/12/22/are-you-binding-your-oracle-dates- Correctly-i-bet-you-arent/

特别是，一个可能的解决方案就是简单地将 oracle.sql.DATE 而不是任何 java.sql 类型传递给 PreparedStatement