一个隐式转换引发的执行计划错误

查看awr报告过程发现一个隐式转换导致的索引失效问题，做做记录

awr中语句实际消耗大40多w逻辑读

代入变量后测试很快

SQL> SELECT a.order_prod_amount, b.prod_cost_price FROM user_sess a, PR_DO_T b WHERE a.prod_id = b.prod_id AND a.order_no = '125924325287';Elapsed: 00:00:00.00Execution Plan----------------------Plan hash value: 4287492176-------------------------------------------| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |-------------------------------------------|   0 | SELECT STATEMENT                    |             |     1 |    30 |     4   (0)| 00:00:01 |       |       ||   1 |  NESTED LOOPS                       |             |     1 |    30 |     4   (0)| 00:00:01 |       |       ||   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| user_sess   |     1 |    21 |     3   (0)| 00:00:01 | ROWID | ROWID ||*  3 |    INDEX UNIQUE SCAN                | SYS_C008504 |     1 |       |     2   (0)| 00:00:01 |       |       ||   4 |   TABLE ACCESS BY INDEX ROWID       | PR_DO_T     |  2983 | 26847 |     1   (0)| 00:00:01 |       |       ||*  5 |    INDEX UNIQUE SCAN                | SYS_C008459 |     1 |       |     0   (0)| 00:00:01 |       |       |-------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - access("A"."ORDER_NO"='125924325287')   5 - access("A"."PROD_ID"="B"."PROD_ID")Statistics----------------------          1  recursive calls          0  db block gets          7  consistent gets          2  physical reads          0  redo size        489  bytes sent via SQL*Net to client        396  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed

按语句sqlid查看历史执行计划

SQL> SELECT a.order_prod_amount, b.prod_cost_price FROM user_sess a, PR_DO_T b WHERE a.prod_id = b.prod_id AND a.order_no = '871314                 SQL> set linesize 200SQL> select * from table(dbms_xplan.display_cursor('88f4paj5t26xb'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------SQL_ID  88f4paj5t26xb, child number 0-------------------------------------SELECT a.order_prod_amount,b.prod_cost_price                        FROM user_sess a, PR_DO_T b                  WHERE a.prod_id  = b.prod_id                         AND a.order_no = :"SYS_B_0"Plan hash value: 4105848458------------------------------------------------------------------------| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |------------------------------------------------------------------------|   0 | SELECT STATEMENT             |             |       |       |   106K(100)|          |       |       |PLAN_TABLE_OUTPUT------------------------------------------------------------------------|   1 |  NESTED LOOPS                |             |     1 |    30 |   106K  (1)| 00:21:14 |       |       ||   2 |   PARTITION RANGE ALL        |             |     1 |    21 |   106K  (1)| 00:21:14 |     1 |    53 ||*  3 |    TABLE ACCESS FULL         | user_sess  |     1 |    21 |   106K  (1)| 00:21:14 |     1 |    53 ||   4 |   TABLE ACCESS BY INDEX ROWID| PR_DO_T     |     1 |     9 |     1   (0)| 00:00:01 |       |       ||*  5 |    INDEX UNIQUE SCAN         | SYS_C008459 |     1 |       |     0   (0)|          |       |       |------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - filter(TO_NUMBER("A"."ORDER_NO")=:SYS_B_0)PLAN_TABLE_OUTPUT-------------------------------------------------------------------------   5 - access("A"."PROD_ID"="B"."PROD_ID")24 rows selected.

?user_sess明显走错了执行计划，使用了全表扫描

突然想到user_sess表字段order_no为varchar2猜测为隐式转换
去掉单引号测试

SQL> SELECT a.order_prod_amount, b.prod_cost_price FROM user_sess a, PR_DO_T b WHERE a.prod_id = b.prod_id AND a.order_no = 125924325287;Elapsed: 00:00:18.46Execution Plan----------------------Plan hash value: 4105848458------------------------------------------------------------------------| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |------------------------------------------------------------------------|   0 | SELECT STATEMENT             |             |     1 |    30 |   106K  (1)| 00:21:14 |       |       ||   1 |  NESTED LOOPS                |             |     1 |    30 |   106K  (1)| 00:21:14 |       |       ||   2 |   PARTITION RANGE ALL        |             |     1 |    21 |   106K  (1)| 00:21:14 |     1 |    53 ||*  3 |    TABLE ACCESS FULL         | user_sess  |     1 |    21 |   106K  (1)| 00:21:14 |     1 |    53 ||   4 |   TABLE ACCESS BY INDEX ROWID| PR_DO_T     |     1 |     9 |     1   (0)| 00:00:01 |       |       ||*  5 |    INDEX UNIQUE SCAN         | SYS_C008459 |     1 |       |     0   (0)| 00:00:01 |       |       |------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - filter(TO_NUMBER("A"."ORDER_NO")=125924325287)   5 - access("A"."PROD_ID"="B"."PROD_ID")Statistics----------------------          1  recursive calls          0  db block gets     484974  consistent gets     477021  physical reads          0  redo size        489  bytes sent via SQL*Net to client        396  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed

找开发人员加回蛋引号避免隐式转换后恢复

注：隐式类型转换number转换成varchar2时，索引会失效。varchar2转换为number时，索引并不失效