1.创建测试表
SQL> create table t(name varchar2(10));
2.插入数据
SQL> insert into t values('123');
SQL> select * from t;
NAME
----------
123
3.执行正常sql并获取执行计划
SQL> select * from t where name='123';
NAME
----------
123
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 7 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME"='123')
注意:执行计划中的fliter为(NAME='123')
4.模拟错误并获取执行计划
SQL> insert into t values('abc');
SQL> select * from t;
NAME
----------
123
abc
SQL> select * from t where name=123;
ERROR:
ORA-01722: 无效数字
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 7 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("NAME")=123)
特别注意:执行计划中的filter为TO_NUMBER(NAME)=123,这里oracle发生了隐式转换,但是name列中因为有'abc'这种字符值,所以to_number('abc')这个操作触发了ORA-01722的错误。所以当name所有数据都可隐式转换成数字时自然不会有错误。