oracle优化日期区间,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占用比第二中少。

结论是：

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