oracle INTERNAL_FUNCTION

当执行计划出现INTERNAL_FUNCTION的时候索引肯定失效，这里可以理解为隐士转换。
我们来看看官方文档，oracle叫他 

Function Calls

For example, consider the following query:

EXPLAIN PLAN FOR
SELECT SUM(quantity_sold)
FROM sales
WHERE time_id = TO_TIMESTAMP('1-jan-2000', 'dd-mon-yyyy');

Because time_id is of type DATE and Oracle must promote it to the TIMESTAMP type to get the same data type, this predicate is internally rewritten as:

TO_TIMESTAMP(time_id) = TO_TIMESTAMP('1-jan-2000', 'dd-mon-yyyy')

The execution plan for this statement is as follows:

--------------------------------------------------------------------------------------------
|Id | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT     |       |     1 |    11 |     6  (17)| 00:00:01 |       |       |
| 1 |  SORT AGGREGATE      |       |     1 |    11 |            |          |       |       |
| 2 |   PARTITION RANGE ALL|       |    10 |   110 |     6  (17)| 00:00:01 |     1 |    16 |
|*3 |    TABLE ACCESS FULL | SALES |    10 |   110 |     6  (17)| 00:00:01 |     1 |    16 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(INTERNAL_FUNCTION("TIME_ID")=TO_TIMESTAMP('1-jan-2000',:B1))
 
15 rows selected

我们做一下测试：
SQL> create table testdt (dt date);
Table created.
SQL> insert into testdt values(sysdate);
1 row created.
SQL> set autotrace on
SQL> select * from testdt where dt=TO_TIMESTAMP('1-jan-2000', 'dd-mon-yyyy');
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3876087351


----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |     9 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TESTDT |     1 |     9 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------


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


   1 - filter(INTERNAL_FUNCTION("DT")=TIMESTAMP' 2000-01-01
              00:00:00.000000000')



SQL> select * from testdt where dt=to_date('1-jan-2000', 'dd-mon-yyyy');


no rows selected




Execution Plan
----------------------------------------------------------
Plan hash value: 3876087351


----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |     9 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TESTDT |     1 |     9 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DT"=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

虽然语法没有问题但是要注意
date 类型用to_date 

timestamp类型用to_timestamp 

再看如下的例子：
SQL> select * from testdt where to_char(dt,'YYYY')='2016';
DT
---------
05-AUG-16
Execution Plan
----------------------------------------------------------
Plan hash value: 3876087351


----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |     9 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TESTDT |     1 |     9 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_CHAR(INTERNAL_FUNCTION("DT"),'YYYY')='2016')

我们可以用如下代替：
SQL> select * from testdt where dt>=to_date('2016','yyyy') ;

DT
---------
05-AUG-16

Execution Plan
----------------------------------------------------------
Plan hash value: 3876087351
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |     9 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TESTDT |     1 |     9 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter("DT">=TO_DATE('2016','yyyy'))

Note
-----
   - dynamic sampling used for this statement (level=2)

当然情况还有很多这里不在举例，但是遇到这种情况一定要从数据是否匹配入手去检查

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

转载于:http://blog.itpub.net/7728585/viewspace-2122996/