performance - 如何优化在日期的 where 子句中具有 to_char 的 Oracle 查询

Question

我有一个包含 49403459 记录的表。

我想在日期范围内查询表。说 04/10/2010 到 04/10/2010 。但是，日期以 10-APR-10 10.15.06.000000 AM(时间戳)格式存储在表中。

结果当我做

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'

我得到 529 行，但在 255.59 秒内!这是因为我想我在 EACH 记录上做 TO_CHAR。

但是，当我这样做时

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

然后我在 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')

Answer 1

I get 529 rows but in 255.59 seconds! Which is because I guess I am doing TO_CHAR on EACH record.

如果您要 generate an execution plan 进行第一次查询...

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

...您会看到它进行了全表扫描。那是因为 to_char() 阻止在 CREATE DATE 使用您的索引。

你没说跑多久才返回结果...

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

...但我预计它比 4 分钟更接近 0.14 秒。