ITPub博客

首页 > 数据库 > Oracle > [20131204]sql语句优化.txt

[20131204]sql语句优化.txt

原创 Oracle 作者:lfree 时间:2013-12-04 11:41:23 0 删除 编辑
[20131204]sql语句优化.txt

昨天优化sql语句,遇到一些细节问题,做一个记录:

SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create table t (id number,create_date date,pad varchar2(80));
create index i_t_create_date on t(create_date);

SCOTT@test> select /*+ index(t i_t_create_date) */ * from t where create_date>=trunc(sysdate) and  create_date>sysdate - 6/1440;
no rows selected

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  6tqcjs9bj623v, child number 0
-------------------------------------
select /*+ index(t i_t_create_date) */ * from t where
create_date>=trunc(sysdate) and  create_date>sysdate - 6/1440

Plan hash value: 2174186695

-----------------------------------------------------------------------------
| Id  | Operation                   | Name            | E-Rows | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |        |     1 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T               |      1 |     1   (0)|
|*  2 |   INDEX RANGE SCAN          | I_T_CREATE_DATE |      1 |     1   (0)|
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CREATE_DATE">=TRUNC(SYSDATE@!))
       filter("CREATE_DATE">SYSDATE@!-.004166666666666666666666666666666
              666666667)


SCOTT@test> select /*+ index(t i_t_create_date) */ * from t where create_date>sysdate - 6/1440 and create_date>=trunc(sysdate);
no rows selected

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  fcc58jafh38ym, child number 0
-------------------------------------
select /*+ index(t i_t_create_date) */ * from t where
create_date>sysdate - 6/1440 and create_date>=trunc(sysdate)

Plan hash value: 2174186695

-----------------------------------------------------------------------------
| Id  | Operation                   | Name            | E-Rows | Cost (%CPU)|
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |        |     1 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T               |      1 |     1   (0)|
|*  2 |   INDEX RANGE SCAN          | I_T_CREATE_DATE |      1 |     1   (0)|
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CREATE_DATE">SYSDATE@!-.004166666666666666666666666666666
              666666667)
       filter("CREATE_DATE">=TRUNC(SYSDATE@!))


-- 先是感觉奇怪的是两者写法,为什么access的条件不一样。开始感觉第2种写法应该快一些,正常的业务这样扫描的日期范围窄一些。
-- oracle优化器应该能作出正确的选择,后来想起来以前遇到的问题,我给它起一个名字叫"零点魔鬼",在凌晨切换日期时
-- 程序就有问题了。
SELECT trunc(to_date('2013-12-05 00:01:01','yyyy-mm-dd hh24:mi:ss')) a1,
       to_date('2013-12-05 00:01:01','yyyy-mm-dd hh24:mi:ss') a2,
       to_date('2013-12-05 00:01:01','yyyy-mm-dd hh24:mi:ss') -8/1440 a3
FROM dual ;

A1                  A2                  A3
------------------- ------------------- -------------------
2013-12-05 00:00:00 2013-12-05 00:01:01 2013-12-04 23:53:01

-- 很明显在凌晨执行时,日期范围越来越小,到0点6分后业务在正常。
-- 修改很简单,删除 create_date>=trunc(sysdate)条件。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1062126/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2674
  • 访问量
    6433362