oracle: Cartesian Joins

http://aguppi.blogspot.com/2011/07/oracle-table-joins.html
cbo-access-path

«… используется в случае, когда одна или несколько таблиц не имеют никаких условий (join conditions) для соединения с другими таблицами запроса. Оптимизатор соединяет каждую строку первого источника данных с каждой строкой другого, создавая картезианское произведение (Cartesian product) двух наборов данных»

SQL> select job, dname from emp cross join dept;
SQL> select job, dname from emp, dept;

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    56 |  1008 |    10   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|      |    56 |  1008 |    10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | DEPT |     4 |    40 |     3   (0)| 00:00:01 |
|   3 |   BUFFER SORT        |      |    14 |   112 |     7   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | EMP  |    14 |   112 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

SQL> select /*+ ORDERED*/ * from dept d, bonus b, emp e
 2  where d.deptno = e.deptno
 3  and b.ename = e.ename;

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    96 |    10  (10)| 00:00:01 |
|*  1 |  HASH JOIN            |       |     1 |    96 |    10  (10)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|       |     1 |    59 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | DEPT  |     4 |    80 |     3   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |       |     1 |    39 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | BONUS |     1 |    39 |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL   | EMP   |    14 |   518 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

В последнем примере благодаря подсказке ORDERED оптимизатор первым делом соединяет таблицы DEPT и BONUS, не имеющих по условиям запроса никаких условий для соединения (join keys), следовательно, единственной возможной операцией оказывается Join Cartesian.

В случае, когда две небольшие таблицы (DEPT и BONUS) соединяются через условия d.deptno = e.deptno and b.ename = e.ename к «большой» таблице EMP и имеются дополнительные условия на столбцы небольших таблиц (фильтры dept.loc = ‘CHICAGO’ and bonus.comm > 30), оптимизатор по соображениям избирательности (selectivity) без всяких подсказок выбирает Merge Join Cartesian небольших таблиц с последующим соединением (Hash join) с большой таблицей:

SQL> exec dbms_stats.set_table_stats(ownname => 'SCOTT',tabname => 'EMP', numrows => 100000);

PL/SQL procedure successfully completed.

SQL> select * from dept d, bonus b, emp e
 2  where d.deptno = e.deptno and d.loc = 'CHICAGO'
 3  and b.ename = e.ename and b.comm > 30;

Execution Plan
-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |  2381 |   174K|    16  (44)| 00:00:01 |
|*  1 |  HASH JOIN            |       |  2381 |   174K|    16  (44)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|       |     1 |    38 |     6   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL  | DEPT  |     1 |    18 |     3   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |       |     1 |    20 |     3   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL | BONUS |     1 |    20 |     3   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL   | EMP   |   100K|  3613K|     8  (63)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 1 - access("D"."DEPTNO"="E"."DEPTNO" AND "B"."ENAME"="E"."ENAME")
 3 - filter("D"."LOC"='CHICAGO')
 5 - filter("B"."COMM">30)

Хинты
Можно использовать ORDERED