当我为 DATE 列传递 java.sql.Timestamp 时，为什么 Oracle 如此缓慢？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1945603/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why is Oracle so slow when I pass a java.sql.Timestamp for a DATE column?
提问by Aaron Digulla
I have a table with a DATEcolumn with time (as usual in Oracle since there isn't a TIMEtype). When I query that column from JDBC, I have two options:
我有一个DATE带有时间列的表（在 Oracle 中像往常一样，因为没有TIME类型）。当我从 JDBC 查询该列时，我有两个选择：
- Manually convert the values with Oracle's
to_date() - Use a
java.sql.Timestamp
- 使用 Oracle 的手动转换值
to_date() - 用一个
java.sql.Timestamp
Both approaches work and have exclusive areas of hideousness. My problem is when I'm SELECTing data. Here are two sample queries:
这两种方法都有效，并且具有独特的可怕领域。我的问题是当我正在SELECT处理数据时。以下是两个示例查询：
select *
from TABLE
where TS between {ts '2009-12-08 00:00:00.000'} and {ts '2009-12-09 00:00:00.000'}
select *
from TABLE
where TS between trunc({ts '2009-12-08 00:00:00.000'}) and trunc({ts '2009-12-09 00:00:00.000'})
Both queries work, return the same results and produce the exact same output in EXPLAIN PLAN. This right indexes are used.
两个查询都有效，返回相同的结果并在EXPLAIN PLAN. 这个正确的索引被使用。
Only query one runs 15 minutes while the second query takes 0.031s. Why is that? Is there a central place to fix this or do I have to check all my queries for this column and make utterly sure that the trunc()is in there? How do I fix this issue when I need to select down to a certain second?
仅查询一个运行 15 分钟，而第二个查询需要 0.031 秒。这是为什么？是否有一个中心位置可以解决这个问题，或者我是否必须检查我对此列的所有查询并完全确定trunc()在那里？当我需要选择到某一秒时如何解决这个问题？
[EDIT] The table is partitioned and I'm on Oracle 10.2.0.
[编辑] 该表已分区，我在 Oracle 10.2.0 上。
采纳答案by CMG
I don't understand what {ts '2009-12-08 00:00:00.000'} actually mean, since this isn't Oracle SQL as far as I know. Can you show exactly what the query is you're running?
我不明白 {ts '2009-12-08 00:00:00.000'} 实际上是什么意思，因为据我所知，这不是 Oracle SQL。你能准确地显示你正在运行的查询吗？
One possible problem is that you're specifying your range with milliseconds. Oracle's DATE type only goes down to seconds. (Use TIMESTAMP type if you need to store fractions of seconds). But what might be happening is that in the first query, Oracle is converting each DATE value to a TIMESTAMP in order to do the comparison to your specified TIMESTAMP. In the second case, it knows TRUNC() will effectively round your value to something that can be expressed as a DATE, so no conversion is needed.
一个可能的问题是您用毫秒指定范围。Oracle 的 DATE 类型只能降到秒。（如果您需要存储几分之一秒，请使用 TIMESTAMP 类型）。但可能发生的情况是，在第一个查询中，Oracle 将每个 DATE 值转换为 TIMESTAMP，以便与您指定的 TIMESTAMP 进行比较。在第二种情况下，它知道 TRUNC() 将有效地将您的值四舍五入为可以表示为 DATE 的值，因此不需要转换。
If you want to avoid such implicit conversions, make sure you're always comparing like with like. eg
如果您想避免这种隐式转换，请确保您总是比较喜欢和喜欢。例如
select *
from my_table t
where t.ts between to_date('2009-12-08','YYYY-MM-DD') and to_date('2009-12-09','YYYY-MM-DD')
回答by FAB
This is because TIMESTAMP datatype is more accurate than DATE so when you supply TIMESTAMP parameter value into DATE column condition, Oracle has to convert all DATE values into TIMESTAMP to make a comparison (this is the INTERNAL_FUNCTION usage above) and therefore index has to be full scanned.
这是因为 TIMESTAMP 数据类型比 DATE 更准确，所以当您将 TIMESTAMP 参数值提供给 DATE 列条件时，Oracle 必须将所有 DATE 值转换为 TIMESTAMP 进行比较（这是上面的 INTERNAL_FUNCTION 用法），因此索引必须是完整的扫描。
回答by Lukas Eder
I have a similar problem here:
我在这里也有类似的问题：
Non-negligible execution plan difference with Oracle when using jdbc Timestamp or Date
使用 jdbc Timestamp 或 Date 时与 Oracle 不可忽略的执行计划差异
In my example it essentially comes down to the fact that when using JDBC Timestamp, an INTERNAL_FUNCTIONis applied to the filter column, not the bind variable. Thus, the index cannot be used for RANGE SCANSor UNIQUE SCANSanymore:
在我的示例中，它本质上归结为这样一个事实，即在使用 JDBC 时间戳时， anINTERNAL_FUNCTION应用于过滤器列，而不是绑定变量。因此，该索引不能用于RANGE SCANS或UNIQUE 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) With timestamps, I get an INDEX FULL SCANand thus a filter predicate
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) With dates, I get the much better INDEX RANGE SCANand an access predicate
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)
Solving this problem inside third-party APIs
在第三方 API 中解决这个问题
For the record, this problem can also be solved within third-party APIs for instance in Hibernate:
作为记录，这个问题也可以在第三方 API 中解决，例如在 Hibernate 中：
Or in jOOQ:
或者在 jOOQ 中：
回答by Angus
I had this problem on a project a while ago and setting the connection property oracle.jdbc.V8Compatible=true fixed the problem.
不久前我在一个项目上遇到了这个问题，设置连接属性 oracle.jdbc.V8Compatible=true 解决了这个问题。
Dougman's link tells you how to set it:
Dougman 的链接告诉你如何设置：
You set the connection property by adding it to the java.util.Properties object passed to DriverManager.getConnection or to OracleDataSource.setConnectionProperties. You set the system property by including a -D option in your java command line.
java -Doracle.jdbc.V8Compatible="true" MyApp
通过将连接属性添加到传递给 DriverManager.getConnection 或 OracleDataSource.setConnectionProperties 的 java.util.Properties 对象来设置连接属性。您可以通过在 java 命令行中包含 -D 选项来设置系统属性。
java -Doracle.jdbc.V8Compatible="true" MyApp
Note for 11g and this property is apparently not used.
注意 11g 和这个属性显然没有被使用。
From http://forums.oracle.com/forums/thread.jspa?messageID=1659839:
来自http://forums.oracle.com/forums/thread.jspa?messageID=1659839：
One additional note for those who are using the 11gR1 (and on) JDBC thin driver: the V8Compatible connection property no longer exist, so you can't rely on that to send your java.sql.Timestamp as a SQLDATE. What you can do however is call:
setObject(i, aTimestamp, java.sql.Types.DATE) sends data as SQLDATE setObject(i, aDate) sends data as SQLDATE setDate(i, aDate) sends data as SQLDATE setDATE(i, aDATE) (non standard) sends data as SQLDATE setObject(i, aTimestamp) sends data as SQLTIMESTAMP setTimestamp(i, aTimestamp) sends data as SQLTIMESTAMP setObject(i, aTimestamp) sends data as SQLTIMESTAMP setTIMESTAMP(i, aTIMESTAMP) (non standard) sends data as SQLTIMESTAMP
对使用 11gR1（及更高版本）JDBC 瘦驱动程序的人的附加说明：V8Compatible 连接属性不再存在，因此您不能依赖它来将您的 java.sql.Timestamp 作为 SQLDATE 发送。但是，您可以做的是调用：
setObject(i, aTimestamp, java.sql.Types.DATE) sends data as SQLDATE setObject(i, aDate) sends data as SQLDATE setDate(i, aDate) sends data as SQLDATE setDATE(i, aDATE) (non standard) sends data as SQLDATE setObject(i, aTimestamp) sends data as SQLTIMESTAMP setTimestamp(i, aTimestamp) sends data as SQLTIMESTAMP setObject(i, aTimestamp) sends data as SQLTIMESTAMP setTIMESTAMP(i, aTIMESTAMP) (non standard) sends data as SQLTIMESTAMP
