Home » RDBMS Server » Performance Tuning » Remove Merge cartesian Join (Oracle 10g)
Remove Merge cartesian Join [message #669268] Wed, 11 April 2018 06:41 Go to next message
pradies
Messages: 250
Registered: May 2008
Senior Member
Hi All,

I am getting a query which is using merge cartesian join inside the explain plan. can someone please suggest me how to remove this. I checked about this into google, it was saying to check about join condition, but when observing there is only last line in where is creating this issue and as per data can't remove that.

SELECT     distinct   RH.INVOICE_NBR,
       MD.MODEL_NAME,
       CT.PROD_CAT,
       BR.BRAND_NAME,
       RH.RECV_UNIT,
       RH.INVOICE_DATE,
       MODEL_CODE,
       CT.ID AS PROD_TYPE,
       br.BRAND_CODE,
       RH.RECV_SERIAL_NBR  ,
       (SELECT COUNT (*)
          FROM ASSADMIN.RO_HIST
         WHERE     ORD_TYPE = 'ND'
               AND INVOICE_NBR = 'MI/355'
              AND RECV_UNIT = 'CTMSC601MUKE000'
              )
          AS TOTAL_QTY  
  FROM ASSADMIN.INVOICE_ORD IO
       INNER JOIN assadmin.product PD ON (PD.PART_NBR = IO.PART_NBR)
       INNER JOIN assadmin.brands BR ON (BR.BRAND_CODE = PD.BRAND_CODE)
       INNER JOIN assadmin.PROD_CATEGORY CT ON (CT.ID = PD.PROD_TYPE)
       INNER JOIN assadmin.PROD_MODEL MD ON (CT.PROD_TYPE = MD.MODEL_CODE)
       INNER JOIN ASSADMIN.RO_HIST RH ON (RH.INVOICE_NBR = IO.INVOICE_NBR AND RH.RECV_UNIT = IO.PART_NBR)
 WHERE     RH.INVOICE_NBR = 'CT/GGN/MI/355'
       AND PD.PART_NBR = 'CTMSC601MUKE000'
 

And execution plan is as follows

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                          |     3 |   465 |    17   (6)| 00:00:01 |
|   1 |  SORT AGGREGATE                             |                          |     1 |    34 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED       | RO_HIST                  |    26 |   884 |     9   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                         | IDX_RHIST_INB_IDT_RU_RSN |    28 |       |     3   (0)| 00:00:01 |
|   4 |  HASH UNIQUE                                |                          |     3 |   465 |    17   (6)| 00:00:01 |
|   5 |   MERGE JOIN CARTESIAN                      |                          |    11 |  1705 |     7   (0)| 00:00:01 |
|   6 |    NESTED LOOPS                             |                          |     1 |    99 |     5   (0)| 00:00:01 |
|   7 |     NESTED LOOPS                            |                          |     1 |    99 |     5   (0)| 00:00:01 |
|   8 |      NESTED LOOPS                           |                          |     1 |    88 |     4   (0)| 00:00:01 |
|   9 |       NESTED LOOPS                          |                          |     1 |    73 |     3   (0)| 00:00:01 |
|  10 |        MERGE JOIN CARTESIAN                 |                          |     1 |    55 |     2   (0)| 00:00:01 |
|* 11 |         INDEX RANGE SCAN                    | IDX_IORD_PNBR_INBR       |     1 |    31 |     1   (0)| 00:00:01 |
|  12 |         BUFFER SORT                         |                          |     1 |    24 |     1   (0)| 00:00:01 |
|  13 |          TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCT                  |     1 |    24 |     1   (0)| 00:00:01 |
|* 14 |           INDEX RANGE SCAN                  | IDX_PROD_PBR             |     1 |       |     0   (0)| 00:00:01 |
|  15 |        TABLE ACCESS BY INDEX ROWID BATCHED  | PROD_CATEGORY            |     1 |    18 |     1   (0)| 00:00:01 |
|* 16 |         INDEX RANGE SCAN                    | IDX_PCAT_ID              |     1 |       |     0   (0)| 00:00:01 |
|  17 |       TABLE ACCESS BY INDEX ROWID BATCHED   | PROD_MODEL               |     1 |    15 |     1   (0)| 00:00:01 |
|* 18 |        INDEX RANGE SCAN                     | IDX_PMOD_MCODE           |     1 |       |     0   (0)| 00:00:01 |
|* 19 |      INDEX RANGE SCAN                       | IDX_BND_BCODE            |     1 |       |     0   (0)| 00:00:01 |
|  20 |     TABLE ACCESS BY INDEX ROWID             | BRANDS                   |     1 |    11 |     1   (0)| 00:00:01 |
|  21 |    BUFFER SORT                              |                          |    77 |  4312 |     6   (0)| 00:00:01 |
|* 22 |     INDEX RANGE SCAN                        | IDX_RHIST_INB_IDT_RU_RSN |    77 |  4312 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ORD_TYPE"='ND')
   3 - access("INVOICE_NBR"='MI/355' AND "RECV_UNIT"='CTMSC601MUKE000')
  11 - access("IO"."PART_NBR"='CTMSC601MUKE000' AND "IO"."INVOICE_NBR"='CT/GGN/MI/355')
  14 - access("PD"."PART_NBR"='CTMSC601MUKE000')
  16 - access("CT"."ID"=TO_NUMBER("PD"."PROD_TYPE"))
  18 - access("CT"."PROD_TYPE"="MD"."MODEL_CODE")
  19 - access("BR"."BRAND_CODE"="PD"."BRAND_CODE")
  22 - access("RH"."INVOICE_NBR"='CT/GGN/MI/355' AND "RH"."RECV_UNIT"='CTMSC601MUKE000')

Note
-----
   - this is an adaptive plan


kindly check and suggest.

Thanks in Advance.
Re: Remove Merge cartesian Join [message #669270 is a reply to message #669268] Wed, 11 April 2018 08:46 Go to previous message
John Watson
Messages: 8270
Registered: January 2010
Location: Global Village
Senior Member
Are you sure that the cartesian join is a problem? You could test an alternative: hint a nested loop join to RH and see how it runs then.

--update: I just noticed that it is an adaptive plan, so you need to check what plan is actually being used. Get that wqith dbms_xplan.display_cursor, with format=>'adaptive'.

[Updated on: Wed, 11 April 2018 08:48]

Report message to a moderator

Previous Topic: Finding downgraded sql
Next Topic: A case need to help clear
Goto Forum:
  


Current Time: Mon Jun 01 21:48:15 CDT 2020