绑定变量与隐式转换

绑定变量与隐式转换

开发人员来寻求帮助，一则简单的UPDATE语句运行持续几个小时都没有结束。(涉及安全，不能贴太多代码，只能通过枯燥的白话描述)

UPDATE tab1 SET col1= :1, col2= :2 WHERE col3 = :3 AND col4 IS NULL AND clo5 IS NULL

从真实的执行计划来看，走的全表扫描，沟通开发人员，知道更新量很少，走索引最合适，于是通过强制的Hint 让其走索引，不幸的是，不关如何加hint, 结果要么走全表扫描，要么走 index full scan, 永远不走index range scan, 确定hint没有加错，并且在Toad中预执行计划都可以做到走 index range scan, 找到具体的值带入SQL语句中，使用set autotrace on观察执行计划如何走，当带入col3 = ‘55590700008654’，执行计划很完美，走index range scan.
开发人员使用的是informatica工具，于是找到col3字段的数据源头，也是varchar2类型，没有问题，貌似一切都是对的，无懈可击。
强烈的智能第六感告诉我可能是隐式转换导致的，但是从表象看不像，于是开始寻找证据证明自己的想法。首先再去认真的读真实执行计划：

Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(("col1" IS NULL AND "col2" IS NULL 
              AND SYS_OP_C2C("col3")=:3))

看到有个特殊的函数SYS_OP_C2C, 通过查询知道这个就是Oracle内部的隐式转换函数，于是先确定根本原因就是隐式转换导致的，那么下一步就要找出它什么时候被转换掉了，再次找到开发人员，需要看informatica的整个workflow的过程，发现有一步，从数据库读取varchar2类型的字段数据后，下一步的处理转化成了informatia的string类型，再用string类型传递给了上面的update语句。基本上确定就是这个坑了，informatica的类型里面没有varchar2类型，选择了string类型做替代，猜想转换就发生在这里，为了验证string类型传递给绑定变量后的类型是和varchar2类型不一样的，使用了以下的终极方法来确定。

UPDATE tab1 SET col1= :1, col2= :2 WHERE col3 = to_char(:3) AND col4 IS NULL AND clo5 IS NULL

在加了to_char转换后，执行计划完美的按照index range scan执行，瞬间结束，痛快。