从 java 运行查询时不使用 oracle 中日期类型列的索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1713248/
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
Index on date type column in oracle not used when query is run from java
提问by Ali
i have a table containing 15+ million records in oracle. its sort of a log table which has a created_ts column of type "date" . i have a simple "non-unique" type index on created_ts column.
我在 oracle 中有一个包含 15+ 百万条记录的表。它是一种日志表，它有一个 created_ts 类型的“date”列。我在 created_ts 列上有一个简单的“非唯一”类型索引。
i have a simple range query :
我有一个简单的范围查询：
select * from table1 where created_ts >= ? and created_ts <= ?;
when i run this query from SQLPlus or SQL Developer etc like this :
当我从 SQLPlus 或 SQL Developer 等运行此查询时：
select * from table1
where created_ts >= TO_DATE( '2009-11-10 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and created_ts <= TO_DATE( '2009-11-10 23:59:59', 'YYYY-MM-DD HH24:MI:SS');
the query returns within 1-2 second max.
查询最多在 1-2 秒内返回。
but when I run the exact same query in java over JDBC and set the corresponding "?" params using java.sql.Timestamp object . the query takes long time . Analyzing the oracle process it goes for full table scan and doesnt use the index.
但是当我通过 JDBC 在 java 中运行完全相同的查询并设置相应的“？” 参数使用 java.sql.Timestamp 对象。查询需要很长时间。分析oracle进程，它进行全表扫描，不使用索引。
the jdbc driver i am using is ojdbc5 11.1.0.7.0
我使用的 jdbc 驱动程序是 ojdbc5 11.1.0.7.0
Can any one please help .. how to create the index correctly so that it uses the index.
任何人都可以帮忙..如何正确创建索引以便它使用索引。
My problem was resolved when i used "oracle.sql.DATE" objects to set the bind variables instead of "java.sql.timestamp" . The query used the index and executed almost within 1-2 seconds.
当我使用 "oracle.sql.DATE" 对象来设置绑定变量而不是 "java.sql.timestamp" 时，我的问题得到了解决。查询使用了索引并几乎在 1-2 秒内执行。
Thanks to all who replied and helped.
感谢所有回答和帮助的人。
But its problematic for me as this solution is DB dependent and my app receives DB connection and query as param and load and process data in a generic way. The DB connection can be of any RDBMS like oracle, mysql, etc.
但它对我来说是有问题的，因为这个解决方案依赖于数据库，我的应用程序接收数据库连接和查询作为参数，并以通用方式加载和处理数据。DB 连接可以是任何 RDBMS，如 oracle、mysql 等。
采纳答案by APC
This is classic behaviour for an implicit datatype conversion. Because the database is having to convert the datatype of the column it cannot use any index on that column.
这是隐式数据类型转换的经典行为。因为数据库必须转换列的数据类型，所以它不能在该列上使用任何索引。
In your case I suspect this is due to your use of java.sql.Timestamp. Would it be possible to use the equivalent type from the Oracle datatypes package, oracle.sql.Timestamp? Obviously that may have some knock-on effects but I think you should at least test it, to see whether that solves your problem.
在您的情况下，我怀疑这是由于您使用java.sql.Timestamp. 是否可以从使用的同等类型的Oracle数据包，oracle.sql.Timestamp？显然，这可能会产生一些连锁反应，但我认为您至少应该对其进行测试，看看是否能解决您的问题。
回答by WW.
The difference may because of bind variables vs. literal values. You are not comparing the same things.
差异可能是因为绑定变量与文字值。你不是在比较相同的东西。
Try this in SQL*Plus:-
在 SQL*Plus 中试试这个：-
explain plan for
select * from table1 where created_ts >= :1 and created_ts <= :2;
set markup html preformat on
set linesize 100
set pagesize 0
select plan_table_output
from table(dbms_xplan.display('plan_table',null,'serial'));
This will show you the plan Oracle will pick when using bind variables. In this scenario, Oracle has to make up a plan before you have provided values for your date range. It does not know if you are selecting only a small fraction of the data or all of it. If this has the same plan (full scan?) as your plan from java, at least you konw what is happening.
这将显示 Oracle 在使用绑定变量时将选择的计划。在这种情况下，Oracle 必须在您为日期范围提供值之前制定计划。它不知道您是只选择了数据的一小部分还是全部。如果这与您的 Java 计划具有相同的计划（完整扫描？），至少您知道发生了什么。
Then, you could consider:-
那么，你可以考虑：-
- Enabling bind peeking (but only after testing this does not cause anything else to go bad)
- Carefully binding literal values from java in a way that does not allow SQL injection
- Putting a hint in the statement to indicate it should use the index you want it to.
- 启用绑定窥视（但只有在测试后才不会导致其他任何问题）
- 以不允许 SQL 注入的方式小心地绑定来自 java 的文字值
- 在语句中放置一个提示以指示它应该使用您想要的索引。
回答by Alan
You should try a hint of the form /*+ USE_INDEX(table_name, index_name) */
您应该尝试使用 /*+ USE_INDEX(table_name, index_name) */
My guess is that the optimizer is choosing a full table scan because it sees that as the best option in absence of knowing the bind values.
我的猜测是优化器正在选择全表扫描，因为它认为这是在不知道绑定值的情况下的最佳选择。
