[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)条件。
昨天优化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)条件。
