关于MERGE JOIN CARTESIAN

在我以前优化SQL的过程中一直把MERGE JOIN CARTESIAN当成是存在问题的执行方式。
但是今天仔细想了下，为什么oracle会选择这样一种执行方式。
oracle没有那么傻，肯定是存在什么原因的。

下面通过个小测试来说MERGE JOIN CARTESIAN的适用范围：

finods@DWALI>create table test as select * from all_objects;

Table created.

finods@DWALI>create table test1 as select * from all_users where username=’FINODS’;

Table created.

finods@DWALI>CREATE TABLE TEST2 AS SELECT ‘TABLE’ OBJECT_TYPE FROM DUAL;

Table created.

finods@DWALI>analyze table test compute statistics;

Table analyzed.

finods@DWALI>analyze table test1 compute statistics;

Table analyzed.

finods@DWALI>analyze table test2 compute statistics;

Table analyzed.

finods@DWALI>set autot traceonly EXP

finods@DWALI>select a.* from test a,test1 b,test2 c where a.owner=b.username and a.object_type=c.object_type;

Execution Plan
———————————————————-
Plan hash value: 3403251386

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 68 | 7140 | 50 (18)| 00:00:01 |
|* 1 | HASH JOIN | | 68 | 7140 | 50 (18)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN| | 1 | 11 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TEST1 | 1 | 6 | 2 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 1 | 5 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TEST2 | 1 | 5 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | TEST | 93352 | 8569K| 44 (16)| 00:00:01 |
——————————————————————————-

Predicate Information (identified by operation id):
—————————————————

1 – access(“A”.”OWNER”=”B”.”USERNAME” AND
“A”.”OBJECT_TYPE”=”C”.”OBJECT_TYPE”)

我们看到，在对三张进行分析后，oracle选择的执行方式就是MERGE JOIN CARTESIAN。
在这种情况选择MERGE JOIN CARTESIAN是很有好处的，用两张小表的MERGE JOIN CARTESIAN来代替大表的hash join。
通过提示来限制三张表走hash join的效果如下：

finods@DWALI>select /*+ordered*/ a.* from test a,test1 b,test2 c where a.owner=b.username and a.object_type=c.object_type;

Execution Plan
———————————————————-
Plan hash value: 993621649

————————————————————————————-
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
————————————————————————————-
| 0 | SELECT STATEMENT | | 68 | 7140 | | 165 (7)| 00:00:02 |
|* 1 | HASH JOIN | | 68 | 7140 | | 165 (7)| 00:00:02 |
| 2 | TABLE ACCESS FULL | TEST2 | 1 | 5 | | 2 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 1761 | 171K| 9664K| 163 (7)| 00:00:02 |
| 4 | TABLE ACCESS FULL| TEST | 93352 | 8569K| | 44 (16)| 00:00:01 |
| 5 | TABLE ACCESS FULL| TEST1 | 1 | 6 | | 2 (0)| 00:00:01 |
————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

1 – access(“A”.”OBJECT_TYPE”=”C”.”OBJECT_TYPE”)
3 – access(“A”.”OWNER”=”B”.”USERNAME”)

上面的执行计划不是太好，我们调换下join的顺序在看看效果。

finods@DWALI>select /*+ordered swap_join_inputs(b) swap_join_inputs(c)*/ a.*
2 from test a,test1 b,test2 c where a.owner=b.username and a.object_type=c.object_type;

Execution Plan
———————————————————-
Plan hash value: 3882301066

—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 68 | 7140 | 51 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 68 | 7140 | 51 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL | TEST2 | 1 | 5 | 2 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 1761 | 171K| 48 (19)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TEST1 | 1 | 6 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| TEST | 93352 | 8569K| 44 (16)| 00:00:01 |
—————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – access(“A”.”OBJECT_TYPE”=”C”.”OBJECT_TYPE”)
3 – access(“A”.”OWNER”=”B”.”USERNAME”)

这个时候的COST和最开始的时候差不多了。但是两者执行方式却有很大的不同。

第一主要执行两次JOIN：
1,小表和小表的MERGE JOIN CARTESIAN
2,小表和大表的HASH JOIN

第二主要执行两次JOIN：
1,小表和大表的HASH JOIN
2,小表和大表的HASH JOIN

到底哪种执行好呢？
这个还是得看实际的情况，不同的数据分布会有不同的结果。

但是有一点可以肯定的是，MERGE JOIN CARTESIAN的两个表必须是小表，否则效率极差。
><

来源：http://www.oratea.net/?p=346