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

转载于:https://www.cnblogs.com/zhaoyangjian724/p/3797871.html