| Remove Merge cartesian Join [message #669268] |
Wed, 11 April 2018 06:41  |
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  |
John Watson
Messages: 8960
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
