本文链接地址：http://blog.chinaunix.net/uid-31396856-id-5780032.html
我们有时候会遇到，SQL传递参数的数据类型与表索引字段类型不一致，而无法走索引
SQL> desc test1
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> desc test2
Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID VARCHAR2(100)
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
OBJECT_ID2 NUMBER
以上两张测试表，test1和test2。
其中test1的object_id为NUMBER，test2表的object_id是VARCHAR2，和test1表的object_id数据类型不一致。
object_id2是NUMBER，和test1表的object_id数据类型一致
对test2表的object_id和object_id2两个字段都创建了索引
SQL> CREATE INDEX ind_test2_id1 ON test2(object_id);
Index created.
SQL> CREATE INDEX ind_test2_id2 ON test2(object_id2);
Index created.
我们先来看看TEST2的object_id和TEST1中的object_id数据类型一致的情况
SQL>
2 UPDATE test1 t
3 SET a.object_type =
4 (SELECT i.object_type FROM test2 i WHERE i.object_id2 = t.object_id);
Explained.
看看执行计划
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 53732 | 1259K| 154 (2)| 00:00:02 |
| 1 | UPDATE | TEST1 | | | | |
| 2 | TABLE ACCESS FULL | TEST1 | 53732 | 1259K| 154 (2)| 00:00:02 |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST2 | 489 | 11736 | 6 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IND_TEST2_ID2 | 196 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("I"."OBJECT_ID2"=:B1)
Note
-----
- dynamic sampling used for this statement
20 rows selected.
执行计划中ID 4，IND_TEST2_ID2被正常使用
我们继续TEST2的object_id和TEST1中的object_id数据类型是不一致的情况.
传递参数时,类型与数据库表的字段类型不同(表字段类型为字符型),这样就会产生数据类型转换,ORACLE无法使用到索引,走了TABLE ACCESS FULL,导致 buffer get过多，成本高，执行时间长。
SQL>
2 UPDATE test1 t
SET a.object_type =
3 4 (SELECT i.object_type FROM test2 i WHERE i.object_id = t.object_id);
Explained.
看看执行计划
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 53732 | 1259K| 154 (2)| 00:00:02 |
| 1 | UPDATE | TEST1 | | | | |
| 2 | TABLE ACCESS FULL| TEST1 | 53732 | 1259K| 154 (2)| 00:00:02 |
|* 3 | TABLE ACCESS FULL| TEST2 | 489 | 30807 | 168 (2)| 00:00:03 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(TO_NUMBER("I"."OBJECT_ID")=:B1)
Note
-----
- dynamic sampling used for this statement
19 rows selected.
谓词信息可以看到object_id列进行to_number转换，但是执行计划却没有选择object_id字段的索引
解决办法1：创建进行数据类型转换的函数索引
SQL> CREATE INDEX ind_test2_id1x ON test2(TO_NUMBER(OBJECT_ID));
Index created.
执行sql：UPDATE test1 t SET a.object_type = (SELECT i.object_type FROM test2 i WHERE i.object_id =t.object_id);
执行计划为
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 53732 | 1259K| 154 (2)| 00:00:02 |
| 1 | UPDATE | TEST1 | | | | |
| 2 | TABLE ACCESS FULL | TEST1 | 53732 | 1259K| 154 (2)| 00:00:02 |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST2 | 489 | 30807 | 6 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IND_TEST2_ID1X | 196 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(TO_NUMBER("OBJECT_ID")=:B1)
Note
-----
- dynamic sampling used for this statement
20 rows selected.
解决办法2
修改sql语句：
UPDATE test1 t
SET a.object_type =
(SELECT i.object_type FROM test2 i WHERE TO_NUMBER(i.object_id) = t.object_id);
执行计划：
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 53732 | 1259K| 154 (2)| 00:00:02 |
| 1 | UPDATE | TEST1 | | | | |
| 2 | TABLE ACCESS FULL | TEST1 | 53732 | 1259K| 154 (2)| 00:00:02 |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST2 | 489 | 30807 | 6 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IND_TEST2_ID1X | 196 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(TO_NUMBER("OBJECT_ID")=:B1)
Note
-----
- dynamic sampling used for this statement
20 rows selected.
另外，一些sql，在变量传递的时候，可以考虑修改传递的数据类型为所需要的类型.
--The End