查看: 7046|回复: 17

[SQL] 【大话IT】三种表连接hj,nl,smj,在什么情况下使用的效率会更高

[复制链接]
论坛徽章:
2
优秀写手
日期:2015-02-13 06:00:15秀才
日期:2017-02-22 15:18:00
发表于 2015-2-3 17:07 | 显示全部楼层 |阅读模式
如果 T1 只有10条记录,索引RID,T2 有100w记录 ,索引RID
怎么样的表连接才是最佳
select t1.* from t1 ,t2 where t1.rid=t2.rid?
论坛徽章:
17
蛋疼蛋
日期:2012-04-27 23:09:45罗罗诺亚·索隆
日期:2016-11-04 21:04:09懒羊羊
日期:2015-06-23 09:05:532015年新春福章
日期:2015-04-01 14:21:232015年新春福章
日期:2015-03-06 11:58:39慢羊羊
日期:2015-03-04 14:53:33青年奥林匹克运动会-击剑
日期:2014-08-29 15:44:44马上加薪
日期:2014-02-18 16:48:492014年新春福章
日期:2014-02-18 16:48:49一汽
日期:2013-12-24 18:12:09
发表于 2015-2-3 17:11 | 显示全部楼层
用t1做驱动表去t2表做嵌套循环!

使用道具 举报

回复
论坛徽章:
127
ITPUB元老
日期:2007-07-04 17:27:50会员2007贡献徽章
日期:2007-09-26 18:42:10现任管理团队成员
日期:2011-05-07 01:45:08优秀写手
日期:2015-01-09 06:00:14版主7段
日期:2015-07-16 02:10:00
发表于 2015-2-3 17:12 | 显示全部楼层
这种情况如果是等值连接的话,当然是nested loop, 至于smj只是写成subquery会用到

使用道具 举报

回复
论坛徽章:
2
优秀写手
日期:2015-02-13 06:00:15秀才
日期:2017-02-22 15:18:00
 楼主| 发表于 2015-2-3 17:24 | 显示全部楼层
zxy877298415 发表于 2015-2-3 17:11
用t1做驱动表去t2表做嵌套循环!

能具体解释一下吗?

使用道具 举报

回复
论坛徽章:
2
优秀写手
日期:2015-02-13 06:00:15秀才
日期:2017-02-22 15:18:00
 楼主| 发表于 2015-2-3 17:25 | 显示全部楼层
为什么是 以T1做驱动表 以及为什么是做嵌套循环

使用道具 举报

回复
论坛徽章:
737
季节之章:春
日期:2015-07-31 17:16:29ITPUB季度 技术新星
日期:2014-07-17 14:37:00季节之章:秋
日期:2015-07-31 17:16:14季节之章:夏
日期:2015-07-31 17:16:29股神
日期:2014-10-15 09:23:31衰神
日期:2014-10-20 22:47:12季节之章:冬
日期:2015-07-31 17:16:14红钻
日期:2014-12-16 17:51:41洛杉矶湖人
日期:2016-09-23 08:18:15布鲁克林篮网
日期:2016-09-23 08:17:18
发表于 2015-2-3 18:24 | 显示全部楼层
lz是刚入门oracle还是优化水平了?

如果是前者,没必要研究,先搞基础,先实现功能再说!
如果是后者,我只能说:请找兔子

使用道具 举报

回复
论坛徽章:
2
优秀写手
日期:2015-02-13 06:00:15秀才
日期:2017-02-22 15:18:00
 楼主| 发表于 2015-2-3 20:45 | 显示全部楼层
本帖最后由 yleizzz 于 2015-2-3 20:47 编辑
oracle_cj 发表于 2015-2-3 18:24
lz是刚入门oracle还是优化水平了?

如果是前者,没必要研究,先搞基础,先实现功能再说!


在学习如何优化sql,

使用道具 举报

回复
论坛徽章:
401
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2015-2-3 22:08 | 显示全部楼层
yleizzz 发表于 2015-2-3 20:45
在学习如何优化sql,

正确收集统计信息,然后让cbo决定

使用道具 举报

回复
认证徽章
论坛徽章:
40
2014年新春福章
日期:2014-02-18 16:43:09喜羊羊
日期:2015-05-18 16:24:25慢羊羊
日期:2015-06-12 13:08:22暖羊羊
日期:2015-07-02 16:06:20暖羊羊
日期:2015-07-06 16:28:55狮子座
日期:2015-07-29 17:14:43摩羯座
日期:2015-09-02 13:58:47白羊座
日期:2015-09-08 10:39:06天枰座
日期:2015-09-17 21:41:53摩羯座
日期:2015-10-29 21:07:02
发表于 2015-2-3 22:30 | 显示全部楼层
做个实验就清楚了。SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE        10.2.0.5.0        Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> set timing on
SQL> set linesize 400
SQL> set autotrace traceonly;
--创建测试表及索引
SQL> create table t1 as select rownum rid,'T1_'||rownum name from dual connect by rownum<=10;

Table created.

Elapsed: 00:00:00.35
SQL> create index ind_t1 on t1(rid);

Index created.

Elapsed: 00:00:00.20
SQL> create table t2 as select rownum rid,'T2_'||rownum name from dual connect by rownum<=1000000;

Table created.

Elapsed: 00:00:03.37
SQL> create index ind_t2 on t2(rid);

Index created.
--收集统计信息,以保证ORACLE选择正确的执行计划
SQL> exec dbms_stats.gather_table_stats(ownname=>'DEMO',tabname=>'T1',cascade=>TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.89
SQL> exec dbms_stats.gather_table_stats(ownname=>'DEMO',tabname=>'T2',cascade=>TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.75

--执行语句,不带任何提示。
SQL> select t1.* from t1,t2 where t1.rid=t2.rid;

10 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3054380619

-----------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |          1 |         13 |         23   (0)| 00:00:01 |
|   1 |  NESTED LOOPS           |            |          1 |         13 |         23   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1     |         10 |         80 |          3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | IND_T2 |          1 |          5 |          2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T1"."RID"="T2"."RID")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         27  consistent gets
          0  physical reads
          0  redo size
        741  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed
--可见,ORACLE认为走NL是最佳的。其COST值为23.
--加HINT,强制走HASH连接
SQL> select /*+ use_hash(t1,t2) */ t1.* from t1,t2 where t1.rid=t2.rid;

10 rows selected.

Elapsed: 00:00:00.09

Execution Plan
----------------------------------------------------------
Plan hash value: 788290213

--------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |    13 |   499         (2)| 00:00:06 |
|*  1 |  HASH JOIN              |        |     1 |    13 |   499         (2)| 00:00:06 |
|   2 |   TABLE ACCESS FULL   | T1     |    10 |    80 |     3         (0)| 00:00:01 |
|   3 |   INDEX FAST FULL SCAN| IND_T2 |   993K|  4852K|   493         (1)| 00:00:06 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."RID"="T2"."RID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2245  consistent gets
          0  physical reads
          0  redo size
        741  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed
--注意其COST是499,远高于NL的23.
--加HINT,强制走SMJ。
SQL> select /*+ use_merge(t1,t2) */ t1.* from t1,t2 where t1.rid=t2.rid;

10 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 4161739255

------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |           1 |          13 |        2238   (1)| 00:00:27 |
|   1 |  MERGE JOIN            |             |           1 |          13 |        2238   (1)| 00:00:27 |
|   2 |   INDEX FULL SCAN   | IND_T2 |         993K|        4852K|        2234   (1)| 00:00:27 |
|*  3 |   SORT JOIN            |             |          10 |          80 |           4  (25)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T1     |          10 |          80 |           3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T1"."RID"="T2"."RID")
       filter("T1"."RID"="T2"."RID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        741  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed
--COST是2238,是三种连接方式中最高的。


--来个极端的,假设T2表中的RID的重复率很高,只有10个唯一值(0到9)。
SQL> update t2 set rid=mod(rid,10);

1000000 rows updated.

Elapsed: 00:02:00.50

Execution Plan
----------------------------------------------------------
Plan hash value: 2722410703

---------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time          |
---------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |          |   993K|  4852K|   620   (1)| 00:00:08 |
|   1 |  UPDATE            | T2   |          |          |               |          |
|   2 |   TABLE ACCESS FULL| T2   |   993K|  4852K|   620   (1)| 00:00:08 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
       4111  recursive calls
    7173357  db block gets
      58338  consistent gets
       2234  physical reads
  748306104  redo size
        829  bytes sent via SQL*Net to client
        722  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL> commit;

Commit complete.

Elapsed: 00:00:00.02
--重新收集统计信息,以确保此后的执行计划正确。
SQL> exec dbms_stats.gather_table_stats(ownname=>'DEMO',tabname=>'T2',no_invalidate=>TRUE,cascade=>TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.17
--不带任何提示,看看ORACLE认为何种方式是最优的。
SQL> select t1.* from t1,t2 where t1.rid=t2.rid;

900000 rows selected.

Elapsed: 00:00:05.17

Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time          |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |  1004K|    10M|   626   (2)| 00:00:08 |
|*  1 |  HASH JOIN           |          |  1004K|    10M|   626   (2)| 00:00:08 |
|   2 |   TABLE ACCESS FULL| T1   |    10 |    80 |        3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |  1004K|  2942K|   620   (1)| 00:00:08 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."RID"="T2"."RID")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      62926  consistent gets
          0  physical reads
          0  redo size
   15480507  bytes sent via SQL*Net to client
     660481  bytes received via SQL*Net from client
      60001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     900000  rows processed
--是HASH连接,而且不用索引。COST是626.
--加HINT,强制走NL连接
SQL> select /*+ use_nl(t1,t2) */ t1.* from t1,t2 where t1.rid=t2.rid;

900000 rows selected.

Elapsed: 00:00:05.24

Execution Plan
----------------------------------------------------------
Plan hash value: 3054380619

-----------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |  1004K|         10M|  3429   (1)| 00:00:42 |
|   1 |  NESTED LOOPS           |            |  1004K|         10M|  3429   (1)| 00:00:42 |
|   2 |   TABLE ACCESS FULL| T1     |         10 |         80 |          3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | IND_T2 |        100K|        294K|        343   (1)| 00:00:05 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T1"."RID"="T2"."RID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      62926  consistent gets
          0  physical reads
          0  redo size
   15480507  bytes sent via SQL*Net to client
     660481  bytes received via SQL*Net from client
      60001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     900000  rows processed
--NL的COST是3429
--加HINT,强制走SMJ连接
SQL> select /*+ use_merge(t1,t2) */ t1.* from t1,t2 where t1.rid=t2.rid;

900000 rows selected.

Elapsed: 00:00:05.87

Execution Plan
----------------------------------------------------------
Plan hash value: 178869083

-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |  1004K|    10M|       |  3304        (1)| 00:00:40 |
|   1 |  MERGE JOIN                     |              |  1004K|    10M|       |  3304        (1)| 00:00:40 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |    10 |    80 |       |     2        (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN             | IND_T1 |    10 |       |       |     1        (0)| 00:00:01 |
|*  4 |   SORT JOIN                     |              |  1004K|  2942K|    23M|  3302        (1)| 00:00:40 |
|   5 |    TABLE ACCESS FULL             | T2     |  1004K|  2942K|       |   620        (1)| 00:00:08 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."RID"="T2"."RID")
       filter("T1"."RID"="T2"."RID")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      65433  consistent gets
          0  physical reads
          0  redo size
   15480507  bytes sent via SQL*Net to client
     660481  bytes received via SQL*Net from client
      60001  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     900000  rows processed
--其COST是3304。
--本次测试,NL连接是COST值最高的。





使用道具 举报

回复
论坛徽章:
2
优秀写手
日期:2015-02-13 06:00:15秀才
日期:2017-02-22 15:18:00
 楼主| 发表于 2015-2-4 09:52 | 显示全部楼层
bfc99 发表于 2015-2-3 22:30
做个实验就清楚了。SQL> select * from v$version;

BANNER

解释的很清楚,

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表