[20130411]timestamp数据类型与运算.txt
昨天查看sql语句,发现程序员使用日期类型有问题,使用了timestamp数据类型。
拿scott.emp表作为例子来说明:
create index i_emp_hiredate on emp(hiredate) ;
在hiredate上建立索引。
--可以发现由于使用变量为timestamp类型,导致存在隐式转换,不可能使用索引。
--但是我觉得奇怪的是如果加入运算,问题消失。
--有点不好理解,通过运算,应该类型不变才对!可以通过这个方式来快速修改代码。
--再写几个例子:
昨天查看sql语句,发现程序员使用日期类型有问题,使用了timestamp数据类型。
拿scott.emp表作为例子来说明:
create index i_emp_hiredate on emp(hiredate) ;
在hiredate上建立索引。
SQL> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SQL> variable x varchar2(40)
SQL> exec :x := '1980-12-17 00:00:00';
PL/SQL procedure successfully completed.
SQL> select * from emp where hiredate = to_timestamp(:x,'yyyy-mm-dd hh24:mi:ss') ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
SQL> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID 5wx35kzbmxmu8, child number 0
-------------------------------------
select * from emp where hiredate = to_timestamp(:x,'yyyy-mm-dd
hh24:mi:ss')
Plan hash value: 3956160932
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
|* 1 | TABLE ACCESS FULL| EMP | 1 | 3 (0)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("HIREDATE")=TO_TIMESTAMP(:X,'yyyy-mm-dd
hh24:mi:ss'))
--可以发现由于使用变量为timestamp类型,导致存在隐式转换,不可能使用索引。
--但是我觉得奇怪的是如果加入运算,问题消失。
SQL> select * from emp where hiredate = to_timestamp(:x,'yyyy-mm-dd hh24:mi:ss')+0 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
SQL> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID fb2pd2pbgx2q9, child number 0
-------------------------------------
select * from emp where hiredate = to_timestamp(:x,'yyyy-mm-dd
hh24:mi:ss')+0
Plan hash value: 3350871025
----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 2 (0)|
|* 2 | INDEX RANGE SCAN | I_EMP_HIREDATE | 1 | 1 (0)|
----------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (VARCHAR2(30), CSID=852): '1980-12-17 00:00:00'
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("HIREDATE"=TO_TIMESTAMP(:X,'yyyy-mm-dd hh24:mi:ss')+0)
--有点不好理解,通过运算,应该类型不变才对!可以通过这个方式来快速修改代码。
--再写几个例子:
SQL> select * from emp where hiredate = to_timestamp(:x,'yyyy-mm-dd hh24:mi:ss')+interval '0' second ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
SQL> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID fnk6fn50ybfmb, child number 0
-------------------------------------
select * from emp where hiredate = to_timestamp(:x,'yyyy-mm-dd
hh24:mi:ss')+interval '0' second
Plan hash value: 3956160932
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
|* 1 | TABLE ACCESS FULL| EMP | 1 | 3 (0)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("HIREDATE")=TO_TIMESTAMP(:X,'yyyy-mm-dd
hh24:mi:ss')+INTERVAL'+00 00:00:00.000000' DAY(2) TO SECOND(6))
--这样写不行。
SQL> select * from emp where hiredate = to_date(:x,'yyyy-mm-dd hh24:mi:ss')+interval '0' second ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
SQL> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 6xvckc7tbhudj, child number 0
-------------------------------------
select * from emp where hiredate = to_date(:x,'yyyy-mm-dd
hh24:mi:ss')+interval '0' second
Plan hash value: 3350871025
----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 2 (0)|
|* 2 | INDEX RANGE SCAN | I_EMP_HIREDATE | 1 | 1 (0)|
----------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (VARCHAR2(30), CSID=852): '1980-12-17 00:00:00'
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("HIREDATE"=TO_DATE(:X,'yyyy-mm-dd
hh24:mi:ss')+INTERVAL'+00 00:00:00.000000' DAY(2) TO SECOND(6))
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-758233/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-758233/