当分页语句遇到union all
来源：互联网 发布：linux系统能玩什么游戏 编辑：程序博客网 时间：2024/05/17 21:56
SELECT * FROM (SELECT pubformdat0_.id id332_, pubformdat0_.domain_id domain2_332_, pubformdat0_.process_id process3_332_, pubformdat0_.entity_id entity4_332_, pubformdat0_.file_type file5_332_, pubformdat0_.title title332_, pubformdat0_.word_no word7_332_, pubformdat0_.priority priority332_, pubformdat0_.secret_level secret9_332_, pubformdat0_.drafter drafter332_, pubformdat0_.drafter_name drafter11_332_, pubformdat0_.draft_dept draft12_332_, pubformdat0_.draft_deptname draft13_332_, pubformdat0_.draft_date draft14_332_, pubformdat0_.end_date end15_332_, pubformdat0_.arch_state arch16_332_, pubformdat0_.arch_fileid arch17_332_, pubformdat0_.gq_templateid gq18_332_, pubformdat0_.gw_templateid gw19_332_, pubformdat0_.edit_flag edit20_332_, pubformdat0_.delete_flag delete21_332_, pubformdat0_.operator operator332_, pubformdat0_.operate_date operate23_332_, pubformdat0_.file_security_level file24_332_, pubformdat0_.yj_flag yj25_332_, pubformdat0_.yj_user yj26_332_ FROM wf_pub_form_data pubformdat0_ WHERE 1 = 1 and pubformdat0_.operate_date is not null 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') ) ORDER BY pubformdat0_.operate_date DESC) WHERE rownum <= 10;SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID43pfg99tyav4a, child number 0-------------------------------------SELECT * FROM (SELECT pubformdat0_.id id332_, pubformdat0_.domain_id domain2_332_, pubformdat0_.process_id process3_332_,pubformdat0_.entity_id entity4_332_, pubformdat0_.file_type file5_332_, pubformdat0_.title title332_, pubformdat0_.word_noword7_332_, pubformdat0_.priority priority332_, pubformdat0_.secret_level secret9_332_, pubformdat0_.drafterdrafter332_, pubformdat0_.drafter_name drafter11_332_, pubformdat0_.draft_dept draft12_332_,pubformdat0_.draft_deptname draft13_332_, pubformdat0_.draft_date draft14_332_, pubformdat0_.end_date end15_332_, pubformdat0_.arch_state arch16_332_,pubformdat0_.arch_fileid arch17_332_, pubformdat0_.gq_templateid gq18_332_, pubformdat0_.gw_templateid gw19_332_,Plan hash value: 2461821393---------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows |A-Time | Buffers | Reads | Writes |OMem |1Mem | Used-Mem | Used-Tmp|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------|* 1 | COUNT STOPKEY | |1 | | 10 |00:00:00.39 | 23012 | 3 | 3 | | || || 2 | VIEW | |1 | 718 | 10 |00:00:00.39 | 23012 | 3 | 3 | | || ||* 3 | SORT ORDER BY STOPKEY | |1 | 718 | 10 |00:00:00.39 | 23012 | 3 | 3 | 27648 | 27648 |50176 (1)| 1024 ||* 4 | HASH JOIN RIGHT SEMI | |1 | 718 |681 |00:00:00.38 | 23008 | 0 | 0 | 842K| 842K|139K (0)| || 5 | VIEW | VW_NSO_2 |1 | 714 |693 |00:00:00.19 | 8056 | 0 | 0 | | || ||* 6 | HASH JOIN | |1 | 714 |693 |00:00:00.19 | 8056 | 0 | 0 |1306K|1306K|99328 (0)| || 7 | VIEW | VW_NSO_1 |1 | 714 |693 |00:00:00.01 | 4352 | 0 | 0 | | || || 8 | UNION-ALL | |1 | |693 |00:00:00.01 | 4352 | 0 | 0 | | || || 9 | NESTED LOOPS | |1 | 132 | 5 |00:00:00.01 |20 | 0 | 0 | | || || 10 | TABLE ACCESS BY INDEX ROWID| WORKITEM_RUN |1 | 132 | 5 |00:00:00.01 | 8 | 0 | 0 | | || ||* 11 | INDEX RANGE SCAN | IDX_WORKITEM_R_13 |1 | 132 | 5 |00:00:00.01 | 3 | 0 | 0 | | || ||* 12 | TABLE ACCESS BY INDEX ROWID| CASE_RUN |5 | 1 | 5 |00:00:00.01 |12 | 0 | 0 | | || ||* 13 | INDEX UNIQUE SCAN | PK_CASE_RUN |5 | 1 | 5 |00:00:00.01 | 7 | 0 | 0 | | || || 14 | NESTED LOOPS | |1 | 582 |688 |00:00:00.01 | 4332 | 0 | 0 | | || || 15 | TABLE ACCESS BY INDEX ROWID| WORKITEM_HISTORY |1 | 582 | 2010 |00:00:00.01 | 1632 | 0 | 0 | | || ||* 16 | INDEX RANGE SCAN | IDX_WORKITEM_H_13 |1 | 325 | 2010 |00:00:00.01 |11 | 0 | 0 | | || ||* 17 | TABLE ACCESS BY INDEX ROWID| CASE_RUN |2010 | 1 |688 |00:00:00.01 | 2700 | 0 | 0 | | || ||* 18 | INDEX UNIQUE SCAN | PK_CASE_RUN |2010 | 1 |688 |00:00:00.01 | 2012 | 0 | 0 | | || || 19 | INDEX FAST FULL SCAN | IDX_WF_PUB_FC_4 |1 | 375K|384K|00:00:00.01 | 3704 | 0 | 0 | | || ||* 20 | TABLE ACCESS FULL | WF_PUB_FORM_DATA |1 | 293K|295K|00:00:00.01 | 14952 | 0 | 0 | | || |---------------------------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(ROWNUM<=10) 3 - filter(ROWNUM<=10) 4 - access("PUBFORMDAT0_"."ID"="$nso_col_1") 6 - access("PUBFORMCAS1_"."CASE_ID"="$nso_col_1") 11 - access("WORKITEMRU1_"."PERFORMER"='300016/00415' AND "WORKITEMRU1_"."VALID_FLAG"='0') 12 - filter("CASERUN0_"."STATE"=2) 13 - access("CASERUN0_"."ID"="WORKITEMRU1_"."CASEID") 16 - access("WORKITEMHI3_"."PERFORMER"='300016/00415' AND "WORKITEMHI3_"."VALID_FLAG"='0') 17 - filter("CASERUN2_"."STATE"=2) 18 - access("CASERUN2_"."ID"="WORKITEMHI3_"."CASEID") 20 - filter("PUBFORMDAT0_"."OPERATE_DATE" IS NOT NULL)52 rows selected.从wf_pub_form_data pubformdat0_ 返回295K条记录,|* 20 | TABLE ACCESS FULL | WF_PUB_FORM_DATA |1 | 293K|分页SQL不能走全表扫描在operate_date 排序列上创建索引create index wf_pub_form_data_idx1 on wf_pub_form_data(operate_date);SELECT * FROM (SELECT /*+index_desc(pubformdat0_ wf_pub_form_data_idx1)*/ pubformdat0_.id id332_, pubformdat0_.domain_id domain2_332_, pubformdat0_.process_id process3_332_, pubformdat0_.entity_id entity4_332_, pubformdat0_.file_type file5_332_, pubformdat0_.title title332_, pubformdat0_.word_no word7_332_, pubformdat0_.priority priority332_, pubformdat0_.secret_level secret9_332_, pubformdat0_.drafter drafter332_, pubformdat0_.drafter_name drafter11_332_, pubformdat0_.draft_dept draft12_332_, pubformdat0_.draft_deptname draft13_332_, pubformdat0_.draft_date draft14_332_, pubformdat0_.end_date end15_332_, pubformdat0_.arch_state arch16_332_, pubformdat0_.arch_fileid arch17_332_, pubformdat0_.gq_templateid gq18_332_, pubformdat0_.gw_templateid gw19_332_, pubformdat0_.edit_flag edit20_332_, pubformdat0_.delete_flag delete21_332_, pubformdat0_.operator operator332_, pubformdat0_.operate_date operate23_332_, pubformdat0_.file_security_level file24_332_, pubformdat0_.yj_flag yj25_332_, pubformdat0_.yj_user yj26_332_ FROM wf_pub_form_data pubformdat0_ WHERE 1 = 1 and pubformdat0_.operate_date is not null 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') ) ORDER BY pubformdat0_.operate_date DESC) WHERE rownum <= 10 SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_IDcr76jyxubq29b, child number 0-------------------------------------SELECT * FROM (SELECT /*+index_desc(pubformdat0_ wf_pub_form_data_idx1)*/pubformdat0_.id id332_, pubformdat0_.domain_id domain2_332_, pubformdat0_.process_id process3_332_, pubformdat0_.entity_id entity4_332_,pubformdat0_.file_typefile5_332_, pubformdat0_.title title332_, pubformdat0_.word_no word7_332_, pubformdat0_.priority priority332_, pubformdat0_.secret_level secret9_332_, pubformdat0_.drafter drafter332_, pubformdat0_.drafter_namedrafter11_332_, pubformdat0_.draft_dept draft12_332_,pubformdat0_.draft_deptname draft13_332_,pubformdat0_.draft_date draft14_332_, pubformdat0_.end_date end15_332_, pubformdat0_.arch_state arch16_332_, pubformdat0_.arch_fileid arch17_332_, pubformdat0_.gq_templateid gq18_332_,Plan hash value: 3354863370------------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads| OMem | 1Mem | Used-Mem |------------------------------------------------------------------------------------------------------------------------------------------------------------|* 1 | COUNT STOPKEY | | 1 | | 10 |00:00:00.97 | 293K| 6453 || || 2 | VIEW | | 1 | 718 | 10 |00:00:00.97 | 293K| 6453 || ||* 3 | SORT ORDER BY STOPKEY | | 1 | 718 | 10 |00:00:00.97 | 293K| 6453 | 5120 | 5120 | 4096(0)||* 4 | HASH JOIN RIGHT SEMI | | 1 | 718 | 681 |00:00:00.97 | 293K| 6453 | 842K| 842K| 169K (0)|| 5 | VIEW | VW_NSO_2 | 1 | 714 | 693 |00:00:00.19 | 8056 | 0 || ||* 6 | HASH JOIN | | 1 | 714 | 693 |00:00:00.19 | 8056 | 0 | 1306K| 1306K|99328(0)|| 7 | VIEW | VW_NSO_1 | 1 | 714 | 693 |00:00:00.01 | 4352 | 0 || || 8 | UNION-ALL | | 1 | | 693 |00:00:00.01 | 4352 | 0 || || 9 | NESTED LOOPS | | 1 | 132 | 5 |00:00:00.01 | 20 | 0 || || 10 | TABLE ACCESS BY INDEX ROWID | WORKITEM_RUN | 1 | 132 | 5 |00:00:00.01 | 8 | 0 || ||* 11 | INDEX RANGE SCAN | IDX_WORKITEM_R_13 | 1 | 132 | 5 |00:00:00.01 | 3 | 0 || ||* 12 | TABLE ACCESS BY INDEX ROWID | CASE_RUN | 5 | 1 | 5 |00:00:00.01 | 12 | 0 || ||* 13 | INDEX UNIQUE SCAN | PK_CASE_RUN | 5 | 1 | 5 |00:00:00.01 | 7 | 0 || || 14 | NESTED LOOPS | | 1 | 582 | 688 |00:00:00.01 | 4332 | 0 || || 15 | TABLE ACCESS BY INDEX ROWID | WORKITEM_HISTORY | 1 | 582 | 2010 |00:00:00.01 | 1632 | 0 || ||* 16 | INDEX RANGE SCAN | IDX_WORKITEM_H_13 | 1 | 325 | 2010 |00:00:00.01 | 11 | 0 || ||* 17 | TABLE ACCESS BY INDEX ROWID | CASE_RUN | 2010 | 1 | 688 |00:00:00.01 | 2700 | 0 || ||* 18 | INDEX UNIQUE SCAN | PK_CASE_RUN | 2010 | 1 | 688 |00:00:00.01 | 2012 | 0 || || 19 | INDEX FAST FULL SCAN | IDX_WF_PUB_FC_4 | 1 | 375K| 384K|00:00:00.01 | 3704 | 0 || || 20 | TABLE ACCESS BY INDEX ROWID | WF_PUB_FORM_DATA | 1 | 293K| 295K|00:00:00.59 | 285K| 6453 || ||* 21 | INDEX FULL SCAN DESCENDING | WF_PUB_FORM_DATA_IDX1 | 1 | 295K| 295K|00:00:00.04 | 787 | 786 || |------------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(ROWNUM<=10) 3 - filter(ROWNUM<=10) 4 - access("PUBFORMDAT0_"."ID"="$nso_col_1") 6 - access("PUBFORMCAS1_"."CASE_ID"="$nso_col_1") 11 - access("WORKITEMRU1_"."PERFORMER"='300016/00415' AND "WORKITEMRU1_"."VALID_FLAG"='0') 12 - filter("CASERUN0_"."STATE"=2) 13 - access("CASERUN0_"."ID"="WORKITEMRU1_"."CASEID") 16 - access("WORKITEMHI3_"."PERFORMER"='300016/00415' AND "WORKITEMHI3_"."VALID_FLAG"='0') 17 - filter("CASERUN2_"."STATE"=2) 18 - access("CASERUN2_"."ID"="WORKITEMHI3_"."CASEID") 21 - filter("PUBFORMDAT0_"."OPERATE_DATE" IS NOT NULL)53 rows selected.扫描了295K创建组合索引: where条件列+排序列create index WF_PUB_FORM_DATA_IDX2 on WF_PUB_FORM_DATA(ID,OPERATE_DATE);SELECT * FROM (SELECT /*+index_desc(pubformdat0_ WF_PUB_FORM_DATA_IDX2)*/ pubformdat0_.id id332_, pubformdat0_.domain_id domain2_332_, pubformdat0_.process_id process3_332_, pubformdat0_.entity_id entity4_332_, pubformdat0_.file_type file5_332_, pubformdat0_.title title332_, pubformdat0_.word_no word7_332_, pubformdat0_.priority priority332_, pubformdat0_.secret_level secret9_332_, pubformdat0_.drafter drafter332_, pubformdat0_.drafter_name drafter11_332_, pubformdat0_.draft_dept draft12_332_, pubformdat0_.draft_deptname draft13_332_, pubformdat0_.draft_date draft14_332_, pubformdat0_.end_date end15_332_, pubformdat0_.arch_state arch16_332_, pubformdat0_.arch_fileid arch17_332_, pubformdat0_.gq_templateid gq18_332_, pubformdat0_.gw_templateid gw19_332_, pubformdat0_.edit_flag edit20_332_, pubformdat0_.delete_flag delete21_332_, pubformdat0_.operator operator332_, pubformdat0_.operate_date operate23_332_, pubformdat0_.file_security_level file24_332_, pubformdat0_.yj_flag yj25_332_, pubformdat0_.yj_user yj26_332_ FROM wf_pub_form_data pubformdat0_ WHERE 1 = 1 and pubformdat0_.operate_date is not null 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') ) ORDER BY pubformdat0_.operate_date DESC) WHERE rownum <= 10 SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID05xxrwtr25wgz, child number 0-------------------------------------SELECT * FROM (SELECT /*+index_desc(pubformdat0_ WF_PUB_FORM_DATA_IDX2)*/pubformdat0_.id id332_, pubformdat0_.domain_id domain2_332_,pubformdat0_.process_id process3_332_,pubformdat0_.entity_id entity4_332_, pubformdat0_.file_type file5_332_,pubformdat0_.title title332_, pubformdat0_.word_no word7_332_,pubformdat0_.priority priority332_, pubformdat0_.secret_levelsecret9_332_, pubformdat0_.drafter drafter332_, pubformdat0_.drafter_name drafter11_332_, pubformdat0_.draft_deptdraft12_332_, pubformdat0_.draft_deptname draft13_332_, pubformdat0_.draft_date draft14_332_, pubformdat0_.end_dateend15_332_, pubformdat0_.arch_state arch16_332_, pubformdat0_.arch_fileid arch17_332_, pubformdat0_.gq_templateidgq18_332_,Plan hash value: 549882602---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|* 1 | COUNT STOPKEY | | 1 | | 10 |00:00:00.20 | 10106 | 534 | 5 | | | || 2 | VIEW | | 1 | 718 | 10 |00:00:00.20 | 10106 | 534 | 5 | | | ||* 3 | SORT ORDER BY STOPKEY | | 1 | 718 | 10 |00:00:00.20 | 10106 | 534 | 5 | 18432 | 18432 |74752 (1)| 1024 || 4 | TABLE ACCESS BY INDEX ROWID | WF_PUB_FORM_DATA | 1 | 1 | 681 |00:00:00.20 | 10103 | 532 | 3 | | | || 5 | NESTED LOOPS | | 1 | 718 | 1363 |00:00:00.20 | 9424 | 532 | 3 | | | || 6 | VIEW | VW_NSO_2 | 1 | 714 | 681 |00:00:00.19 | 8056 |3 | 3 | | | || 7 | HASH UNIQUE | | 1 | 714 | 681 |00:00:00.19 | 8056 |3 | 3 | 846K| 846K| 252K (0)| 1024 ||* 8 | HASH JOIN | | 1 | 714 | 693 |00:00:00.19 | 8056 |0 | 0 | 1306K| 1306K|98304 (0)| || 9 | VIEW | VW_NSO_1 | 1 | 714 | 693 |00:00:00.01 | 4352 |0 | 0 | | | || 10 | UNION-ALL | | 1 | | 693 |00:00:00.01 | 4352 |0 | 0 | | | || 11 | NESTED LOOPS | | 1 | 132 | 5 |00:00:00.01 | 20 |0 | 0 | | | || 12 | TABLE ACCESS BY INDEX ROWID| WORKITEM_RUN | 1 | 132 |5 |00:00:00.01 | 8 |0 | 0 | | | ||* 13 | INDEX RANGE SCAN | IDX_WORKITEM_R_13 | 1 | 132 |5 |00:00:00.01 | 3 |0 | 0 | | | ||* 14 | TABLE ACCESS BY INDEX ROWID| CASE_RUN | 5 | 1 |5 |00:00:00.01 | 12 |0 | 0 | | | ||* 15 | INDEX UNIQUE SCAN | PK_CASE_RUN | 5 | 1 |5 |00:00:00.01 | 7 |0 | 0 | | | || 16 | NESTED LOOPS | | 1 | 582 | 688 |00:00:00.01 | 4332 |0 | 0 | | | || 17 | TABLE ACCESS BY INDEX ROWID| WORKITEM_HISTORY | 1 | 582 | 2010 |00:00:00.01 | 1632 |0 | 0 | | | ||* 18 | INDEX RANGE SCAN | IDX_WORKITEM_H_13 | 1 | 325 | 2010 |00:00:00.01 | 11 |0 | 0 | | | ||* 19 | TABLE ACCESS BY INDEX ROWID| CASE_RUN | 2010 | 1 | 688 |00:00:00.01 | 2700 |0 | 0 | | | ||* 20 | INDEX UNIQUE SCAN | PK_CASE_RUN | 2010 | 1 | 688 |00:00:00.01 | 2012 |0 | 0 | | | || 21 | INDEX FAST FULL SCAN | IDX_WF_PUB_FC_4 | 1 | 375K| 384K|00:00:00.01 | 3704 |0 | 0 | | | ||* 22 | INDEX RANGE SCAN DESCENDING| WF_PUB_FORM_DATA_IDX2| 681 | 1 | 681 |00:00:00.01 | 1368 | 529 | 0 | | | |---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(ROWNUM<=10) 3 - filter(ROWNUM<=10) 8 - access("PUBFORMCAS1_"."CASE_ID"="$nso_col_1") 13 - access("WORKITEMRU1_"."PERFORMER"='300016/00415' AND "WORKITEMRU1_"."VALID_FLAG"='0') 14 - filter("CASERUN0_"."STATE"=2) 15 - access("CASERUN0_"."ID"="WORKITEMRU1_"."CASEID") 18 - access("WORKITEMHI3_"."PERFORMER"='300016/00415' AND "WORKITEMHI3_"."VALID_FLAG"='0') 19 - filter("CASERUN2_"."STATE"=2) 20 - access("CASERUN2_"."ID"="WORKITEMHI3_"."CASEID") 22 - access("PUBFORMDAT0_"."ID"="$nso_col_1") filter("PUBFORMDAT0_"."OPERATE_DATE" IS NOT NULL)54 rows selected.为扫描时扫描681行停止,而不是10行呢？为什么没及时刹车呢？因为子查询这里有union allUNION 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') 去掉union all后： SELECT * FROM (SELECT /*+index_desc(pubformdat0_ wf_pub_form_data_idx2)*/ pubformdat0_.id id332_, pubformdat0_.domain_id domain2_332_, pubformdat0_.process_id process3_332_, pubformdat0_.entity_id entity4_332_, pubformdat0_.file_type file5_332_, pubformdat0_.title title332_, pubformdat0_.word_no word7_332_, pubformdat0_.priority priority332_, pubformdat0_.secret_level secret9_332_, pubformdat0_.drafter drafter332_, pubformdat0_.drafter_name drafter11_332_, pubformdat0_.draft_dept draft12_332_, pubformdat0_.draft_deptname draft13_332_, pubformdat0_.draft_date draft14_332_, pubformdat0_.end_date end15_332_, pubformdat0_.arch_state arch16_332_, pubformdat0_.arch_fileid arch17_332_, pubformdat0_.gq_templateid gq18_332_, pubformdat0_.gw_templateid gw19_332_, pubformdat0_.edit_flag edit20_332_, pubformdat0_.delete_flag delete21_332_, pubformdat0_.operator operator332_, pubformdat0_.operate_date operate23_332_, pubformdat0_.file_security_level file24_332_, pubformdat0_.yj_flag yj25_332_, pubformdat0_.yj_user yj26_332_ FROM wf_pub_form_data pubformdat0_ WHERE 1 = 1 and pubformdat0_.operate_date is not null 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')) ORDER BY pubformdat0_.operate_date DESC) WHERE rownum <= 5SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_IDb7szcw0nawpvq, child number 0------------------------------------- SELECT * FROM (SELECT /*+index_desc(pubformdat0_ wf_pub_form_data_idx2)*/ pubformdat0_.id id332_, pubformdat0_.domain_iddomain2_332_, pubformdat0_.process_id process3_332_, pubformdat0_.entity_id entity4_332_, pubformdat0_.file_type file5_332_, pubformdat0_.title title332_, pubformdat0_.word_no word7_332_,pubformdat0_.priority priority332_, pubformdat0_.secret_level secret9_332_, pubformdat0_.drafterdrafter332_, pubformdat0_.drafter_name drafter11_332_, pubformdat0_.draft_dept draft12_332_, pubformdat0_.draft_deptname draft13_332_, pubformdat0_.draft_date draft14_332_, pubformdat0_.end_dateend15_332_, pubformdat0_.arch_state arch16_332_, pubformdat0_.arch_fileid arch17_332_,pubformdat0_.gq_templateid gq18_332_,Plan hash value: 3448365600----------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |----------------------------------------------------------------------------------------------------------------------------------------------------|* 1 | COUNT STOPKEY | | 1 || 5 |00:00:00.01 | 54 ||| || 2 | VIEW | | 1 | 133 | 5 |00:00:00.01 | 54 ||| ||* 3 | SORT ORDER BY STOPKEY | | 1 | 133 | 5 |00:00:00.01 | 54 | 3072 | 3072 | 2048(0)|| 4 | TABLE ACCESS BY INDEX ROWID | WF_PUB_FORM_DATA | 1 | 1 | 5 |00:00:00.01 | 54 ||| || 5 | NESTED LOOPS | | 1 | 133 | 11 |00:00:00.01 | 49 ||| || 6 | VIEW | VW_NSO_1 | 1 | 132 | 5 |00:00:00.01 | 37 ||| || 7 | HASH UNIQUE | | 1 | 132 | 5 |00:00:00.01 | 37 | 846K| 846K| 101K (0)|| 8 | TABLE ACCESS BY INDEX ROWID | WF_PUB_FORM_CASE | 1 | 1 | 5 |00:00:00.01 | 37 ||| || 9 | NESTED LOOPS | | 1 | 132 | 11 |00:00:00.01 | 32 ||| || 10 | NESTED LOOPS | | 1 | 132 | 5 |00:00:00.01 | 20 ||| || 11 | TABLE ACCESS BY INDEX ROWID| WORKITEM_RUN | 1 | 132 | 5 |00:00:00.01 | 8 ||| ||* 12 | INDEX RANGE SCAN | IDX_WORKITEM_R_13 | 1 | 132 | 5 |00:00:00.01 | 3 ||| ||* 13 | TABLE ACCESS BY INDEX ROWID| CASE_RUN | 5 | 1 | 5 |00:00:00.01 | 12 ||| ||* 14 | INDEX UNIQUE SCAN | PK_CASE_RUN | 5 | 1 | 5 |00:00:00.01 | 7 ||| ||* 15 | INDEX RANGE SCAN | IDX_WF_PUB_FC_2 | 5 | 1 | 5 |00:00:00.01 | 12 ||| ||* 16 | INDEX RANGE SCAN DESCENDING | WF_PUB_FORM_DATA_IDX2 | 5 | 1 | 5 |00:00:00.01 | 12 ||| |----------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(ROWNUM<=5) 3 - filter(ROWNUM<=5) 12 - access("WORKITEMRU1_"."PERFORMER"='300016/00415' AND "WORKITEMRU1_"."VALID_FLAG"='0') 13 - filter("CASERUN0_"."STATE"=2) 14 - access("CASERUN0_"."ID"="WORKITEMRU1_"."CASEID") 15 - access("PUBFORMCAS1_"."CASE_ID"=TO_CHAR("CASERUN0_"."ID")) 16 - access("PUBFORMDAT0_"."ID"="$nso_col_1") filter("PUBFORMDAT0_"."OPERATE_DATE" IS NOT NULL)46 rows selected.果断刹住了 0 0
- 当分页语句遇到union all
- 分页加union all
- sql语句union,union all
- 分表后多表,UNION 和UNION ALL查询分页
- mysql UNION ALL查询分页
- sql语句中的union和union all
- SQL语句 UNION 和 UNION ALL 使用
- SQL - UNION 及 UNION ALL 语句
- SQL语句之Union和Union All
- SQL语句UNION和 UNION ALL
- sql查询语句UNION ALL
- UNION ALL的排序分页问题
- 关于ORACLE UNION ALL 遇到的问题
- SQL语句教程(18) UNION ALL
- Union all 连接2个SQL语句
- SQL语句中UNION与UNION ALL的区别！
- sql语句查询结果合并union 和union all用法
- sql语句查询结果合并union 和union all用法
- [BZOJ1004][HNOI2008][Burnside引理][DP]Cards
- 孙力：智能可穿戴的创意设计与未来
- 巧用SEO技术,速提自然流量
- QT 获取系统屏幕分辨率
- Java jdk源代码的Math包
- 当分页语句遇到union all
- Two Sum
- 让Nginx作为Windows服务开机自启动
- C++习题 复数类--重载运算符2+
- WIN7如何自定义桌面右键菜单
- 二维码简介
- SGU 226
- session存储到memchached实现方案时，属性说明
- 增加页面附加价值，提升网站排名
