Home » RDBMS Server » Performance Tuning » Merge Join Cartesian in Outline Data (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit)
Merge Join Cartesian in Outline Data [message #569697] Wed, 31 October 2012 06:47
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

Hi,

Please give a look to the below execution plan:


 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                    | E-Rows |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------
|   1 |  UNION-ALL                           |                         |        |       |       |          |
|   2 |   HASH UNIQUE                        |                         |   5678 |   832K|   832K| 1086K (0)|
|*  3 |    FILTER                            |                         |        |       |       |          |
|*  4 |     CONNECT BY WITH FILTERING        |                         |        |  2048 |  2048 | 2048  (0)|
|*  5 |      FILTER                          |                         |        |       |       |          |
|   6 |       COUNT                          |                         |        |       |       |          |
|*  7 |        HASH JOIN RIGHT OUTER         |                         |   5678 |  1179K|  1179K| 1546K (0)|
|   8 |         TABLE ACCESS FULL            | MAP_DATASET_INPUT       |   4871 |       |       |          |
|   9 |         TABLE ACCESS FULL            | MAP_DATASET             |   5678 |       |       |          |
|  10 |       NESTED LOOPS                   |                         |      1 |       |       |          |
|  11 |        NESTED LOOPS                  |                         |      1 |       |       |          |
|* 12 |         TABLE ACCESS BY INDEX ROWID  | MAP_DATASET             |      1 |       |       |          |
|* 13 |          INDEX UNIQUE SCAN           | MAP_DATASET_PK          |      1 |       |       |          |
|* 14 |         INDEX RANGE SCAN             | SPQ_SM_IX02             |      1 |       |       |          |
|* 15 |        TABLE ACCESS BY INDEX ROWID   | MAP_OP_SDTM_DOMAIN      |      1 |       |       |          |
|* 16 |         INDEX UNIQUE SCAN            | MAP_OP_SDTM_DOMAIN_UK01 |      1 |       |       |          |
|* 17 |      HASH JOIN                       |                         |        |  1348K|  1348K|  761K (0)|
|  18 |       CONNECT BY PUMP                |                         |        |       |       |          |
|  19 |       COUNT                          |                         |        |       |       |          |
|* 20 |        HASH JOIN RIGHT OUTER         |                         |   5678 |  1179K|  1179K| 1639K (0)|
|  21 |         TABLE ACCESS FULL            | MAP_DATASET_INPUT       |   4871 |       |       |          |
|  22 |         TABLE ACCESS FULL            | MAP_DATASET             |   5678 |       |       |          |
|  23 |   HASH UNIQUE                        |                         |   5676 |  1134K|  1134K|          |
|* 24 |    HASH JOIN RIGHT ANTI              |                         |   5676 |    11M|  3722K|   29M (0)|
|  25 |     VIEW                             | VW_NSO_3                |      1 |       |       |          |
|  26 |      NESTED LOOPS                    |                         |      1 |       |       |          |
|* 27 |       HASH JOIN                      |                         |      1 |    11M|  4042K|   28M (0)|
|* 28 |        INDEX RANGE SCAN              | SPQ_SM_IX02             |      1 |       |       |          |
|* 29 |        TABLE ACCESS FULL             | MAP_OP_SDTM_DOMAIN      |     15 |       |       |          |
|* 30 |       TABLE ACCESS BY INDEX ROWID    | MAP_DATASET             |      1 |       |       |          |
|* 31 |        INDEX UNIQUE SCAN             | MAP_DATASET_PK          |      1 |       |       |          |
|* 32 |     VIEW                             |                         |   5678 |       |       |          |
|* 33 |      FILTER                          |                         |        |       |       |          |
|* 34 |       CONNECT BY WITH FILTERING      |                         |        |  2048 |  2048 | 2048  (0)|
|* 35 |        FILTER                        |                         |        |       |       |          |
|  36 |         COUNT                        |                         |        |       |       |          |
|* 37 |          HASH JOIN RIGHT OUTER       |                         |   5678 |  1179K|  1179K| 1547K (0)|
|  38 |           TABLE ACCESS FULL          | MAP_DATASET_INPUT       |   4871 |       |       |          |
|  39 |           TABLE ACCESS FULL          | MAP_DATASET             |   5678 |       |       |          |
|  40 |         NESTED LOOPS                 |                         |      1 |       |       |          |
|  41 |          NESTED LOOPS                |                         |      1 |       |       |          |
|  42 |           TABLE ACCESS BY INDEX ROWID| MAP_DATASET             |      1 |       |       |          |
|* 43 |            INDEX UNIQUE SCAN         | MAP_DATASET_PK          |      1 |       |       |          |
|* 44 |           INDEX RANGE SCAN           | SPQ_SM_IX02             |      1 |       |       |          |
|* 45 |          TABLE ACCESS BY INDEX ROWID | MAP_OP_SDTM_DOMAIN      |      1 |       |       |          |
|* 46 |           INDEX UNIQUE SCAN          | MAP_OP_SDTM_DOMAIN_UK01 |      1 |       |       |          |
|* 47 |        HASH JOIN                     |                         |        |  1348K|  1348K|  763K (0)|
|  48 |         CONNECT BY PUMP              |                         |        |       |       |          |
|  49 |         COUNT                        |                         |        |       |       |          |
|* 50 |          HASH JOIN RIGHT OUTER       |                         |   5678 |  1179K|  1179K| 1649K (0)|
|  51 |           TABLE ACCESS FULL          | MAP_DATASET_INPUT       |   4871 |       |       |          |
|  52 |           TABLE ACCESS FULL          | MAP_DATASET             |   5678 |       |       |          |
------------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$291F8F59")
      OUTLINE_LEAF(@"SEL$8771BF6C")
      UNNEST(@"SEL$3")
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$0DDF58A2")
      OUTLINE_LEAF(@"SEL$3512B053")
      OUTLINE_LEAF(@"SEL$E571FF94")
      UNNEST(@"SEL$8")
      OUTLINE_LEAF(@"SEL$9")
      OUTLINE_LEAF(@"SEL$7")
      OUTLINE_LEAF(@"SEL$9BEFCD7A")
      UNNEST(@"SEL$10")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$10")
      OUTLINE(@"SEL$8")
      OUTLINE(@"SEL$6")
      OUTLINE(@"SEL$5")
      NO_ACCESS(@"SEL$9BEFCD7A" "from$_subquery$_011"@"SEL$5")
      NO_ACCESS(@"SEL$9BEFCD7A" "VW_NSO_3"@"SEL$9BEFCD7A")
      LEADING(@"SEL$9BEFCD7A" "from$_subquery$_011"@"SEL$5" "VW_NSO_3"@"SEL$9BEFCD7A")
      USE_HASH(@"SEL$9BEFCD7A" "VW_NSO_3"@"SEL$9BEFCD7A")
      SWAP_JOIN_INPUTS(@"SEL$9BEFCD7A" "VW_NSO_3"@"SEL$9BEFCD7A")
      FULL(@"SEL$2" "MD"@"SEL$2")
      FULL(@"SEL$2" "MDI"@"SEL$2")
      LEADING(@"SEL$2" "MD"@"SEL$2" "MDI"@"SEL$2")
      USE_HASH(@"SEL$2" "MDI"@"SEL$2")
      SWAP_JOIN_INPUTS(@"SEL$2" "MDI"@"SEL$2")
      INDEX_RS_ASC(@"SEL$4" "MD"@"SEL$4" ("MAP_DATASET"."MAP_DATASET_SEQ_NO"))
      INDEX(@"SEL$4" "SPQ"@"SEL$4" ("STAGING_PROCESS_QUEUE"."STATUS_FLAG" 
              "STAGING_PROCESS_QUEUE"."STUDY_ID" "STAGING_PROCESS_QUEUE"."DOMAIN_NAME"))
      INDEX_RS_ASC(@"SEL$4" "MSD"@"SEL$4" ("MAP_OP_SDTM_DOMAIN"."MAP_DATASET_SEQ_NO" 
              "MAP_OP_SDTM_DOMAIN"."DOMAIN_NAME"))
      LEADING(@"SEL$4" "MD"@"SEL$4" "SPQ"@"SEL$4" "MSD"@"SEL$4")
      USE_NL(@"SEL$4" "SPQ"@"SEL$4")
      USE_NL(@"SEL$4" "MSD"@"SEL$4")
      NO_ACCESS(@"SEL$8771BF6C" "VW_NSO_1"@"SEL$8771BF6C")
      INDEX(@"SEL$8771BF6C" "MD"@"SEL$1" ("MAP_DATASET"."MAP_DATASET_SEQ_NO"))
      INDEX_FFS(@"SEL$8771BF6C" "MDI"@"SEL$1" ("MAP_DATASET_INPUT"."MAP_DATASET_INPUT_SEQ_NO"))
      LEADING(@"SEL$8771BF6C" "VW_NSO_1"@"SEL$8771BF6C" "MD"@"SEL$1" "MDI"@"SEL$1")
      USE_NL(@"SEL$8771BF6C" "MD"@"SEL$1")
      USE_MERGE_CARTESIAN(@"SEL$8771BF6C" "MDI"@"SEL$1")
      INDEX(@"SEL$291F8F59" "SPQ"@"SEL$3" ("STAGING_PROCESS_QUEUE"."STATUS_FLAG" 
              "STAGING_PROCESS_QUEUE"."STUDY_ID" "STAGING_PROCESS_QUEUE"."DOMAIN_NAME"))
      FULL(@"SEL$291F8F59" "MSD"@"SEL$3")
      INDEX_RS_ASC(@"SEL$291F8F59" "MD"@"SEL$3" ("MAP_DATASET"."MAP_DATASET_SEQ_NO"))
      LEADING(@"SEL$291F8F59" "SPQ"@"SEL$3" "MSD"@"SEL$3" "MD"@"SEL$3")
      USE_HASH(@"SEL$291F8F59" "MSD"@"SEL$3")
      USE_NL(@"SEL$291F8F59" "MD"@"SEL$3")
      INDEX(@"SEL$0DDF58A2" "SPQ"@"SEL$10" ("STAGING_PROCESS_QUEUE"."STATUS_FLAG" 
              "STAGING_PROCESS_QUEUE"."STUDY_ID" "STAGING_PROCESS_QUEUE"."DOMAIN_NAME"))
      FULL(@"SEL$0DDF58A2" "MSD"@"SEL$10")
      INDEX_RS_ASC(@"SEL$0DDF58A2" "MD"@"SEL$10" ("MAP_DATASET"."MAP_DATASET_SEQ_NO"))
      LEADING(@"SEL$0DDF58A2" "SPQ"@"SEL$10" "MSD"@"SEL$10" "MD"@"SEL$10")
      USE_HASH(@"SEL$0DDF58A2" "MSD"@"SEL$10")
      USE_NL(@"SEL$0DDF58A2" "MD"@"SEL$10")
      FULL(@"SEL$7" "MD"@"SEL$7")
      FULL(@"SEL$7" "MDI"@"SEL$7")
      LEADING(@"SEL$7" "MD"@"SEL$7" "MDI"@"SEL$7")
      USE_HASH(@"SEL$7" "MDI"@"SEL$7")
      SWAP_JOIN_INPUTS(@"SEL$7" "MDI"@"SEL$7")
      INDEX_RS_ASC(@"SEL$9" "MD"@"SEL$9" ("MAP_DATASET"."MAP_DATASET_SEQ_NO"))
      INDEX(@"SEL$9" "SPQ"@"SEL$9" ("STAGING_PROCESS_QUEUE"."STATUS_FLAG" 
              "STAGING_PROCESS_QUEUE"."STUDY_ID" "STAGING_PROCESS_QUEUE"."DOMAIN_NAME"))
      INDEX_RS_ASC(@"SEL$9" "MSD"@"SEL$9" ("MAP_OP_SDTM_DOMAIN"."MAP_DATASET_SEQ_NO" 
              "MAP_OP_SDTM_DOMAIN"."DOMAIN_NAME"))
      LEADING(@"SEL$9" "MD"@"SEL$9" "SPQ"@"SEL$9" "MSD"@"SEL$9")
      USE_NL(@"SEL$9" "SPQ"@"SEL$9")
      USE_NL(@"SEL$9" "MSD"@"SEL$9")
      NO_ACCESS(@"SEL$E571FF94" "VW_NSO_2"@"SEL$E571FF94")
      INDEX(@"SEL$E571FF94" "MD"@"SEL$6" ("MAP_DATASET"."MAP_DATASET_SEQ_NO"))
      INDEX_FFS(@"SEL$E571FF94" "MDI"@"SEL$6" ("MAP_DATASET_INPUT"."MAP_DATASET_INPUT_SEQ_NO"))
      LEADING(@"SEL$E571FF94" "VW_NSO_2"@"SEL$E571FF94" "MD"@"SEL$6" "MDI"@"SEL$6")
      USE_NL(@"SEL$E571FF94" "MD"@"SEL$6")
      USE_MERGE_CARTESIAN(@"SEL$E571FF94" "MDI"@"SEL$6")
      INDEX(@"SEL$3512B053" "SPQ"@"SEL$8" ("STAGING_PROCESS_QUEUE"."STATUS_FLAG" 
              "STAGING_PROCESS_QUEUE"."STUDY_ID" "STAGING_PROCESS_QUEUE"."DOMAIN_NAME"))
      FULL(@"SEL$3512B053" "MSD"@"SEL$8")
      INDEX_RS_ASC(@"SEL$3512B053" "MD"@"SEL$8" ("MAP_DATASET"."MAP_DATASET_SEQ_NO"))
      LEADING(@"SEL$3512B053" "SPQ"@"SEL$8" "MSD"@"SEL$8" "MD"@"SEL$8")
      USE_HASH(@"SEL$3512B053" "MSD"@"SEL$8")
      USE_NL(@"SEL$3512B053" "MD"@"SEL$8")
      END_OUTLINE_DATA
  */
 


In execution plan it is not showing that we have used Merge Join, but in outline data its showing USE_MERGE_CARTESIAN.

I am not getting the reason behind it, please help.

Regards,
Manu
Previous Topic: [11g]Direct-path insert / temp usage / PGA
Next Topic: NESTED LOOPS JOIN and DISTRIBUTED OPERATIONS
Goto Forum:
  


Current Time: Fri Mar 29 10:53:11 CDT 2024