oracle含日期比较的sql语句的优化

oracle的时间和日期比较功能比较强大,它提供了几个函数：比如to_char()和to_date()

但是如果在sql语句的时间比较中包含这两个函数，那么sql的效率会是非常的底下。

下面是三种时间比较的sql语句：

在sqlplus中

执行：

set timing on;

set autotrace traceonly;

分别执行以下三种语句：

 

一。SELECT "V_HEADQUARTER_HW_LIST"."ID",
       "V_HEADQUARTER_HW_LIST"."RQ_OPTION"
  FROM "V_HEADQUARTER_HW_LIST"
 WHERE (rq_option >= to_date('2007-09-01', 'yyyy.mm.dd') AND
       rq_option < to_date('2007-09-02', 'yyyy.mm.dd'))

结果如下：

172 rows selected.

 real: 250

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'W_SHDJ_MX_ZD'
   2    1     INDEX (RANGE SCAN) OF 'W_SHDJ_MX_ZD_RQ_OPTION_D
          UNIQUE)

Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
       2870  consistent gets
          0  physical reads
          0  redo size
       6350  bytes sent via SQL*Net to client
       2076  bytes received via SQL*Net from client
         15  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        172  rows processed

二。

SELECT "V_HEADQUARTER_HW_LIST"."ID",
       "V_HEADQUARTER_HW_LIST"."RQ_OPTION"
  FROM "V_HEADQUARTER_HW_LIST"
WHere
(to_char(rq_option, 'yyyy.mm.dd') = '2007.09.01')

结果如下：

172 rows selected.

 real: 19656

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'W_SHDJ_MX_ZD'
Statistics
----------------------------------------------------------
          7  recursive calls
          3  db block gets
     207025  consistent gets
     107946  physical reads
          0  redo size
       6350  bytes sent via SQL*Net to client
       2015  bytes received via SQL*Net from client
         15  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        172  rows processed
三。

SELECT "V_HEADQUARTER_HW_LIST"."ID",
       "V_HEADQUARTER_HW_LIST"."RQ_OPTION"
  FROM "V_HEADQUARTER_HW_LIST"
where
trunc(rq_option, 'DD') = to_date('2007.09.01','yyyy.mm.dd')

172 rows selected.

 real: 20531

Execution Plan
---------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'W_SHDJ_MX_ZD'
Statistics
---------------------------------------------------
          7  recursive calls
          3  db block gets
     207022  consistent gets
     108024  physical reads
          0  redo size
       6350  bytes sent via SQL*Net to client
       2025  bytes received via SQL*Net from client
         15  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        172  rows processed
可以看出，第一种的效率比另外两种高。

但是根据网上有人的测试，第三种的CPU占用比第二中少。

结论是：

涉及到时间的比较最好转化成可以使用索引，避免全表扫描。