本文介绍了使用 jdbc Timestamp 或 Date 时与 Oracle 的不可忽略的执行计划差异的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在分析 Oracle 执行计划并发现了一个惊人的事实.看看这个查询.提示只是显示我有一个索引,我希望 Oracle 将它用于范围扫描:

I'm analysing Oracle execution plans and found an astonishing fact. Check out this query. The hint is just to display that I have an index and I'd expect Oracle to use it for range scans:

// 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 < ?");

这两个绑定导致完全不同的行为(为了排除绑定变量窥视问题,我实际上强制执行了两个硬解析):

These two bindings result in entirely different behaviour (to exclude bind variable peeking issues, I actually enforced two hard-parses):

// 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 和一个过滤谓词

1) With timestamps, I get an INDEX FULL SCAN and thus a filter predicate

--------------------------------------------------------------
| 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 和访问谓词

2) With dates, I get the much better INDEX RANGE SCAN and an access predicate

--------------------------------------------------------------
| 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 SCANSUNIQUE SCANS(取决于谓词)而不是 FULL SCANS.

Now my example is just an example. The real query is much more complex, where it is essential to have RANGE SCANS or UNIQUE SCANS (depending on the predicate) rather than FULL SCANS.

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

Is there something I'm misunderstanding here? Can someone point me to the best solution/practice? Because in the Java world, I think that java.sql.Timestamp is much more suitable but most of our columns are of Oracle's DATE type. We're using Java 6 and Oracle 11g

推荐答案

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

So the thing is, Oracle timestamps and Oracle dates are two different datatypes. In order to compare a timestamp to a date Oracle has to run a conversion - that INTERNAL_FUNCTION(). The interesting design decision is that Oracle converts the table column rather than the passed value, which means the query no longer uses the index.

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

I have been able to reproduce your scenario in SQL*Plus, so it's not a problem with using java.sql.Timestamp. Casting the passed timestamps to dates does resolve the problem...

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>

但我认为这对您没有任何帮助:只是传递日期会更容易.

But I don't think that helps you any: it would be easier to just pass dates instead.

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

Interestingly, building a function-based index casting the date columns to timestamps doesn't help. The INTERNAL_FUNCTION() call isn't recognised as a CAST() and the index is ignored. Trying to build an index using INTERNAL_FUNCTION() hurls an ORA-00904.

这篇关于使用 jdbc Timestamp 或 Date 时与 Oracle 的不可忽略的执行计划差异的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-04 13:45
查看更多