Oracle表三种连接方式(sql优化）(四)
OIN方式：
a）RBO模式
b）不等价关联(>,<,>=,<=,<>)
c）bHASH_JOIN_ENABLED=false
d）数据源已排序
e）Merge Join 是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配，因为merge join需要做更多的排序，所以消耗的资源更多。
f) like ,not like
通常来讲，能够使用merge join的地方，hash join都可以发挥更好的性能
可用USE_MERGE(table_name1 table_name2)提示强制使用sort merge join。
例子：
SQL> drop table ttt purge;
表已删除。
SQL> drop table ttt1 purge;
表已删除。
SQL> create table ttt as select * from user_tables order by table_name desc;
表已创建。
SQL> create table ttt1 as select * from user_tables where table_name like '%ACCESS%' order by table_name desc ;
表已创建。
当我们执行等值连接时此时为hash join:
SQL> select a.table_name,b.table_name from ttt a,ttt1 b
2 where a.table_name=b.table_name;
已选择1913行。
执行计划
----------------------------------------------------------
Plan hash value: 1061339121
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1798 | 61132 | 40 (3)| 00:00:01 |
|* 1 | HASH JOIN | | 1798 | 61132 | 40 (3)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TTT | 1798 | 30566 | 20 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TTT1 | 1944 | 33048 | 19 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."TABLE_NAME"="B"."TABLE_NAME")
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
16 recursive calls
0 db block gets
377 consistent gets
117 physical reads
0 redo size
84520 bytes sent via SQL*Net to client
1813 bytes received via SQL*Net from client
129 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1913 rows processed
当我们在等值连接的基础上加上限制条件>,就变sort merge join
SQL> select a.table_name,b.table_name,b.tablespace_name from ttt a,ttt1 b
2 where a.table_name > b.table_name;
已选择10581行。
执行计划
----------------------------------------------------------
Plan hash value: 200774751
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 768 | 39936 | 25 (8)| 00:00:01 |
| 1 | MERGE JOIN | | 768 | 39936 | 25 (8)| 00:00:01 |
| 2 | SORT JOIN | | 8 | 272 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TTT1 | 8 | 272 | 3 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 1921 | 34578 | 21 (5)| 00:00:01 |
| 5 | TABLE ACCESS FULL| TTT | 1921 | 34578 | 20 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------