[20130411]timestamp数据类型与运算.txt

昨天查看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))