 |
|
Oracle: removing expensive "merge join cartesian"
Oracle Tips by Burleson Consulting |
Question:
I have this SQL which does a "Merge join Cartesian", and it
takes forever to run. It's
consuming 60% of the database resources, and I've been told that
the merge join cartesian is a bad execution plan.
SELECT DISTINCT A.CUSTOMER_REF,
ATTRIBUTE_VALUE
FROM
G42PRODUCTATTRIBUTE E,
G42CUSTHASPACKAGE A,
G42CUSTHASPRODUCT C,
G42CUSTPRODUCTDETAILS B,
G42CUSTPRODUCTATTRDETAILS D
WHERE
A.CUSTOMER_REF = :B2
AND
B.ACCOUNT_NUM =:B1
AND
A.CUSTOMER_REF = B.CUSTOMER_REF
AND
B.CUSTOMER_REF = C.CUSTOMER_REF
AND
A.PACKAGE_SEQ = C.PACKAGE_SEQ
AND
B.PRODUCT_SEQ = C.PRODUCT_SEQ
AND
A.CUSTOMER_REF = D.CUSTOMER_REF
AND
C.PRODUCT_SEQ = D.PRODUCT_SEQ
AND
D.PRODUCT_ID+0 = 1
AND
D.PRODUCT_ATTRIBUTE_SUBID = E.PRODUCT _ATTRIBUTE_SUBID AND
D.PRODUCT_ID+0 = E.PRODUCT_ID
AND
E.ATTRIBUTE_UA_NAME = 'CUST_ORD_NUMBER'
AND
A.PACKAGE_ID IN (1, 14, 15, 16, 19, 22, 23, 24, 26, 37)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 103 | 2068 |
| 1 | SORT UNIQUE | | 1 | 103 | 2068 |
| 2 | TABLE ACCESS BY INDEX ROWID | G42CUSTPRODUCTATTRDETAILS |
1 | 27 | 1
| 3 | NESTED LOOPS | | 1 | 103 | 2067 |
| 4 | NESTED LOOPS | | 1 | 76 | 2066 |
| 5 | NESTED LOOPS | | 4 | 236 | 2065 |
| 6 | MERGE JOIN CARTESIAN | | 4 | 148 | 2064 |
| 7 | TABLE ACCESS BY INDEX ROWID| G42PRODUCTATTRIBUTE | 2 | 42
| 1 |
| 8 | INDEX RANGE SCAN | G42PRODUCTATTRIBUTE_AK2 | 6 | | 1 |
| 9 | BUFFER SORT | | 2 | 32 | 2063 |
| 10 | INDEX FULL SCAN | G42CUSHASPKG_CUSREF_PKSQ_PKID | 2 | 32
| 6875 |
| 11 | TABLE ACCESS BY INDEX ROWID | G42CUSTPRODUCTDETAILS | 1 |
22 | 1 |
| 12 | INDEX RANGE SCAN | G42CUSTPRODUCTDETAILS_AK3 | 1 | | 1 |
| 13 | TABLE ACCESS BY INDEX ROWID | G42CUSTHASPRODUCT | 1 | 17
| 1 |
| 14 | INDEX RANGE SCAN | G42CUSTHASPRODUCT_PK1 | 1 | | 0 |
| 15 | INDEX RANGE SCAN | G42CUSTPRODUCTATTRDETAILS_PK1 | 1 | |
1 |
How to I remove the merge join
cartesian?
Answer:
There are several reasons that your SQL will do a merge join Cartesian. Cartesian
merge joins can also be caused by:
- Forgot to add a table join condition to WHERE clause
- Missing join indexes
- Bad/stale schema statistics (reanalyze with dbms_stats)
You have a
4-way table join, and I would start by using the
ORDERED hint to force your optimal join order.
As for the cartesian merge join, first, carefully check your WHERE
clause to ensure that you have provided the proper join
conditions (e.g. where a.primary_key= b.foreign_key).
More information on disabling cartesian merge joins, if
desirable, is available
HERE.
 |
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
