• 当分页语句遇到union all


    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_ID	43pfg99tyav4a, 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_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_,
    
    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_ID	cr76jyxubq29b, 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_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_,
    
    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_ID	05xxrwtr25wgz, 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_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_,
    
    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 all
    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')
                                                                      
    去掉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 <= 5
    SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID	b7szcw0nawpvq, 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_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_,
    
    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.
    
    果断刹住了

  • 相关阅读:
    P2525 Uim的情人节礼物·其之壱
    prev_permutation()
    P1634 禽兽的传染病
    P1615 西游记公司
    P1888 三角函数
    __gcd()函数
    P4325
    unique函数
    vscode C++开发环境配置教程(教你如何用vscode写C++)
    codeforces-C. News Distribution-2019.5.15
  • 原文地址:https://www.cnblogs.com/hzcya1995/p/13352315.html
Copyright © 2020-2023  润新知