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占用比第二中少。
结论是:
涉及到时间的比较最好转化成可以使用索引,避免全表扫描。
分享到:
相关推荐
oracle性能优化中SQL语句优化,简单的优化思想、常见的优化命令
Oracle Sql语句转换成Mysql Sql语句java 源码,非常简单,只要给定源oracle sql语句地址,和生成目标文件地址运行即可。
Oracle——sql语句优化
oracle中SQL语句优化
ORACLEsql语句优化,性能优化,语句技巧优化
SQL优化 SQL优化软件 SQL优化工具 很好用的工具,可以分析优化TSQL语句,oracle数据库语句优化工具
ORACLE数据库SQL语句编写优化总结文档
ORACLE数据库优化之SQL语句的并行处理.pdf
Oracle数据库设计中SQL语句优化研究.doc
基于Oracle数据库的SQL语句优化.
SQL_Server,Oracle,DB2数据库SQL语句比较
描述对sql语句的优化达到优化oracle的目的
Oracle SQL 语句优化53个规则详解
oracle sql语句优化,sql语句优化策略和方法以及实例
查询ORACLE 系统中当前会话正在执行的有关SQL语句。
Oracle SQL语句性能优化
通过分析SQL语句的执行计划优化SQL,F5执行计划如何优化
Oracle sql语句优化规则汇总
sql语句优化.chmmssql优化 mysql优化 oracle优化