问题使用jdbc时间戳或日期时，与Oracle的执行计划差异不可忽略

我正在分析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

12902

2017-07-07 14:58

起源

注意，我在这里发现了一个类似的问题： stackoverflow.com/questions/1945603/... - Lukas Eder

答案:

所以问题是，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。

2017-07-07 17:12

铸造是可能的，因为我们对所有查询使用数据库抽象层，类似于 jooq.org。但我担心转换可能会引入基于功能的索引的新问题。在这里看到这个问题： stackoverflow.com/questions/5340738/...。所以是的，可能，考虑到我们只有 DATE 数据库中的列，可能我们应该从“最后一分钟”转换日期 java.sql.Timestamp 至 java.sql.Date在数据库抽象层中绑定之前...... - Lukas Eder

运用 java.sql.Timestamp 在Java中，但绑定 java.sql.Date 诀窍。执行计划现在显示索引使用...让我们希望这不会破坏其他东西...... :) - Lukas Eder

不幸的是，在某些情况下，Oracle（可能是JDBC）会截断时间部分 java.sql.Date。尤其是 INSERT 和 UPDATE 声明，这可能是错误的......： - / - Lukas Eder

我想，铸造是最安全的。但在将该补丁应用于我们的数据库抽象之前，我会更彻底地检查... - Lukas Eder

而 APC的答案已经充分解释了为什么会发生这种情况，以下博客文章很有趣，以防你试图用JPA和Hibernate解决这个问题：

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

或者使用JDBC或jOOQ：

http://blog.jooq.org/2014/12/22/are-you-binding-your-oracle-dates-correctly-i-bet-you-arent/

特别是，一种可能的解决方案是简单地通过 oracle.sql.DATE 而不是任何 java.sql 输入到 PreparedStatement

2017-12-29 15:18

问题 使用jdbc时间戳或日期时，与Oracle的执行计划差异不可忽略

答案:

热门问题

问题使用jdbc时间戳或日期时，与Oracle的执行计划差异不可忽略