to_char函数引发的不走索引

SQL> conn cowork_czsh/cowork_czsh
Connected.
SQL> set linesize 200
SQL> set pagesize 200
SQL> set autot trace
SQL> select workitemhi0_.PERFORMER as col_0_0_
  from WORKITEM_HISTORY workitemhi0_
 where (to_char(workitemhi0_.CASEID) in
       (select pubformcas1_.CASE_ID
           from WF_PUB_FORM_CASE pubformcas1_
          where (pubformcas1_.FORM_ID = '1292892470448ejk57w15i5xw7ias6f13rpd18iqc19e7d1jy')));  2    3    4    5    6  27 rows selected.Execution Plan
----------------------------------------------------------
Plan hash value: 2272759491------------------------------------------------------------
| Id  | Operation| Name   |
------------------------------------------------------------
|   0 | SELECT STATEMENT|   |
|   1 |  MERGE JOIN|   |
|   2 |   SORT JOIN|   |
|   3 |    TABLE ACCESS FULL| WORKITEM_HISTORY |
|*  4 |   SORT JOIN|   |
|   5 |    VIEW | VW_NSO_1   |
|   6 |     SORT UNIQUE |   |
|   7 |      TABLE ACCESS BY INDEX ROWID| WF_PUB_FORM_CASE |
|*  8 |       INDEX RANGE SCAN| IDX_WF_PUB_FC_1  |
------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   4 - access("$nso_col_1"=TO_CHAR("WORKITEMHI0_"."CASEID"))
       filter("$nso_col_1"=TO_CHAR("WORKITEMHI0_"."CASEID"))
   8 - access("PUBFORMCAS1_"."FORM_ID"='1292892470448ejk57w15i5xw7ias6f1
      3rpd18iqc19e7d1jy')Note
-----
   - rule based optimizer used (consider using cbo)Statistics
----------------------------------------------------------
 78  recursive calls
       2954  db block gets
      80566  consistent gets
     111154  physical reads
116  redo size
       1132  bytes sent via SQL*Net to client
503  bytes received via SQL*Net from client
  3  SQL*Net roundtrips to/from client
  2  sorts (memory)
  1  sorts (disk)
 27  rows processed这里 WORKITEM_HISTORY workitemhi0_ CASEID    NOT NULL NUMBER(38)这里WF_PUB_FORM_CASE pubformcas1_  CASE_ID    VARCHAR2(100)在Oracle中 char varchar2会自动转换为NUMBER,去掉to_char后SQL> SQL> SQL> SQL> SQL> SQL>  select  workitemhi0_.PERFORMER as col_0_0_
     from WORKITEM_HISTORY workitemhi0_
     where ((workitemhi0_.CASEID) in
           (select pubformcas1_.CASE_ID
              from WF_PUB_FORM_CASE pubformcas1_
              where (pubformcas1_.FORM_ID = '1292892470448ejk57w15i5xw7ias6f13rpd18iqc19e7d1jy')));  2    3    4    5    6  27 rows selected.Execution Plan
----------------------------------------------------------
Plan hash value: 1939572408------------------------------------------------------------
| Id  | Operation| Name   |
------------------------------------------------------------
|   0 | SELECT STATEMENT|   |
|   1 |  TABLE ACCESS BY INDEX ROWID| WORKITEM_HISTORY |
|   2 |   NESTED LOOPS|   |
|   3 |    VIEW | VW_NSO_1   |
|   4 |     SORT UNIQUE |   |
|   5 |      TABLE ACCESS BY INDEX ROWID| WF_PUB_FORM_CASE |
|*  6 |       INDEX RANGE SCAN| IDX_WF_PUB_FC_1  |
|*  7 |    INDEX RANGE SCAN| IDX_WORKITEM_H_3 |
------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   6 - access("PUBFORMCAS1_"."FORM_ID"='1292892470448ejk57w15i5xw7ias6f1
      3rpd18iqc19e7d1jy')
   7 - access("WORKITEMHI0_"."CASEID"=TO_NUMBER("$nso_col_1"))Note
-----
   - rule based optimizer used (consider using cbo)Statistics
----------------------------------------------------------
  1  recursive calls
  0  db block gets
 38  consistent gets
  0  physical reads
  0  redo size
       1145  bytes sent via SQL*Net to client
503  bytes received via SQL*Net from client
  3  SQL*Net roundtrips to/from client
  1  sorts (memory)
  0  sorts (disk)
 27  rows processed