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