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的执行效率可能会很低，对于系统的隐藏参数，一般不应该改变。 转自：http://hi.baidu.com/xiaoxiaosir/blog/item/318cec2929cae6f699250abf.html