Oracle join 字段发生隐式类型转换导致索引失效分析处理

          早上上班的时候，开发发过来一段sql，join 字段存在索引，说还是执行很慢，让帮忙分析原因，sql 片段如下:

select  * from ppsuser.mes_qholdsn_log a  join sajet.g_sn_status b on a.customer_sn||'01')=b.serial_number
where a.in_guid = 'DAFEE6F8-67A0-499E-B696-B1D14ADD04DA'

        看了2个表a、b结构，join 字段确实存在索引，于是查看了执行计划如下:

---------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Rows     | Bytes      | Cost   | Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |   650295 |  161923455 | 227037 | 00:45:25 |
| * 1 |   HASH JOIN                    |                    |   650295 |  161923455 | 227037 | 00:45:25 |
|   2 |    TABLE ACCESS BY INDEX ROWID | MES_QHOLDSN_LOG    |        2 |        146 |      5 | 00:00:01 |
| * 3 |     INDEX RANGE SCAN           | PK_MES_QHOLDSN_LOG |        2 |            |      4 | 00:00:01 |
|   4 |    PARTITION RANGE ALL         |                    | 32514752 | 5722596352 | 226944 | 00:45:24 |
|   5 |     TABLE ACCESS FULL          | G_SN_STATUS        | 32514752 | 5722596352 | 226944 | 00:45:24 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("A"."CUSTOMER_SN"||U'01'=SYS_OP_C2C("B"."SERIAL_NUMBER"))
* 3 - access("A"."IN_GUID"='DAFEE6F8-67A0-499E-B696-B1D14ADD04DA')

         G_SN_STATUS    这张表数据量几千万，可以看到SYS_OP_C2C 关键字，也就是发生了隐式类型转换导致B表索引失效，再次查看a、b表结构，a表字段customer_sn 类型为nvarchar2,b表字段serial_number类型为varchar2，处理起来也很简单,使用to_char 函数转换一下，调整后执行速度很快，sql 如下:

select  * from ppsuser.mes_qholdsn_log a  join sajet.g_sn_status b on to_char(a.customer_sn||'01')=b.serial_number
where a.in_guid = 'DAFEE6F8-67A0-499E-B696-B1D14ADD04DA'

     执行计划如下:

-----------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name               | Rows | Bytes | Cost | Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                    |    2 |   498 |    9 | 00:00:01 |
|   1 |   NESTED LOOPS                        |                    |    2 |   498 |    9 | 00:00:01 |
|   2 |    NESTED LOOPS                       |                    |    2 |   498 |    9 | 00:00:01 |
|   3 |     TABLE ACCESS BY INDEX ROWID       | MES_QHOLDSN_LOG    |    2 |   146 |    5 | 00:00:01 |
| * 4 |      INDEX RANGE SCAN                 | PK_MES_QHOLDSN_LOG |    2 |       |    4 | 00:00:01 |
| * 5 |     INDEX UNIQUE SCAN                 | IDX_SAJET_A030     |    1 |       |    1 | 00:00:01 |
|   6 |    TABLE ACCESS BY GLOBAL INDEX ROWID | G_SN_STATUS        |    1 |   176 |    2 | 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 4 - access("A"."IN_GUID"='DAFEE6F8-67A0-499E-B696-B1D14ADD04DA')
* 5 - access("B"."SERIAL_NUMBER"=SYS_OP_C2C("A"."CUSTOMER_SN"||U'01'))