1. 首页
  2. IT资讯

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://www.cxybcw.com/183638.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code