在我以前优化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的两个表必须是小表,否则效率极差。
><