{"id":11526,"date":"2018-08-08T22:00:27","date_gmt":"2018-08-08T20:00:27","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/merge-join-cartesian-a-join-method-or-a-join-type\/"},"modified":"2018-08-08T22:00:27","modified_gmt":"2018-08-08T20:00:27","slug":"merge-join-cartesian-a-join-method-or-a-join-type","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/merge-join-cartesian-a-join-method-or-a-join-type\/","title":{"rendered":"MERGE JOIN CARTESIAN: a join method or a join type?"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nI’ll present about join methods at POUG and DOAG. I’ll show how the different join methods work in order to better understand them. The idea is to show Nested Loops, Hash Join, Sort Merge Join, Merge Join Cartesian on the same query. I’ll run a simple join between DEPT and EMP with the USE_NL, USE_HASH, USE_MERGE and USE_MERGE_CARTESIAN hints. I’ll show the execution plan, with SQL Monitoring in text mode. And I’ll put some gdb breakpoints on the ‘qer’ (query execution rowsource) functions to run the plan operations step by step. Then I’ll do the same on a different query in order to show in detail the 12c adaptive plans.<\/p>\n

But wait, I listed Nested Loops, Hash Join, Sort Merge Join, Merge Join Cartesian… but is Merge Cartesian Join really a join method? I mean, my query is not a cartesian join. I have all join predicates here. But for sure you can also do an inner join by starting with a cartesian join and then filter on the join predicate. As if doing physically what the old join syntax of Oracle is doing logically: by not putting any predicates in the from clause and add the join predicates in the where clause to filter over it.
\n
\nIf I look at the 12.2 documentation, it is a Join method
\n\"CaptureJoinMethods122\"<\/a><\/p>\n

For the definition, a Join Method is how the join will be executed. It is not a decision of the SQL developer because SQL is declarative: you declare the result you want, and the optimizer will decide how to do it. And this is why hints are in comments: they are not part of the declarative syntax. Forcing how to do it is not part of SQL.<\/p>\n

Just after listing the join methods, the documentation lists the join types which are part of the SQL because it declares the join result you expect. Inner join to get all matching rows. Semi join to get only the first matching row. Anti Join to get all rows which do not match. Outer join to get all matching rows in addition to those which matches. The syntax is INNER JOIN, OUTER JOIN, EXISTS or IN, NOT EXISTS or NOT IN. Join type is not ‘how’ but ‘what’.<\/p>\n

Ok, so back to the join method. Let’s force it on my inner join between DEPT and EMP:<\/p>\n

11g<\/h3>\n
\nSQL> alter session set current_schema=SCOTT statistics_level=all;\nSession altered.\n \nSQL> select \/*+ leading(DEPT) USE_MERGE_CARTESIAN(EMP) FULL(DEPT) *\/ * from DEPT join EMP using(deptno);\n \n    DEPTNO DNAME          LOC                EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM\n---------- -------------- ------------- ---------- ---------- --------- ---------- --------- ---------- ----------\n        10 ACCOUNTING     NEW YORK            7782 CLARK      MANAGER         7839 09-JUN-81       2450\n        10 ACCOUNTING     NEW YORK            7839 KING       PRESIDENT            17-NOV-81       5000\n        10 ACCOUNTING     NEW YORK            7934 MILLER     CLERK           7782 23-JAN-82       1300\n        10 RESEARCH       DALLAS              7782 CLARK      MANAGER         7839 09-JUN-81       2450\n        10 RESEARCH       DALLAS              7839 KING       PRESIDENT            17-NOV-81       5000\n        10 RESEARCH       DALLAS              7934 MILLER     CLERK           7782 23-JAN-82       1300\n        10 SALES          CHICAGO             7782 CLARK      MANAGER         7839 09-JUN-81       2450\n        10 SALES          CHICAGO             7839 KING       PRESIDENT            17-NOV-81       5000\n        10 SALES          CHICAGO             7934 MILLER     CLERK           7782 23-JAN-82       1300\n        10 OPERATIONS     BOSTON              7782 CLARK      MANAGER         7839 09-JUN-81       2450\n        10 OPERATIONS     BOSTON              7839 KING       PRESIDENT            17-NOV-81       5000\n        10 OPERATIONS     BOSTON              7934 MILLER     CLERK           7782 23-JAN-82       1300\n \n12 rows selected.\n \nSQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));\n \nPLAN_TABLE_OUTPUT\n------------------------------------------------------------------------------------------------------------------\nSQL_ID  1xpfxq6pc30vq, child number 0\n-------------------------------------\nselect \/*+ leading(DEPT) USE_MERGE_CARTESIAN(EMP) FULL(DEPT) *\/ * from\nDEPT join EMP using(deptno)\n \nPlan hash value: 2034389985\n \n------------------------------------------------------------------------------------------------------------------\n| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |\n------------------------------------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT     |      |      1 |        |     12 |00:00:00.01 |       7 |       |       |          |\n|   1 |  MERGE JOIN CARTESIAN|      |      1 |     14 |     12 |00:00:00.01 |       7 |       |       |          |\n|   2 |   TABLE ACCESS FULL  | DEPT |      1 |      4 |      4 |00:00:00.01 |       4 |       |       |          |\n|   3 |   BUFFER SORT        |      |      4 |      4 |     12 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|\n|*  4 |    TABLE ACCESS FULL | EMP  |      1 |      4 |      3 |00:00:00.01 |       3 |       |       |          |\n------------------------------------------------------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n   4 - filter(\"DEPT\".\"DEPTNO\"=\"EMP\".\"DEPTNO\")\n \n<\/code><\/pre>\n
Ok, then I declared my result with an inner join query, and I forced the join method with a hint to show that it is possible. But look at the result. 12 rows? Only DEPTNO 10 where the SCOTT schema has employees in 10, 20 and 30? And only 3 employees here, repeated 4 times for each department name? That’s wrong result.<\/p>\n
NEVER FORCE A CARTESIAN JOIN WITH USE_MERGE_CARTESIAN!<\/p>\n
That’s a very old bug: Bug 17064391  Wrong result with USE_MERGE_CARTESIAN hint<\/a> finally fixed in 12c (12.2 and backported in 12.1 PSU)<\/p>\n
Then how is it fixed? <\/p>\n
18c<\/h3>\n
With the fix, the hint is just ignored and a SORT MERGE JOIN is used here:<\/p>\n
\nSQL> alter session set current_schema=SCOTT statistics_level=all;\nSession altered.\n \nSQL> select \/*+ leading(DEPT) USE_MERGE_CARTESIAN(EMP) FULL(DEPT) *\/ * from DEPT join EMP using(deptno);\n \n    DEPTNO DNAME          LOC                EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM\n---------- -------------- ------------- ---------- ---------- --------- ---------- --------- ---------- ----------\n        10 ACCOUNTING     NEW YORK            7782 CLARK      MANAGER         7839 09-JUN-81       2450\n        10 ACCOUNTING     NEW YORK            7839 KING       PRESIDENT            17-NOV-81       5000\n        10 ACCOUNTING     NEW YORK            7934 MILLER     CLERK           7782 23-JAN-82       1300\n        20 RESEARCH       DALLAS              7566 JONES      MANAGER         7839 02-APR-81       2975\n        20 RESEARCH       DALLAS              7902 FORD       ANALYST         7566 03-DEC-81       3000\n        20 RESEARCH       DALLAS              7876 ADAMS      CLERK           7788 23-MAY-87       1100\n        20 RESEARCH       DALLAS              7369 SMITH      CLERK           7902 17-DEC-80        800\n        20 RESEARCH       DALLAS              7788 SCOTT      ANALYST         7566 19-APR-87       3000\n        30 SALES          CHICAGO             7521 WARD       SALESMAN        7698 22-FEB-81       1250        500\n        30 SALES          CHICAGO             7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0\n        30 SALES          CHICAGO             7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300\n        30 SALES          CHICAGO             7900 JAMES      CLERK           7698 03-DEC-81        950\n        30 SALES          CHICAGO             7698 BLAKE      MANAGER         7839 01-MAY-81       2850\n        30 SALES          CHICAGO             7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400\n \n14 rows selected.\n \nSQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));\n \nPLAN_TABLE_OUTPUT\n--------------------------------------------------------------------------------------------------------------------------\nSQL_ID  1xpfxq6pc30vq, child number 0\n-------------------------------------\nselect \/*+ leading(DEPT) USE_MERGE_CARTESIAN(EMP) FULL(DEPT) *\/ * from\nDEPT join EMP using(deptno)\n \nPlan hash value: 1407029907\n \n--------------------------------------------------------------------------------------------------------------------------\n| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |\n--------------------------------------------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT    |      |      1 |        |     14 |00:00:00.01 |      12 |     12 |       |       |          |\n|   1 |  MERGE JOIN         |      |      1 |     14 |     14 |00:00:00.01 |      12 |     12 |       |       |          |\n|   2 |   SORT JOIN         |      |      1 |      4 |      4 |00:00:00.01 |       6 |      6 |  2048 |  2048 | 2048  (0)|\n|   3 |    TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       6 |      6 |       |       |          |\n|*  4 |   SORT JOIN         |      |      4 |     14 |     14 |00:00:00.01 |       6 |      6 |  2048 |  2048 | 2048  (0)|\n|   5 |    TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       6 |      6 |       |       |          |\n--------------------------------------------------------------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n   4 - access(\"DEPT\".\"DEPTNO\"=\"EMP\".\"DEPTNO\")\n       filter(\"DEPT\".\"DEPTNO\"=\"EMP\".\"DEPTNO\")\n<\/code><\/pre>\n
So here the result is good, thanks to the fix, and we clearly see how it is fixed: the USE_MERGE_CARTESIAN hint has been ignored.<\/p>\n
And the funny thing is that when you look at the 18c documentation, the Merge Join Cartesian is not a join method anymore but a join type:
\n\"CaptureJoinMethods183\"<\/a><\/p>\n
Exactly the same paragraph, but now in join types (the ‘what’) rather than in join methods (the ‘when’).<\/p>\n
What or How?<\/h3>\n
Actually, in my opinion, it is both. When you explicitly want a cartesian join, that’s a join type described by the CROSS JOIN in the ANSI join syntax, or the lack of related predicates in the old syntax. This is ‘what’. But you may also encounter a MERGE JOIN CARTESIAN for a non-cartesian join just because the optimizer decides it is more efficient. When you have very few rows on both sides, it may be faster to start with a cartesian product on small rowsources. This can be part of star transformation where fact rows are joined back to the cartesian product of filtered dimensions in order to project the dimension attributes. This is ‘how’ it will be executed. We also see it when the optimizer underestimates the cardinalities and is followed by a long nested loop.<\/p>\n
When?<\/h3>\n
So, let’s look at the documentation “When the Optimizer Considers Cartesian Joins”:
\n\"CaptureJoinMethods183-when\"<\/a><\/p>\n