如何优化在日期的 where 子句中具有 to_char 的 Oracle 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2614418/
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
How to optimize an Oracle query that has to_char in where clause for date
提问by Josh
I have a table that contains about 49403459records.
我有一个包含关于49403459记录的表。
I want to query the table on a date range. say 04/10/2010to 04/10/2010. However, the dates are stored in the table as format 10-APR-10 10.15.06.000000 AM(time stamp).
我想在日期范围内查询表。说04/10/2010来04/10/2010。但是，日期作为格式10-APR-10 10.15.06.000000 AM（时间戳）存储在表中。
As a result when I do
结果当我做
SELECT bunch,of,stuff,create_date
FROM myTable
WHERE TO_CHAR (create_date,'MM/DD/YYYY)' >= '04/10/2010'
AND TO_CHAR (create_date, 'MM/DD/YYYY' <= '04/10/2010'
I get 529rows but in 255.59seconds! Which is because I guess I am doing TO_CHAR on EACH record.
我得到529行，但在255.59几秒钟内！这是因为我想我在 EACH 记录上做 TO_CHAR。
However, when I do
但是，当我这样做时
SELECT bunch,of,stuff,create_date
FROM myTable
WHERE create_date >= to_date('04/10/2010','MM/DD/YYYY')
AND create_date <= to_date('04/10/2010','MM/DD/YYYY')
then I get 0results in 0.14seconds.
然后我0在0.14几秒钟内得到结果。
How can I make this query fast and still get valid (529) results?
如何快速执行此查询并仍然获得有效的 ( 529) 结果？
At this point I can not change indexes. Right now I think index is created on create_datecolumn.
此时我无法更改索引。现在我认为索引是在create_date列上创建的。
How can I convert the two date ranges so that first date range gets converted to time stamp with all 0's and the second one gets converted to time stamp that is the last time stamp of the date. If that makes sense...?
如何转换两个日期范围，以便第一个日期范围转换为全 0 的时间戳，而第二个日期范围转换为时间戳，即日期的最后一个时间戳。如果这是有道理的......？
The following where clause fetches no results either:
以下 where 子句也不会获取任何结果：
WHERE
create_date >= to_timestamp('04/10/2010 00:00:00.000000','MM/DD/YYYY HH24:MI:SS.FF')
AND
create_date <= to_timestamp('04/10/2010 00:00:00.000000','MM/DD/YYYY HH24:MI:SS.FF')
回答by APC
I get 529 rows but in 255.59 seconds! Which is because I guess I am doing TO_CHAR on EACH record.
我得到 529 行，但在 255.59 秒内！这是因为我想我在 EACH 记录上做 TO_CHAR。
If you were to generate an execution planfor your first query ...
如果您要为第一个查询生成执行计划...
explain plan for
SELECT bunch,of,stuff,create_date
FROM myTable
WHERE TO_CHAR (create_date,'MM/DD/YYYY)' >= '04/10/2010'
AND TO_CHAR (create_date, 'MM/DD/YYYY') <= '04/10/2010'
/
... you would see that it does a full table scan. That's because the to_char()prevents the use of your index on CREATE DATE.
...您会看到它进行了全表扫描。那是因为to_char()防止在 CREATE DATE 使用您的索引。
You don't say how long it took to return the results when you ran ...
你没说跑了多久才返回结果...
SELECT bunch,of,stuff,create_date
FROM myTable
WHERE
create_date >= to_timestamp('04/10/2010 00:00:00.000000','MM/DD/YYYY HH24:MI:SS.FF')
AND
create_date <= to_timestamp('04/10/2010 23:59:59:123000','MM/DD/YYYY HH24:MI:SS.FF')
/
... but I expect it was way closer to 0.14 seconds than 4 minutes.
...但我预计它比 4 分钟更接近 0.14 秒。
回答by Igby Largeman
Of course this doesn't work:
当然这是行不通的：
WHERE
create_date >= to_timestamp('04/10/2010 00:00:00.000000','MM/DD/YYYY HH24:MI:SS.FF')
AND
create_date <= to_timestamp('04/10/2010 00:00:00.000000','MM/DD/YYYY HH24:MI:SS.FF')
Because that would only return rows where the create_date is 4/10/2010 12:00 AM exactly!
因为那只会返回 create_date正好是 4/10/2010 12:00 AM 的行！
If you want to get all rows where create_date occurs any time on the day of 4/10/2010, use this:
如果要获取 2010 年 4 月 10 日当天任何时间发生 create_date 的所有行，请使用以下命令：
WHERE
create_date >= to_timestamp('04/10/2010 00:00:00.000000','MM/DD/YYYY HH24:MI:SS.FF')
AND
create_date < to_timestamp('04/11/2010 00:00:00.000000','MM/DD/YYYY HH24:MI:SS.FF')
OR if you prefer:
或者，如果您愿意：
WHERE create_date BETWEEN to_timestamp('04/10/2010 00:00:00.000000','MM/DD/YYYY HH24:MI:SS.FF')
AND to_timestamp('04/10/2010 23:59:59.999999','MM/DD/YYYY HH24:MI:SS.FF')
By the way, when you want to represent midnight, you can leave all the other parts out. So you could just say:
顺便说一句，当你想代表午夜时，你可以把所有其他部分都去掉。所以你可以说：
WHERE
create_date >= to_timestamp('04/10/2010','MM/DD/YYYY')
AND
create_date < to_timestamp('04/11/2010','MM/DD/YYYY')
回答by newdayrising
In your first query, you are doing a character comparison rather than a date comparison, which should not be producing correct results.
在您的第一个查询中，您进行的是字符比较而不是日期比较，这不应该产生正确的结果。
For example, using your logic, 01/02/2009will be greater than 01/01/2010because the day component '02' is greater than the day component '01' when comparing characters and the year will never be evaluated.
例如，使用您的逻辑，01/02/2009将大于，01/01/2010因为在比较字符时，日期组件 ' 02' 大于日期组件 ' 01'，并且永远不会评估年份。
回答by Josh
This works:
这有效：
WHERE
create_date >= to_timestamp('04/10/2010 00:00:00.000000','MM/DD/YYYY HH24:MI:SS.FF')
AND
create_date <= to_timestamp('04/10/2010 23:59:59:123000','MM/DD/YYYY HH24:MI:SS.FF')
回答by Adam Musch
SELECT bunch,of,stuff,create_date
FROM myTable
WHERE create_date >= to_date('04/10/2010','MM/DD/YYYY')
AND create_date < to_date('04/11/2010','MM/DD/YYYY')
The date 04/10/2010 includes all the date values from midnight on the 10th until 11:59:59 PM, so getting everything less than the 11th will cover all the bases. An alternative is to ensure data in myTable has the CREATE_DATE field truncated on data entry; I prefer to do that for DATE fields, and if I care about the time components, I use TIMESTAMPs.
日期 04/10/2010 包括从 10 号午夜到晚上 11:59:59 的所有日期值，因此获取小于 11 号的所有内容将涵盖所有基数。另一种方法是确保 myTable 中的数据在数据输入时截断 CREATE_DATE 字段；我更喜欢对 DATE 字段这样做，如果我关心时间组件，我会使用 TIMESTAMP。
回答by jazzdup
Your 1st query is doing a string comparison with wrong results. Your 2nd query needs to be:
您的第一个查询正在对错误结果进行字符串比较。您的第二个查询需要是：
WHERE create_date >= TRUNC(to_date('04/10/2010','MM/DD/YYYY'))
WHERE create_date >= TRUNC(to_date('04/10/2010','MM/DD/YYYY'))
or add hh:mi:ss to the predicate. It's not working simply 'cos you're formatting the date in a different way to what Oracle expects.
或将 hh:mi:ss 添加到谓词。它不能简单地工作，因为您以与 Oracle 期望的不同的方式格式化日期。
