如何优化在date的where子句中具有to_char的Oracle查询 [英] How to optimize an Oracle query that has to_char in where clause for date

问题描述

我有一张表，其中包含大约$ code> 49403459 记录。

我想在一个日期查询表范围。说 04/10/2010 to 04/10/2010 。但是，日期以格式 10-APR-10 10.15.06.000000 AM （时间戳）存储在表格中。

结果当我做

  SELECT束，的，东西，create_date 
从myTable 
 WHERE TO_CHAR（create_date，'MM / DD / YYYY）'> = '04 / 10/2010'
 AND TO_CHAR（create_date，'MM / DD / YYYY'< = '04 / 10 / 
  

我得到 529 行，但在code> 255.59 秒！这是因为我猜我在每个记录上都做TO_CHAR。

然而，当我做

  SELECT rows，of，stuff，create_date 
 FROM myTable 
 WHERE create_date> = to_date（'04 / 10 / $$$ / $$$$$$$$$ 

然后我得到 0 结果 0.14 秒。

如何快速查询此查询，并仍然有效（ 529 ）结果？

此时我无法更改索引。现在我认为索引是在 create_date 列上创建的。

如何将两个日期范围转换为第一个日期范围被转换为所有0的时间戳，第二个被转换为时间戳，该时间戳是日期的最后一个时间戳。如果这样做是有道理的？

以下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'）
  / pre> 
 
解决方案
 
 
 
我得到529行，但在255.59秒！ 
这是因为我想我在每个记录上都做
 TO_CHAR。
 
 
如果你是为您的第一个查询生成执行计划 
  
的解释计划SELECT rows，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 / 
 / 
  
 ...您会看到它执行了全表扫描。这是因为 to_char（）阻止在CREATE DATE使用您的索引。 
 
 
 
您不会说运行时返回结果需要花费多长时间。
  SELECT rows，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'）
 / 
  
 ...但我预计比4分钟更接近0.14秒。
 
I have a table that contains about 49403459 records.

I want to query the table on a date range. say 04/10/2010 to 04/10/2010.  However, the dates are stored in the table as format 10-APR-10 10.15.06.000000 AM (time stamp).

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 529 rows but in 255.59 seconds! Which is because I guess I am doing TO_CHAR on EACH record. 

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 0 results in 0.14 seconds.  

How can I make this query fast and still get valid (529) results?

At this point I can not change indexes. Right now I think index is created on create_date column.

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...?

The following where clause fetches no results either:
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')

解决方案

  
I get 529 rows but in 255.59 seconds!
  Which is because I guess I am doing
  TO_CHAR on EACH record.
If you were to generate an execution plan for 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.  

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.

                        
这篇关于如何优化在date的where子句中具有to_char的Oracle查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！