[20130411]timestamp資料型別與運算.txt

[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))