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