Oracle中的merge join Cartesian

在執行計畫中，有時會出現CARTESIAN笛卡爾乘積,簡單的說一下什麼叫cartesian?就是有兩個集合，每個集合的任意一個成員都要與另外一個集合的任意一個成員有關聯...下面是關於cartesian的一些實驗:

SQL> set linesize 2000

SQL> select * from tab;

TNAME                          TABTYPE CLUSTERID

------------------------------ ------- ----------

T                              TABLE

REP_T_LOG                      TABLE

SQL> select * from t,rep_t_log

Execution Plan

----------------------------------------------------------

Plan hash value: 2235198130

----------------------------------------------------------------------------------

| Id | Operation            | Name      | Rows | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT     |           | 20000 |   429K|    16   (7)| 00:00:01 |

|   1 | MERGE JOIN CARTESIAN|           | 20000 |   429K|    16   (7)| 00:00:01 |

|   2 |   TABLE ACCESS FULL | REP_T_LOG |     2 |    30 |     3   (0)| 00:00:01 |

|   3 |   BUFFER SORT        |           | 10000 | 70000 |    13   (8)| 00:00:01 |

|   4 |    TABLE ACCESS FULL | T         | 10000 | 70000 |     6   (0)| 00:00:01 |

----------------------------------------------------------------------------------

Note

-----

   - dynamic sampling used for this statement

SQL>

如果強制不允許merge join Cartesian出現，可以通過設定隱含參數“_optimizer_mjc_enabled”

SQL> alter session set "_optimizer_mjc_enabled" = false;

Session altered.

SQL> select * from t,rep_t_log;

Execution Plan

----------------------------------------------------------

Plan hash value: 4018263157

--------------------------------------------------------------------------------

| Id | Operation          | Name      | Rows | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |           | 20000 |   429K|    16   (7)| 00:00:01 |

|   1 | NESTED LOOPS      |           | 20000 |   429K|    16   (7)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| REP_T_LOG |     2 |    30 |     3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| T         | 10000 | 70000 |     6   (0)| 00:00:01 |

--------------------------------------------------------------------------------

Note

-----

   - dynamic sampling used for this statement

SQL> conn / as sysdba

Connected.

在整個系統級禁用笛卡爾乘積

SQL> alter system set "_optimizer_mjc_enabled" = false;

System altered.

SQL> conn test/test

Connected.

SQL> set autotrace trace exp

SQL> select * from t,rep_t_log;

Execution Plan

----------------------------------------------------------

Plan hash value: 4018263157

--------------------------------------------------------------------------------

| Id | Operation          | Name      | Rows | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |           | 20000 |   429K|    16   (7)| 00:00:01 |

|   1 | NESTED LOOPS      |           | 20000 |   429K|    16   (7)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| REP_T_LOG |     2 |    30 |     3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| T         | 10000 | 70000 |     6   (0)| 00:00:01 |

--------------------------------------------------------------------------------

Note

-----

   - dynamic sampling used for this statement

當出現merge join Cartesian時，sql的執行效率可能會很低，對於系統的隱藏參數，一般不應該改變。