Oracle排序合并连接


为了弥补嵌套循环连接的不足，即在连接数据量比较大时，为了缩减嵌套循环连接中的大量随机读取，以及连接列不存在索引时，可以替代嵌套循环连接。

但是为了实现表连接，它必须要先对两个表中的将要执行连接的行进行排序。虽然这种方式提高了连接效率，但由于排序的存在，增加了连接的代价。如果能够快速的完成排序操作（排序内存空间大小(sort_area_size)，并行），那么在处理大数据量时，排序合并连接将是不错的选择。

如果在连接列上有索引，并且利用该索引读取数据，由于索引是有序的，可以消除外部行源的排序操作。

排序合并连接没有嵌套循环连接中驱动表的概念。

  1. SQL> createtable t1 asselect * from dba_objects;
  3. 表已创建。
  5. SQL> createtable t2 asselect * from dba_objects;
  7. 表已创建。
  9. SQL> set autot traceonly
  10. SQL> set linesize 120
  11. SQL> select /*+ use_merge(t1,t2) +*/ t1.object_id,t1.object_name,t2.object_name
  12. 2 from t1,t2
  13. 3 where t1.object_id=t2.object_id;
  15. 已选择50425行。
  18. 执行计划
  19. ----------------------------------------------------------
  20. Plan hash value: 412793182
  22. ------------------------------------------------------------------------------------
  23. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
  24. ------------------------------------------------------------------------------------
  25. | 0 | SELECT STATEMENT | | 53430 | 8244K| | 2379 (2)| 00:00:29 |
  26. | 1 | MERGE JOIN | | 53430 | 8244K| | 2379 (2)| 00:00:29 |
  27. | 2 | SORT JOIN | | 53430 | 4122K| 9304K| 1152 (2)| 00:00:14 |
  28. | 3 | TABLE ACCESS FULL| T1 | 53430 | 4122K| | 161 (2)| 00:00:02 |
  29. |* 4 | SORT JOIN | | 57436 | 4431K| 9M| 1227 (2)| 00:00:15 |
  30. | 5 | TABLE ACCESS FULL| T2 | 57436 | 4431K| | 161 (2)| 00:00:02 |
  31. ------------------------------------------------------------------------------------
  33. Predicate Information (identified by operation id):
  34. ---------------------------------------------------
  36. 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  37. filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  39. Note
  40. -----
  41. - dynamic sampling used for this statement
  44. 统计信息
  45. ----------------------------------------------------------
  46. 0 recursive calls
  47. 0 db block gets
  48. 1396 consistent gets
  49. 0 physical reads
  50. 0 redo size
  51. 3166055 bytes sent via SQL*Net to client
  52. 37356 bytes received via SQL*Net from client
  53. 3363 SQL*Net roundtrips to/from client
  54. 2 sorts (memory)
  55. 0 sorts (disk)
  56. 50425 rows processed
  58. SQL> createindex ind_t1_object_id on t1(object_id);
  60. 索引已创建。
  62. SQL> createindex ind_t2_object_id on t2(object_id);
  64. 索引已创建。
  66. /**由于索引是有序的，可以消除外部行源的排序操作*/
  67. SQL> select /*+ use_merge(t1,t2) +*/ t1.object_id,t1.object_name,t2.object_name
  68. 2 from t1,t2
  69. 3 where t1.object_id=t2.object_id ;
  71. 已选择50425行。
  74. 执行计划
  75. ----------------------------------------------------------
  76. Plan hash value: 3739185171
  78. ---------------------------------------------------------------------------------------------------------
  79. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
  80. ---------------------------------------------------------------------------------------------------------
  81. | 0 | SELECT STATEMENT | | 53430 | 8244K| | 2031 (1)| 00:00:25 |
  82. | 1 | MERGE JOIN | | 53430 | 8244K| | 2031 (1)| 00:00:25 |
  83. | 2 | TABLE ACCESS BYINDEX ROWID| T2 | 57436 | 4431K| | 879 (1)| 00:00:11 |
  84. | 3 | INDEXFULL SCAN | IND_T2_OBJECT_ID | 57436 | | | 125 (2)| 00:00:02 |
  85. |* 4 | SORT JOIN | | 53430 | 4122K| 9304K| 1152 (2)| 00:00:14 |
  86. | 5 | TABLE ACCESS FULL | T1 | 53430 | 4122K| | 161 (2)| 00:00:02 |
  87. ---------------------------------------------------------------------------------------------------------
  89. Predicate Information (identified by operation id):
  90. ---------------------------------------------------
  92. 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  93. filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  95. Note
  96. -----
  97. - dynamic sampling used for this statement
  100. 统计信息
  101. ----------------------------------------------------------
  102. 8 recursive calls
  103. 0 db block gets
  104. 8384 consistent gets
  105. 117 physical reads
  106. 0 redo size
  107. 3166055 bytes sent via SQL*Net to client
  108. 37356 bytes received via SQL*Net from client
  109. 3363 SQL*Net roundtrips to/from client
  110. 3 sorts (memory)
  111. 0 sorts (disk)
  112. 50425 rows processed
  114. SQL> select /*+ use_merge(t1,t2) index(t1 ind_t1_object_id) +*/ t1.object_id,t1.object_name,t2.object_name
  115. 2 from t1,t2
  116. 3 where t1.object_id=t2.object_id ;
  118. 已选择50425行。
  121. 执行计划
  122. ----------------------------------------------------------
  123. Plan hash value: 3856170417
  125. ---------------------------------------------------------------------------------------------------------
  126. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
  127. ---------------------------------------------------------------------------------------------------------
  128. | 0 | SELECT STATEMENT | | 53430 | 8244K| | 2107 (1)| 00:00:26 |
  129. | 1 | MERGE JOIN | | 53430 | 8244K| | 2107 (1)| 00:00:26 |
  130. | 2 | TABLE ACCESS BYINDEX ROWID| T1 | 53430 | 4122K| | 881 (1)| 00:00:11 |
  131. | 3 | INDEXFULL SCAN | IND_T1_OBJECT_ID | 53430 | | | 125 (2)| 00:00:02 |
  132. |* 4 | SORT JOIN | | 57436 | 4431K| 9M| 1227 (2)| 00:00:15 |
  133. | 5 | TABLE ACCESS FULL | T2 | 57436 | 4431K| | 161 (2)| 00:00:02 |
  134. ---------------------------------------------------------------------------------------------------------
  136. Predicate Information (identified by operation id):
  137. ---------------------------------------------------
  139. 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  140. filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  142. Note
  143. -----
  144. - dynamic sampling used for this statement
  147. 统计信息
  148. ----------------------------------------------------------
  149. 7 recursive calls
  150. 0 db block gets
  151. 8386 consistent gets
  152. 0 physical reads
  153. 0 redo size
  154. 3166055 bytes sent via SQL*Net to client
  155. 37356 bytes received via SQL*Net from client
  156. 3363 SQL*Net roundtrips to/from client
  157. 3 sorts (memory)
  158. 0 sorts (disk)
  159. 50425 rows processed
  161. SQL> select /*+ use_merge(t1,t2) index(t1 ind_t1_object_id) index(t2 ind_t2_object_id) +*/ t1.object_id,t1.object_name,
  162. 2.object_name
  163. 2 from t1,t2
  164. 3 where t1.object_id=t2.object_id ;
  166. 已选择50425行。
  169. 执行计划
  170. ----------------------------------------------------------
  171. Plan hash value: 3655247094
  173. ----------------------------------------------------------------------------------------------------------
  174. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
  175. ----------------------------------------------------------------------------------------------------------
  176. | 0 | SELECT STATEMENT | | 53430 | 8244K| | 2750 (1)| 00:00:34 |
  177. | 1 | MERGE JOIN | | 53430 | 8244K| | 2750 (1)| 00:00:34 |
  178. | 2 | TABLE ACCESS BYINDEX ROWID | T2 | 57436 | 4431K| | 879 (1)| 00:00:11 |
  179. | 3 | INDEXFULL SCAN | IND_T2_OBJECT_ID | 57436 | | | 125 (2)| 00:00:02 |
  180. |* 4 | SORT JOIN | | 53430 | 4122K| 9304K| 1872 (1)| 00:00:23 |
  181. | 5 | TABLE ACCESS BYINDEX ROWID| T1 | 53430 | 4122K| | 881 (1)| 00:00:11 |
  182. | 6 | INDEXFULL SCAN | IND_T1_OBJECT_ID | 53430 | | | 125 (2)| 00:00:02 |
  183. ----------------------------------------------------------------------------------------------------------
  185. Predicate Information (identified by operation id):
  186. ---------------------------------------------------
  188. 4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  189. filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  191. Note
  192. -----
  193. - dynamic sampling used for this statement
  196. 统计信息
  197. ----------------------------------------------------------
  198. 7 recursive calls
  199. 0 db block gets
  200. 8551 consistent gets
  201. 0 physical reads
  202. 0 redo size
  203. 3166055 bytes sent via SQL*Net to client
  204. 37356 bytes received via SQL*Net from client
  205. 3363 SQL*Net roundtrips to/from client
  206. 3 sorts (memory)
  207. 0 sorts (disk)
  208. 50425 rows processed

相关内容