Oracle中的merge join Cartesian

在执行计划中，有时会出现CARTESIAN笛卡尔乘积,简单的说一下什么叫cartesian?就是有两个集合，每个集合的任意一个成员都要与另外一个集合的任意一个成员有关联...下面是关于cartesian的一些实验:

SQL> set linesize 2000

SQL> select * from tab;

TNAMETABTYPECLUSTERID

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

TTABLE

REP_T_LOGTABLE

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的执行效率可能会很低，对于系统的隐藏参数，一般不应该改变。

转自：http://hi.baidu.com/xiaoxiaosir/blog/item/318cec2929cae6f699250abf.html