Home > Developer > Database Administrator

Oracle sort merge connections

Source: Internet
Author: User
Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

To make up for the limitations of nested loop connections, that is, when the data volume is large, to reduce the number of random reads in nested loop connections, and when the connection column does not have an index, nested loop connections can be replaced.

However, to achieve table join, it must first sort the rows in the two tables that will be connected. Although this method improves the connection efficiency, the cost of connection is increased due to the existence of sorting. If you can quickly complete the sorting operation (sort_area_size) in parallel, it is a good choice to sort and merge connections when processing large amounts of data.

If an index exists in the connected column and the index is used to read data, the sorting operation of external row sources can be eliminated because the index is ordered.

The sort and merge joins do not have the concept of driving tables in nested loop joins.

  1. SQL> createtable t1 asselect * from dba_objects;
  3. The table has been created.
  5. SQL> createtable t2 asselect * from dba_objects;
  7. The table has been created.
  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. Row 50425 has been selected.
  18. Execution Plan
  19. ----------------------------------------------------------
  20. Plan hash value: 412793182
  22. Bytes ------------------------------------------------------------------------------------
  23. | Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time |
  24. Bytes ------------------------------------------------------------------------------------
  25. | 0 | select statement | 53430 | 8244K 4k | 2379 (2) | 00:00:29 |
  26. | 1 | merge join | 53430 | 8244K 4k | 2379 (2) | 00:00:29 |
  27. | 2 | sort join | 53430 | 4122K | 9304K | 1152 (2) | 00:00:14 |
  28. | 3 | table access full | T1 | 53430 | 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. Bytes ------------------------------------------------------------------------------------
  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. Statistics
  45. ----------------------------------------------------------
  46. 0 recursive cballs
  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 encoded ed 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. The index has been created.
  62. SQL> createindex ind_t2_object_id on t2 (object_id );
  64. The index has been created.
  66. /** Because the index is ordered, sorting operations on external row sources can be eliminated */
  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. Row 50425 has been selected.
  74. Execution Plan
  75. ----------------------------------------------------------
  76. Plan hash value: 3739185171
  78. Bytes ---------------------------------------------------------------------------------------------------------
  79. | Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time |
  80. Bytes ---------------------------------------------------------------------------------------------------------
  81. | 0 | select statement | 53430 | 8244K 4k | 2031 (1) | 00:00:25 |
  82. | 1 | merge join | 53430 | 8244K 4k | 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 | 161 | (2) | 00:00:02 |
  87. Bytes ---------------------------------------------------------------------------------------------------------
  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. Statistics
  101. ----------------------------------------------------------
  102. 8 recursive cballs
  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 encoded ed 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. Row 50425 has been selected.
  121. Execution Plan
  122. ----------------------------------------------------------
  123. Plan hash value: 3856170417
  125. Bytes ---------------------------------------------------------------------------------------------------------
  126. | Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time |
  127. Bytes ---------------------------------------------------------------------------------------------------------
  128. | 0 | select statement | 53430 | 8244K 4k | 2107 (1) | 00:00:26 |
  129. | 1 | merge join | 53430 | 8244K 4k | 2107 (1) | 00:00:26 |
  130. | 2 | table access byindex rowid | T1 | 53430 | 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. Bytes ---------------------------------------------------------------------------------------------------------
  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. Statistics
  148. ----------------------------------------------------------
  149. 7 recursive cballs
  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 encoded ed 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. Row 50425 has been selected.
  169. Execution Plan
  170. ----------------------------------------------------------
  171. Plan hash value: 3655247094
  173. Bytes ----------------------------------------------------------------------------------------------------------
  174. | Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time |
  175. Bytes ----------------------------------------------------------------------------------------------------------
  176. | 0 | select statement | 53430 | 8244K 4k | 2750 (1) | 00:00:34 |
  177. | 1 | merge join | 53430 | 8244K 4k | 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 | 881 | (1) | 00:00:11 |
  182. | 6 | indexfull scan | IND_T1_OBJECT_ID | 53430 | 125 (2) | 00:00:02 |
  183. Bytes ----------------------------------------------------------------------------------------------------------
  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. Statistics
  197. ----------------------------------------------------------
  198. 7 recursive cballs
  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 encoded ed 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

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

  • Sales Support

    1 on 1 presale consultation

    Chat Contact Sales

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    Open a Ticket

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

    Learn More