[20141212]ORA-01722.txt
--昨天做优化，遇到一个奇怪的问题，记录一下。
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> create table t as select to_char(rownum) id,'test' name from dual connect by levelTable created.
SCOTT@test> create unique index i_t_id on t(id);
Index created.
SCOTT@test> select * from t where id=60;
ID NAME
---------------------------------------- --------------------
60 test
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
--------------------------------------------------------------
SQL_ID 0g3c5maxawf09, child number 0
-------------------------------------
select * from t where id=60
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 66 (100)|
|* 1 | TABLE ACCESS FULL| T | 1 | 66 (4)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("ID")=60)
--存在隐式转换，导致不会使用索引。
SCOTT@test> insert into t values ('x','aaa');
1 row created.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> select * from t where id=60;
ERROR:
ORA-01722: invalid number
no rows selected
$ oerr ora 1722
01722, 00000, "invalid number"
// *Cause: The specified number was invalid.
// *Action: Specify a valid number.
--这个时候查询失败，注意最后的提示no rows selected。实际上这个语句是执行的，直到遇到了id='x',这个转换出错，无法继续。
--如果写成这样就可以有结果。
SCOTT@test> select * from t where id=60 and rownumID NAME
---------------------------------------- --------------------
60 test
--看出视图：
SCOTT@test> select sql_id,sql_text,executions,buffer_gets,child_number from v$sql where sql_id='0g3c5maxawf09';
SQL_ID SQL_TEXT EXECUTIONS BUFFER_GETS CHILD_NUMBER
------------- ------------------------------------------------------------ ---------- ----------- ------------
0g3c5maxawf09 select * from t where id=60 2 464 0
--从执行次数以及BUFFER_GETS都可以看出是遇到错误停止了。
如果我修改第1条记录。
SCOTT@test> update t set id='y' where id='1';
1 row updated.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> select * from t where id=60;
select * from t where id=60
*
ERROR at line 1:
ORA-01722: invalid number
SCOTT@test> select sql_id,sql_text,executions,buffer_gets,child_number from v$sql where sql_id='0g3c5maxawf09';
SQL_ID SQL_TEXT EXECUTIONS BUFFER_GETS CHILD_NUMBER
------------- ------------------------------------------------------------ ---------- ----------- ------------
0g3c5maxawf09 select * from t where id=60 3 467 0
--对比上下buffer_gets的差值，可以推出第3次执行仅仅3个逻辑读。注意一个小细节，这次执行没有no rows selected提示。
--这个也是我开始觉得奇怪的地方，执行计划是全表扫描，而逻辑读却很低。做一个记录。
--现在优化的项目就是存在大量的隐式转换，tmd就是一个豆腐渣工程....实在懒得跟开发提........