Merge Sort Join连接的优势就是可以一定程度上减少随机读的情况。就是逻辑读少,但是sort 排序多.
hash join 逻辑读多,但是sort 排序少.
hash join 是 Merge Sort Join的一个升级补充
1.建立测试表
SQL> create table system.t1(id number(10),name varchar(10));
Table created.
SQL> begin
2 for i in 1 .. 10000 loop
3 insert into system.t1(id,name) select trunc(dbms_random.value(0,10000)) ,'t1' from dual;
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> create index system.t1_idx on system.t1(id);
Index created.
SQL> create table system.t2(id number(10),name varchar(10));
Table created.
SQL> begin
2 for i in 1 .. 100000 loop
3 insert into system.t2(id,name) select trunc(dbms_random.value(0,100000)) ,'t2' from dual;
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> create index system.t2_idx on system.t2(id);
Index created.
SQL> execute dbms_stats.gather_table_stats('SYSTEM','T1');
PL/SQL procedure successfully completed.
SQL> execute dbms_stats.gather_table_stats('SYSTEM','T2');
PL/SQL procedure successfully completed.
两个表差了一个数量级
2.做一下merge测试
SQL> select /*+use_merge(t1,t2)*/* from system.t1,system.t2 where t1.id=t2.id;
9900 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 412793182
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 15680 | 229K| | 426 (2)| 00:00:
06 |
| 1 | MERGE JOIN | | 15680 | 229K| | 426 (2)| 00:00:
06 |
| 2 | SORT JOIN | | 10000 | 70000 | | 8 (13)| 00:00:
01 |
| 3 | TABLE ACCESS FULL| T1 | 10000 | 70000 | | 7 (0)| 00:00:
01 |
|* 4 | SORT JOIN | | 100K| 781K| 3160K| 418 (2)| 00:00:
06 |
| 5 | TABLE ACCESS FULL| T2 | 100K| 781K| | 52 (2)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."ID"="T2"."ID")
filter("T1"."ID"="T2"."ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
207 consistent gets
0 physical reads
0 redo size
202601 bytes sent via SQL*Net to client
7772 bytes received via SQL*Net from client
661 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
9900 rows processed
交换一个表
SQL> select /*+use_merge(t2,t1)*/* from system.t1,system.t2 where t1.id=t2.id;
9900 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 412793182
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 15680 | 229K| | 426 (2)| 00:00:
06 |
| 1 | MERGE JOIN | | 15680 | 229K| | 426 (2)| 00:00:
06 |
| 2 | SORT JOIN | | 10000 | 70000 | | 8 (13)| 00:00:
01 |
| 3 | TABLE ACCESS FULL| T1 | 10000 | 70000 | | 7 (0)| 00:00:
01 |
|* 4 | SORT JOIN | | 100K| 781K| 3160K| 418 (2)| 00:00:
06 |
| 5 | TABLE ACCESS FULL| T2 | 100K| 781K| | 52 (2)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."ID"="T2"."ID")
filter("T1"."ID"="T2"."ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
207 consistent gets
0 physical reads
0 redo size
202601 bytes sent via SQL*Net to client
7772 bytes received via SQL*Net from client
661 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
9900 rows processed
我们也发现,交换了 use_merge(t2,t1) t1与t2的位置,但执行计划是不变的.
我们继续查看逻辑读与sorts(memory), 207 是 逻辑读 , 2 是sorts (memory)
3.做一下nest loop测试
SQL> select /*+ leading(t1) use_nl(t2)*/* from system.t1,system.t2 where t1.id=t2.id;
9900 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 15680 | 229K| 30018 (1)| 00:
06:01 |
| 1 | NESTED LOOPS | | | | |
|
| 2 | NESTED LOOPS | | 15680 | 229K| 30018 (1)| 00:
06:01 |
| 3 | TABLE ACCESS FULL | T1 | 10000 | 70000 | 7 (0)| 00:
00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 2 | | 1 (0)| 00:
00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 2 | 16 | 3 (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."ID"="T2"."ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
21883 consistent gets
0 physical reads
0 redo size
220929 bytes sent via SQL*Net to client
7772 bytes received via SQL*Net from client
661 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9900 rows processed
我们从逻辑读与 sorts(memory) 两个指标进行对比
此次 逻辑读为 21883,显然比较高, sorts(meory) 为零,显然比较底.
hash join 逻辑读多,但是sort 排序少.
hash join 是 Merge Sort Join的一个升级补充
1.建立测试表
SQL> create table system.t1(id number(10),name varchar(10));
Table created.
SQL> begin
2 for i in 1 .. 10000 loop
3 insert into system.t1(id,name) select trunc(dbms_random.value(0,10000)) ,'t1' from dual;
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> create index system.t1_idx on system.t1(id);
Index created.
SQL> create table system.t2(id number(10),name varchar(10));
Table created.
SQL> begin
2 for i in 1 .. 100000 loop
3 insert into system.t2(id,name) select trunc(dbms_random.value(0,100000)) ,'t2' from dual;
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> create index system.t2_idx on system.t2(id);
Index created.
SQL> execute dbms_stats.gather_table_stats('SYSTEM','T1');
PL/SQL procedure successfully completed.
SQL> execute dbms_stats.gather_table_stats('SYSTEM','T2');
PL/SQL procedure successfully completed.
两个表差了一个数量级
2.做一下merge测试
SQL> select /*+use_merge(t1,t2)*/* from system.t1,system.t2 where t1.id=t2.id;
9900 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 412793182
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 15680 | 229K| | 426 (2)| 00:00:
06 |
| 1 | MERGE JOIN | | 15680 | 229K| | 426 (2)| 00:00:
06 |
| 2 | SORT JOIN | | 10000 | 70000 | | 8 (13)| 00:00:
01 |
| 3 | TABLE ACCESS FULL| T1 | 10000 | 70000 | | 7 (0)| 00:00:
01 |
|* 4 | SORT JOIN | | 100K| 781K| 3160K| 418 (2)| 00:00:
06 |
| 5 | TABLE ACCESS FULL| T2 | 100K| 781K| | 52 (2)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."ID"="T2"."ID")
filter("T1"."ID"="T2"."ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
207 consistent gets
0 physical reads
0 redo size
202601 bytes sent via SQL*Net to client
7772 bytes received via SQL*Net from client
661 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
9900 rows processed
交换一个表
SQL> select /*+use_merge(t2,t1)*/* from system.t1,system.t2 where t1.id=t2.id;
9900 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 412793182
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 15680 | 229K| | 426 (2)| 00:00:
06 |
| 1 | MERGE JOIN | | 15680 | 229K| | 426 (2)| 00:00:
06 |
| 2 | SORT JOIN | | 10000 | 70000 | | 8 (13)| 00:00:
01 |
| 3 | TABLE ACCESS FULL| T1 | 10000 | 70000 | | 7 (0)| 00:00:
01 |
|* 4 | SORT JOIN | | 100K| 781K| 3160K| 418 (2)| 00:00:
06 |
| 5 | TABLE ACCESS FULL| T2 | 100K| 781K| | 52 (2)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."ID"="T2"."ID")
filter("T1"."ID"="T2"."ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
207 consistent gets
0 physical reads
0 redo size
202601 bytes sent via SQL*Net to client
7772 bytes received via SQL*Net from client
661 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
9900 rows processed
我们也发现,交换了 use_merge(t2,t1) t1与t2的位置,但执行计划是不变的.
我们继续查看逻辑读与sorts(memory), 207 是 逻辑读 , 2 是sorts (memory)
3.做一下nest loop测试
SQL> select /*+ leading(t1) use_nl(t2)*/* from system.t1,system.t2 where t1.id=t2.id;
9900 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 15680 | 229K| 30018 (1)| 00:
06:01 |
| 1 | NESTED LOOPS | | | | |
|
| 2 | NESTED LOOPS | | 15680 | 229K| 30018 (1)| 00:
06:01 |
| 3 | TABLE ACCESS FULL | T1 | 10000 | 70000 | 7 (0)| 00:
00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 2 | | 1 (0)| 00:
00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 2 | 16 | 3 (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."ID"="T2"."ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
21883 consistent gets
0 physical reads
0 redo size
220929 bytes sent via SQL*Net to client
7772 bytes received via SQL*Net from client
661 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9900 rows processed
我们从逻辑读与 sorts(memory) 两个指标进行对比
此次 逻辑读为 21883,显然比较高, sorts(meory) 为零,显然比较底.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7569309/viewspace-2135078/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7569309/viewspace-2135078/