timestamp类型转换导致不走索引的测试

在生产库上遇到一个sql走的是FTS，在一个选择性比较好的列上创建了索引后，但一直没有走索引，经过检查发现在执行计划里始终有个内部的数据类型转换的操作，
(INTERNAL_FUNCTION("B")=.........,基本可以判定是由于这个内部转换的函数，导致索引没有生效，查了一下，网上有个帖子跟这个有关系：
http://www.orafaq.com/forum/t/151353/0/，可能是oracle认为前端应用程序中输入的绑定变量是timestamp类型而导致的类型转换，做个测试：

SQL> desc t;
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 A                                                                                 NUMBER(10)
 B                                                                                 DATE

SQL> select table_name,index_name,column_position from user_ind_columns where table_name='T';

TABLE_NAME                     INDEX_NAME                     COLUMN_POSITION
------------------------------ ------------------------------ ---------------
T                              IDX_T                                        1

b为date类型，当用应用程序前端输入的值为date类型时，不会发生类型转换，因而能够走索引：

SQL> select * from t where b=to_date('20091202 12:00:00','yyyymmdd hh24:mi:ss');

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    12 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    12 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("B"=TO_DATE('2009-12-02 12:00:00', 'yyyy-mm-dd hh24:mi:ss'))

但如果前端传入的为timestamp类型的数据时，oracle将会自动对b列进行数据类型转换，将b转换为timestamp类型，此时将不走索引：

SQL> select * from t where b=to_timestamp('20091202 12:00:00','yyyymmdd hh24:mi:ss');

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 | 12000 |   418   (2)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| T    |  1000 | 12000 |   418   (2)| 00:00:06 |
--------------------------------------------------------------------------

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

   1 - filter(INTERNAL_FUNCTION("B")=TO_TIMESTAMP('20091202
              12:00:00',:B1))

将date类型的列改为timestamp类型，可以解决此问题：

SQL> alter table t modify b timestamp;

Table altered.

SQL> set autot traceonly explain
SQL> select * from t where b=to_timestamp('20091202 12:00:00','yyyymmdd hh24:mi:ss');

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  4348 | 52176 |    23   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |  4348 | 52176 |    23   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T |  4348 |       |    12   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("B"=TO_TIMESTAMP('20091202 12:00:00',:B1))

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

转载于:http://blog.itpub.net/10972173/viewspace-623204/