oracle 结果的排序与执行计划有关

最新推荐文章于 2024-10-28 14:12:21 发布

原创最新推荐文章于 2024-10-28 14:12:21 发布 · 1.8k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #join #access #user #table #nested

Oracle PL/SQL编程专栏收录该内容

103 篇文章

订阅专栏

本文通过一个具体的例子展示了Oracle数据库中三种不同的表连接方法：HASH JOIN、MERGE JOIN 和 NESTED LOOP的不同执行计划及结果顺序。并通过对比指出，连接方法的选择会影响到数据返回的顺序，并强调在需要特定顺序时应使用ORDER BY子句。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL> --表中原始数据 SQL> select * from user_sd1104; ID PASS NAME ---- ---- ------- 1005 1234 chengzh 1004 1234 hahah 1003 1222 fdfdfdf 1002 1234 v1v2v2 1001 2345 liyi SQL> set autot on exp SQL> --ORACLE根据动态采样执行计划使用HASH JOIN SQL> SELECT u1.id, u1.password, u2.id, u2.password 2 FROM user_sd1104 u1 3 LEFT OUTER JOIN user_sd1104 u2 4 ON u1.password = u2.password; ID PASS ID PASS ---- ---- ---- ---- 1002 1234 1005 1234 1004 1234 1005 1234 1005 1234 1005 1234 1002 1234 1004 1234 1004 1234 1004 1234 1005 1234 1004 1234 1003 1222 1003 1222 1002 1234 1002 1234 1004 1234 1002 1234 1005 1234 1002 1234 1001 2345 1001 2345 已选择11行。执行计划 ---------------------------------------------------------- Plan hash value: 2237943440 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 192 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN OUTER | | 8 | 192 | 7 (15)| 00:00:01 | | 2 | TABLE ACCESS FULL| USER_SD1104 | 5 | 60 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| USER_SD1104 | 5 | 60 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("U1"."PASSWORD"="U2"."PASSWORD"(+)) Note ----- - dynamic sampling used for this statement SQL> --强制执行计划使用NEST LOOP SQL> SELECT/*+use_nl(u1,u2)*/ u1.id, u1.password, u2.id, u2.password 2 FROM user_sd1104 u1 3 LEFT OUTER JOIN user_sd1104 u2 4 ON u1.password = u2.password; ID PASS ID PASS ---- ---- ---- ---- 1005 1234 1005 1234 1005 1234 1004 1234 1005 1234 1002 1234 1004 1234 1005 1234 1004 1234 1004 1234 1004 1234 1002 1234 1003 1222 1003 1222 1002 1234 1005 1234 1002 1234 1004 1234 1002 1234 1002 1234 1001 2345 1001 2345 已选择11行。执行计划 ---------------------------------------------------------- Plan hash value: 769104428 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 192 | 9 (0)| 00:00:01 | | 1 | NESTED LOOPS OUTER| | 8 | 192 | 9 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL| USER_SD1104 | 5 | 60 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| USER_SD1104 | 2 | 24 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("U1"."PASSWORD"="U2"."PASSWORD"(+)) Note ----- - dynamic sampling used for this statement SQL> --强制执行计划使用merg join SQL> SELECT/*+use_merge(u1,u2)*/ u1.id, u1.password, u2.id, u2.password 2 FROM user_sd1104 u1 3 LEFT OUTER JOIN user_sd1104 u2 4 ON u1.password = u2.password; ID PASS ID PASS ---- ---- ---- ---- 1003 1222 1003 1222 1004 1234 1004 1234 1004 1234 1002 1234 1004 1234 1005 1234 1002 1234 1004 1234 1002 1234 1002 1234 1002 1234 1005 1234 1005 1234 1004 1234 1005 1234 1002 1234 1005 1234 1005 1234 1001 2345 1001 2345 已选择11行。执行计划 ---------------------------------------------------------- Plan hash value: 1751357394 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 8 | 192 | 8 (25)| 00:00:01 | | 1 | MERGE JOIN OUTER | | 8 | 192 | 8 (25)| 00:00:01 | | 2 | SORT JOIN | | 5 | 60 | 4 (25)| 00:00:01 | | 3 | TABLE ACCESS FULL| USER_SD1104 | 5 | 60 | 3 (0)| 00:00:01 | |* 4 | SORT JOIN | | 5 | 60 | 4 (25)| 00:00:01 | | 5 | TABLE ACCESS FULL| USER_SD1104 | 5 | 60 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("U1"."PASSWORD"="U2"."PASSWORD"(+)) filter("U1"."PASSWORD"="U2"."PASSWORD"(+)) Note ----- - dynamic sampling used for this statement SQL> /* 总结：从表面上看，NEST LOOP执行计划(第二种)最可能是意料中的顺序。因此两表连接，结果的最终顺序是与执行计划有关的。并且在不强制使用采用某种连接方式之前，你无法确定oracle采用什么执行计划。所以数据的顺序可能是无法确定的。因此，如果你需要按某种顺序输出最好增加order by来排序。从另外一个方面来说，是否使用HINT来强制使用某种连接方式来确定顺序就好呢？回答是否定的，因为这样可能不是SQL最优的执行方式。不要捡了芝麻丢掉西瓜！ */

原帖：http://topic.youkuaiyun.com/u/20110609/23/6aa6389d-699d-42ff-be97-bee1d82836d8.html?98354

HASH JOIN ,MERGE JOIN ,NESTED LOOP:

[1].http://blogold.chinaunix.net/u1/46451/showart_415529.html

Oracle中的Hash Join祥解:

[1].http://www.hellodba.com/reader.php?ID=144&lang=cn

[2].http://www.remote-dba.net/pl_sql/t_oracle_hash_joins.htm

Using hints with join methods:

[1].http://guyharrison.squarespace.com/blog/2009/8/30/using-hints-with-join-methods.html