为什么当我传递一个DATE列的java.sql.Timestamp时，Oracle这么慢？ [英] Why is Oracle so slow when I pass a java.sql.Timestamp for a DATE column?

问题描述

我有一个具有时间的 DATE 列的表（像Oracle一样，因为没有一个 TIME 类型）。当我从JDBC查询该列时，我有两个选项：

• 使用Oracle的 to_date（）手动转换值，


• 使用 java.sql.Timestamp

这两种方法都有作用，并且具有独特的隐藏区域。我的问题是当我是 SELECT ing数据。以下是两个示例查询：

  select * 
 from TABLE 
其中TS在{ts'2009- 12-08 00：00：00.000'}和{ts'2009-12-09 00：00：00.000'} 
 
从TABLE 
中选择* 
其中TS在trunc （{ts'2009-12-08 00：00.000'}）和trunc（{ts'2009-12-09 00：00：00.000'}）
   EXPLAIN PLAN 中产生完全相同的输出。使用这个正确的索引。
 
 
 
只有查询一个运行15分钟，而第二个查询需要0.031秒。这是为什么？是否有一个中心的地方来解决这个问题，或者我必须检查我对这一列的所有查询，并确保 trunc（）在那里？ 
 
 
 
 表是分区的，我在Oracle 10.2.0上。 / p> 
 
解决方案
我不明白{ts'2009-12-08 00：00：00.000'}是什么意思，因为这个据我所知，不是Oracle SQL。您能否正确显示您正在运行的查询？
 
 
 
一个可能的问题是您以毫秒指定范围。 Oracle的DATE类型只能下降到几秒钟。 （如果需要存储分数秒，请使用TIMESTAMP类型）。但是可能发生的情况是，在第一个查询中，Oracle将每个DATE值转换为TIMESTAMP，以便与指定的TIMESTAMP进行比较。 
在第二种情况下，它知道TRUNC（）将有效地将您的值转换为可以表示为DATE的值，因此不需要转换。
 
 
 
如果你想避免这种隐含的转换，那么确保你总是和...一样比较。 
例如
  select * 
 from my_table t 
其中t.ts之间to_date（' 2009-12-08'，'YYYY-MM-DD'）和to_date（'2009-12-09'，'YYYY-MM-DD'）
  
 
I have a table with a DATE column with time (as usual in Oracle since there isn't a TIME type). When I query that column from JDBC, I have two options:


Manually convert the values with Oracle's to_date()
Use a 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 *
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.

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?

[EDIT] The table is partitioned and I'm on Oracle 10.2.0.
解决方案
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?

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.

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')


                        
这篇关于为什么当我传递一个DATE列的java.sql.Timestamp时，Oracle这么慢？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！