用hint NO_UNNEST 可以禁止CBO 进行 Subquery Unnesting
hint UNNEST 可以提示CBO进行Subquery Unnesting
上面HINT只能放在子查询里面
select count(*) as col_0_0_
from WF_PUB_FORM_DATA pubformdat0_
where (1 = 1)
and (pubformdat0_.ID in
(select distinct pubformcas1_.FORM_ID
from WF_PUB_FORM_CASE pubformcas1_
where (pubformcas1_.CASE_ID in
((select to_char(caserun0_.ID)
from CASE_RUN caserun0_, WORKITEM_RUN workitemru1_
where (caserun0_.ID = workitemru1_.CASEID)
and (caserun0_.STATE = 2)
and (workitemru1_.PERFORMER in ('300016/00415'))
and (workitemru1_.VALID_FLAG = '0')) union all
(select to_char(caserun2_.ID)
from CASE_RUN caserun2_, WORKITEM_HISTORY workitemhi3_
where (caserun2_.ID = workitemhi3_.CASEID)
and (caserun2_.STATE = 2)
and (workitemhi3_.PERFORMER in ('300016/00415'))
and (workitemhi3_.VALID_FLAG = '0'))))))
Plan hash value: 3317493882
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 77 | 234M (1)|780:17:15 |
| 1 | SORT AGGREGATE | | 1 | 77 | | |
|* 2 | HASH JOIN RIGHT SEMI | | 656 | 50512 | 234M (1)|780:17:15 |
| 3 | VIEW | VW_NSO_1 | 656 | 17712 | 234M (1)|780:16:33 |
|* 4 | FILTER | | | | | |
| 5 | TABLE ACCESS FULL | WF_PUB_FORM_CASE | 319K| 17M| 1577 (1)| 00:00:19 |
| 6 | UNION-ALL | | | | | |
| 7 | NESTED LOOPS | | 1 | 32 | 229 (0)| 00:00:03 |
|* 8 | TABLE ACCESS BY INDEX ROWID| WORKITEM_RUN | 115 | 2760 | 114 (0)| 00:00:02 |
|* 9 | INDEX RANGE SCAN | IDX_WORKITEM_R_8 | 116 | | 3 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| CASE_RUN | 1 | 8 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_CASE_RUN | 1 | | 0 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | WORKITEM_HISTORY | 1 | 23 | 8 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 1 | 31 | 522 (1)| 00:00:07 |
|* 14 | TABLE ACCESS FULL | CASE_RUN | 1 | 8 | 514 (1)| 00:00:07 |
|* 15 | INDEX RANGE SCAN | IDX_WORKITEM_H_3 | 7 | | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | WF_PUB_FORM_DATA | 256K| 12M| 3574 (1)| 00:00:43 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PUBFORMDAT0_"."ID"="$nso_col_1")
4 - filter( EXISTS ( (SELECT TO_CHAR("CASERUN0_"."ID") FROM "WORKITEM_RUN"
"WORKITEMRU1_","CASE_RUN" "CASERUN0_" WHERE "CASERUN0_"."ID"="WORKITEMRU1_"."CASEID" AND
"CASERUN0_"."STATE"=2 AND TO_CHAR("CASERUN0_"."ID")=:B1 AND
"WORKITEMRU1_"."PERFORMER"='300016/00415' AND "WORKITEMRU1_"."VALID_FLAG"='0') UNION ALL
(SELECT TO_CHAR("CASERUN2_"."ID") FROM "WORKITEM_HISTORY" "WORKITEMHI3_","CASE_RUN"
"CASERUN2_" WHERE "CASERUN2_"."STATE"=2 AND TO_CHAR("CASERUN2_"."ID")=:B2 AND
"CASERUN2_"."ID"="WORKITEMHI3_"."CASEID" AND "WORKITEMHI3_"."PERFORMER"='300016/00415' AND
"WORKITEMHI3_"."VALID_FLAG"='0')))
8 - filter("WORKITEMRU1_"."VALID_FLAG"='0')
9 - access("WORKITEMRU1_"."PERFORMER"='300016/00415')
10 - filter("CASERUN0_"."STATE"=2)
11 - access("CASERUN0_"."ID"="WORKITEMRU1_"."CASEID")
filter(TO_CHAR("CASERUN0_"."ID")=:B1)
12 - filter("WORKITEMHI3_"."PERFORMER"='300016/00415' AND "WORKITEMHI3_"."VALID_FLAG"='0')
14 - filter("CASERUN2_"."STATE"=2 AND TO_CHAR("CASERUN2_"."ID")=:B1)
15 - access("CASERUN2_"."ID"="WORKITEMHI3_"."CASEID")
Plan hash value: 3317493882
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 77 | 234M (1)|780:17:15 |
| 1 | SORT AGGREGATE | | 1 | 77 | | |
|* 2 | HASH JOIN RIGHT SEMI | | 656 | 50512 | 234M (1)|780:17:15 |
| 3 | VIEW | VW_NSO_1 | 656 | 17712 | 234M (1)|780:16:33 |
|* 4 | FILTER | | | | | |
| 5 | TABLE ACCESS FULL | WF_PUB_FORM_CASE | 319K| 17M| 1577 (1)| 00:00:19 |
| 6 | UNION-ALL | | | | | |
| 7 | NESTED LOOPS | | 1 | 32 | 229 (0)| 00:00:03 |
|* 8 | TABLE ACCESS BY INDEX ROWID| WORKITEM_RUN | 115 | 2760 | 114 (0)| 00:00:02 |
|* 9 | INDEX RANGE SCAN | IDX_WORKITEM_R_8 | 116 | | 3 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| CASE_RUN | 1 | 8 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_CASE_RUN | 1 | | 0 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | WORKITEM_HISTORY | 1 | 23 | 8 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 1 | 31 | 522 (1)| 00:00:07 |
|* 14 | TABLE ACCESS FULL | CASE_RUN | 1 | 8 | 514 (1)| 00:00:07 |
|* 15 | INDEX RANGE SCAN | IDX_WORKITEM_H_3 | 7 | | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | WF_PUB_FORM_DATA | 256K| 12M| 3574 (1)| 00:00:43 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PUBFORMDAT0_"."ID"="$nso_col_1")
4 - filter( EXISTS ( (SELECT TO_CHAR("CASERUN0_"."ID") FROM "WORKITEM_RUN"
"WORKITEMRU1_","CASE_RUN" "CASERUN0_" WHERE "CASERUN0_"."ID"="WORKITEMRU1_"."CASEID" AND
"CASERUN0_"."STATE"=2 AND TO_CHAR("CASERUN0_"."ID")=:B1 AND
"WORKITEMRU1_"."PERFORMER"='300016/00415' AND "WORKITEMRU1_"."VALID_FLAG"='0') UNION ALL
(SELECT TO_CHAR("CASERUN2_"."ID") FROM "WORKITEM_HISTORY" "WORKITEMHI3_","CASE_RUN"
"CASERUN2_" WHERE "CASERUN2_"."STATE"=2 AND TO_CHAR("CASERUN2_"."ID")=:B2 AND
"CASERUN2_"."ID"="WORKITEMHI3_"."CASEID" AND "WORKITEMHI3_"."PERFORMER"='300016/00415' AND
"WORKITEMHI3_"."VALID_FLAG"='0')))
8 - filter("WORKITEMRU1_"."VALID_FLAG"='0')
9 - access("WORKITEMRU1_"."PERFORMER"='300016/00415')
10 - filter("CASERUN0_"."STATE"=2)
11 - access("CASERUN0_"."ID"="WORKITEMRU1_"."CASEID")
filter(TO_CHAR("CASERUN0_"."ID")=:B1)
这里说明
(pubformcas1_.CASE_ID in
((select to_char(caserun0_.ID)
from CASE_RUN caserun0_, WORKITEM_RUN workitemru1_
where (caserun0_.ID = workitemru1_.CASEID)
and (caserun0_.STATE = 2)
and (workitemru1_.PERFORMER in ('300016/00415'))
and (workitemru1_.VALID_FLAG = '0'))
这里的子查询没有展开
使用RULE的优化器后:
Plan hash value: 1482859701
--------------------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | MERGE JOIN | |
| 3 | SORT JOIN | |
| 4 | TABLE ACCESS FULL | WF_PUB_FORM_DATA |
|* 5 | SORT JOIN | |
| 6 | VIEW | VW_NSO_2 |
| 7 | SORT UNIQUE | |
| 8 | TABLE ACCESS BY INDEX ROWID | WF_PUB_FORM_CASE |
| 9 | NESTED LOOPS | |
| 10 | VIEW | VW_NSO_1 |
| 11 | SORT UNIQUE | |
| 12 | UNION-ALL | |
| 13 | NESTED LOOPS | |
| 14 | TABLE ACCESS BY INDEX ROWID| WORKITEM_RUN |
|* 15 | INDEX RANGE SCAN | IDX_WORKITEM_R_13 |
|* 16 | TABLE ACCESS BY INDEX ROWID| CASE_RUN |
|* 17 | INDEX UNIQUE SCAN | PK_CASE_RUN |
| 18 | NESTED LOOPS | |
| 19 | TABLE ACCESS BY INDEX ROWID| WORKITEM_HISTORY |
|* 20 | INDEX RANGE SCAN | IDX_WORKITEM_H_13 |
|* 21 | TABLE ACCESS BY INDEX ROWID| CASE_RUN |
|* 22 | INDEX UNIQUE SCAN | PK_CASE_RUN |
|* 23 | INDEX RANGE SCAN | IDX_WF_PUB_FC_2 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("PUBFORMDAT0_"."ID"="$nso_col_1")
filter("PUBFORMDAT0_"."ID"="$nso_col_1")
15 - access("WORKITEMRU1_"."PERFORMER"='300016/00415' AND
"WORKITEMRU1_"."VALID_FLAG"='0')
16 - filter("CASERUN0_"."STATE"=2)
17 - access("CASERUN0_"."ID"="WORKITEMRU1_"."CASEID")
20 - access("WORKITEMHI3_"."PERFORMER"='300016/00415' AND
"WORKITEMHI3_"."VALID_FLAG"='0')
21 - filter("CASERUN2_"."STATE"=2)
22 - access("CASERUN2_"."ID"="WORKITEMHI3_"."CASEID")
23 - access("PUBFORMCAS1_"."CASE_ID"="$nso_col_1")
Note
-----
- rule based optimizer used (consider using cbo)
加上hints的执行计划:
explain plan for
select count(*) as col_0_0_
from WF_PUB_FORM_DATA pubformdat0_
where (1 = 1)
and (pubformdat0_.ID in
(select distinct pubformcas1_.FORM_ID
from WF_PUB_FORM_CASE pubformcas1_
where (pubformcas1_.CASE_ID in
((select /*+unnest*/ to_char(caserun0_.ID)
from CASE_RUN caserun0_, WORKITEM_RUN workitemru1_
where (caserun0_.ID = workitemru1_.CASEID)
and (caserun0_.STATE = 2)
and (workitemru1_.PERFORMER in ('300016/00415'))
and (workitemru1_.VALID_FLAG = '0')) union all
(select to_char(caserun2_.ID)
from CASE_RUN caserun2_, WORKITEM_HISTORY workitemhi3_
where (caserun2_.ID = workitemhi3_.CASEID)
and (caserun2_.STATE = 2)
and (workitemhi3_.PERFORMER in ('300016/00415'))
and (workitemhi3_.VALID_FLAG = '0'))))));
Plan hash value: 1072876563
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 77 | 5621 (1)| 00:01:08 |
| 1 | SORT AGGREGATE | | 1 | 77 | | |
|* 2 | HASH JOIN RIGHT SEMI | | 656 | 50512 | 5621 (1)| 00:01:08 |
| 3 | VIEW | VW_NSO_2 | 656 | 17712 | 2045 (1)| 00:00:25 |
|* 4 | HASH JOIN | | 656 | 51824 | 2045 (1)| 00:00:25 |
| 5 | VIEW | VW_NSO_1 | 656 | 14432 | 753 (0)| 00:00:10 |
| 6 | UNION-ALL | | | | | |
| 7 | NESTED LOOPS | | 115 | 3680 | 229 (0)| 00:00:03 |
|* 8 | TABLE ACCESS BY INDEX ROWID| WORKITEM_RUN | 115 | 2760 | 114 (0)| 00:00:02 |
|* 9 | INDEX RANGE SCAN | IDX_WORKITEM_R_8 | 116 | | 3 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| CASE_RUN | 1 | 8 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_CASE_RUN | 1 | | 0 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 541 | 16771 | 524 (0)| 00:00:07 |
| 13 | TABLE ACCESS BY INDEX ROWID| WORKITEM_HISTORY | 541 | 12443 | 397 (0)| 00:00:05 |
|* 14 | INDEX RANGE SCAN | IDX_WORKITEM_H_14 | 541 | | 10 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID| CASE_RUN | 1 | 8 | 1 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | PK_CASE_RUN | 1 | | 0 (0)| 00:00:01 |
| 17 | INDEX FAST FULL SCAN | IDX_WF_PUB_FC_4 | 319K| 17M| 1289 (1)| 00:00:16 |
| 18 | TABLE ACCESS FULL | WF_PUB_FORM_DATA | 256K| 12M| 3574 (1)| 00:00:43 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PUBFORMDAT0_"."ID"="$nso_col_1")
4 - access("PUBFORMCAS1_"."CASE_ID"="$nso_col_1")
8 - filter("WORKITEMRU1_"."VALID_FLAG"='0')
9 - access("WORKITEMRU1_"."PERFORMER"='300016/00415')
10 - filter("CASERUN0_"."STATE"=2)
11 - access("CASERUN0_"."ID"="WORKITEMRU1_"."CASEID")
14 - access("WORKITEMHI3_"."PERFORMER"='300016/00415' AND "WORKITEMHI3_"."VALID_FLAG"='0')
filter("WORKITEMHI3_"."VALID_FLAG"='0')
15 - filter("CASERUN2_"."STATE"=2)
16 - access("CASERUN2_"."ID"="WORKITEMHI3_"."CASEID")