Oracle排序合并连接
为了弥补嵌套循环连接的不足,即在连接数据量比较大时,为了缩减嵌套循环连接中的大量随机读取,以及连接列不存在索引时,可以替代嵌套循环连接。
但是为了实现表连接,它必须要先对两个表中的将要执行连接的行进行排序。虽然这种方式提高了连接效率,但由于排序的存在,增加了连接的代价。如果能够快速的完成排序操作(排序内存空间大小(sort_area_size),并行),那么在处理大数据量时,排序合并连接将是不错的选择。
如果在连接列上有索引,并且利用该索引读取数据,由于索引是有序的,可以消除外部行源的排序操作。
排序合并连接没有嵌套循环连接中驱动表的概念。
- SQL> createtable t1 asselect * from dba_objects;
- 表已创建。
- SQL> createtable t2 asselect * from dba_objects;
- 表已创建。
- SQL> set autot traceonly
- SQL> set linesize 120
- SQL> select /*+ use_merge(t1,t2) +*/ t1.object_id,t1.object_name,t2.object_name
- 2 from t1,t2
- 3 where t1.object_id=t2.object_id;
- 已选择50425行。
- 执行计划
- ----------------------------------------------------------
- Plan hash value: 412793182
- ------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
- ------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 53430 | 8244K| | 2379 (2)| 00:00:29 |
- | 1 | MERGE JOIN | | 53430 | 8244K| | 2379 (2)| 00:00:29 |
- | 2 | SORT JOIN | | 53430 | 4122K| 9304K| 1152 (2)| 00:00:14 |
- | 3 | TABLE ACCESS FULL| T1 | 53430 | 4122K| | 161 (2)| 00:00:02 |
- |* 4 | SORT JOIN | | 57436 | 4431K| 9M| 1227 (2)| 00:00:15 |
- | 5 | TABLE ACCESS FULL| T2 | 57436 | 4431K| | 161 (2)| 00:00:02 |
- ------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
- filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
- Note
- -----
- - dynamic sampling used for this statement
- 统计信息
- ----------------------------------------------------------
- 0 recursive calls
- 0 db block gets
- 1396 consistent gets
- 0 physical reads
- 0 redo size
- 3166055 bytes sent via SQL*Net to client
- 37356 bytes received via SQL*Net from client
- 3363 SQL*Net roundtrips to/from client
- 2 sorts (memory)
- 0 sorts (disk)
- 50425 rows processed
- SQL> createindex ind_t1_object_id on t1(object_id);
- 索引已创建。
- SQL> createindex ind_t2_object_id on t2(object_id);
- 索引已创建。
- /**由于索引是有序的,可以消除外部行源的排序操作*/
- SQL> select /*+ use_merge(t1,t2) +*/ t1.object_id,t1.object_name,t2.object_name
- 2 from t1,t2
- 3 where t1.object_id=t2.object_id ;
- 已选择50425行。
- 执行计划
- ----------------------------------------------------------
- Plan hash value: 3739185171
- ---------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
- ---------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 53430 | 8244K| | 2031 (1)| 00:00:25 |
- | 1 | MERGE JOIN | | 53430 | 8244K| | 2031 (1)| 00:00:25 |
- | 2 | TABLE ACCESS BYINDEX ROWID| T2 | 57436 | 4431K| | 879 (1)| 00:00:11 |
- | 3 | INDEXFULL SCAN | IND_T2_OBJECT_ID | 57436 | | | 125 (2)| 00:00:02 |
- |* 4 | SORT JOIN | | 53430 | 4122K| 9304K| 1152 (2)| 00:00:14 |
- | 5 | TABLE ACCESS FULL | T1 | 53430 | 4122K| | 161 (2)| 00:00:02 |
- ---------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
- filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
- Note
- -----
- - dynamic sampling used for this statement
- 统计信息
- ----------------------------------------------------------
- 8 recursive calls
- 0 db block gets
- 8384 consistent gets
- 117 physical reads
- 0 redo size
- 3166055 bytes sent via SQL*Net to client
- 37356 bytes received via SQL*Net from client
- 3363 SQL*Net roundtrips to/from client
- 3 sorts (memory)
- 0 sorts (disk)
- 50425 rows processed
- SQL> select /*+ use_merge(t1,t2) index(t1 ind_t1_object_id) +*/ t1.object_id,t1.object_name,t2.object_name
- 2 from t1,t2
- 3 where t1.object_id=t2.object_id ;
- 已选择50425行。
- 执行计划
- ----------------------------------------------------------
- Plan hash value: 3856170417
- ---------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
- ---------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 53430 | 8244K| | 2107 (1)| 00:00:26 |
- | 1 | MERGE JOIN | | 53430 | 8244K| | 2107 (1)| 00:00:26 |
- | 2 | TABLE ACCESS BYINDEX ROWID| T1 | 53430 | 4122K| | 881 (1)| 00:00:11 |
- | 3 | INDEXFULL SCAN | IND_T1_OBJECT_ID | 53430 | | | 125 (2)| 00:00:02 |
- |* 4 | SORT JOIN | | 57436 | 4431K| 9M| 1227 (2)| 00:00:15 |
- | 5 | TABLE ACCESS FULL | T2 | 57436 | 4431K| | 161 (2)| 00:00:02 |
- ---------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
- filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
- Note
- -----
- - dynamic sampling used for this statement
- 统计信息
- ----------------------------------------------------------
- 7 recursive calls
- 0 db block gets
- 8386 consistent gets
- 0 physical reads
- 0 redo size
- 3166055 bytes sent via SQL*Net to client
- 37356 bytes received via SQL*Net from client
- 3363 SQL*Net roundtrips to/from client
- 3 sorts (memory)
- 0 sorts (disk)
- 50425 rows processed
- SQL> select /*+ use_merge(t1,t2) index(t1 ind_t1_object_id) index(t2 ind_t2_object_id) +*/ t1.object_id,t1.object_name,
- 2.object_name
- 2 from t1,t2
- 3 where t1.object_id=t2.object_id ;
- 已选择50425行。
- 执行计划
- ----------------------------------------------------------
- Plan hash value: 3655247094
- ----------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
- ----------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 53430 | 8244K| | 2750 (1)| 00:00:34 |
- | 1 | MERGE JOIN | | 53430 | 8244K| | 2750 (1)| 00:00:34 |
- | 2 | TABLE ACCESS BYINDEX ROWID | T2 | 57436 | 4431K| | 879 (1)| 00:00:11 |
- | 3 | INDEXFULL SCAN | IND_T2_OBJECT_ID | 57436 | | | 125 (2)| 00:00:02 |
- |* 4 | SORT JOIN | | 53430 | 4122K| 9304K| 1872 (1)| 00:00:23 |
- | 5 | TABLE ACCESS BYINDEX ROWID| T1 | 53430 | 4122K| | 881 (1)| 00:00:11 |
- | 6 | INDEXFULL SCAN | IND_T1_OBJECT_ID | 53430 | | | 125 (2)| 00:00:02 |
- ----------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
- filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
- Note
- -----
- - dynamic sampling used for this statement
- 统计信息
- ----------------------------------------------------------
- 7 recursive calls
- 0 db block gets
- 8551 consistent gets
- 0 physical reads
- 0 redo size
- 3166055 bytes sent via SQL*Net to client
- 37356 bytes received via SQL*Net from client
- 3363 SQL*Net roundtrips to/from client
- 3 sorts (memory)
- 0 sorts (disk)
- 50425 rows processed
相关推荐
娜娜 2020-07-18
talkingDB 2020-07-05
zhangchaoming 2020-06-21
zhangchaoming 2020-05-27
LuoXinLoves 2020-05-04
康慧欣 2020-05-02
Justdoit00 2020-04-26
oraclemch 2019-12-29
talkingDB 2020-01-11
Streamoutput 2020-01-10
Streamoutput 2019-12-02
oraclemch 2019-12-01
der 2019-11-27
jbossllx 2019-11-05
静思苑 2019-01-15
人勤阡陌绿 2019-03-27
Cavlier 2019-03-19
EricRay 2017-05-26