oracle表连接⽅式:nestedloop嵌套循环和HashJoin的⽐较⼀、创建两张实验⽤表:hant和wireless_site.bb
SQL> select count(*) from hant;
COUNT(*)
----------
14005
SQL>
SQL> select count(*) from wireless_site.clickthroughrate;
COUNT(*)
----------
2384026
SQL> create table wireless_site.bb as select * from wireless_site.clickthroughrate;
Table created.
SQL>
SQL> select count(*) from wireless_site.bb;
COUNT(*)
----------
2384026
SQL>
⼆、执⾏SQL语句并查看sql语句的真实执⾏计划:
SQL> alter session set statistics_level=all;
Session altered.
SQL>
2.1、⾸先查看Hash Join的执⾏计划:
执⾏SQL语句:select  * from hant,wireless_site.bb dtype = hantid hantid like '%3210%';
等待SQL语句执⾏完毕,然后再使⽤这条SQL语句:select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));查看真实执⾏计划:
由以上执⾏计划可以看出:
1、HASH JOIN的驱动表为BB,扫描⽅式为全表扫描,执⾏了1次,实际返回了1658⾏
2、HASH JOIN的被驱动表为MERCHANT,扫描⽅式为全表扫描,执⾏了1次,实际返回了12⾏
sql语句优化方式
3、需要注意的是:HASH JOIN的驱动表被被驱动表都会被扫描⼀次,⽽nested loop则是驱动表扫描⼀次,被驱动表被扫描N次(具体N 的值是根据驱动表返回的⾏数来决定的。)
2.2、接着使⽤HINT的⽅式让优化器强制⾛nested loop(使⽤hant作为驱动表)
执⾏SQL语句:select /*+ leading(merchant) use_nl(bb) */ * from hant,wireless_site.bb dtype = hantid hantid like '%3210%';
等待SQL语句执⾏完毕,然后再使⽤这条SQL语句:select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));查
看真实执⾏计划:
此时,hant作为驱动表,加上过滤条件filter后,实际返回⾏数为:
由以上执⾏计划可以看出:
1、嵌套循环的驱动表为merchant,采⽤的扫描⽅式为全表扫描,执⾏了1次,返回了12⾏
2、嵌套循环的被驱动表为bb,采⽤的扫描⽅式是全表扫描,执⾏了12次,实际返回了1658⾏
2.3、然后再使⽤HINT的⽅式让优化器强制⾛nested loop(使⽤wireless_site.bb作为驱动表)
执⾏SQL语句:select /*+ leading(bb) use_nl(merchant) */ * from hant,wireless_site.bb dtype = hantid hantid like '%3210%';
等待SQL语句执⾏完毕,然后再使⽤这条SQL语句:select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));查看真实执⾏计划:
此时,wireless_site.bb作为驱动表,加上过滤条件filter后,实际返回⾏数为:
由以上执⾏计划可以看出:
1、表BB作为驱动表,扫描⽅式是全表扫描,实际返回⾏数为1658
2、表merchant作为被驱动表,采⽤的扫描⽅式为索引唯⼀扫描,执⾏了1658次,返回了1658⾏
3、被驱动表merchant索引扫描完成后,通过rowid回表读数执⾏了1658次,实际返回了1658⾏
2.4、接下来在wireless_site.bb表中的recordtype列上创建索引
SQL> create index wireless_site.bb_idx on wireless_site.bb(recordtype);
Index created.
SQL>
接着使⽤HINT的⽅式让优化器强制⾛nested loop(使⽤hant作为驱动表):
执⾏SQL语句:select /*+ leading(merchant) use_nl(bb) */ * from hant,wireless_site.bb dtype
= hantid hantid like '%3210%';
等待SQL语句执⾏完毕,然后再使⽤这条SQL语句:select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));查看真实执⾏计划:
由执⾏计划可以看出:
1、merchant作为驱动表,采⽤的是全表扫描的⽅式,实际返回⾏数为12
2、被驱动表BB的索引采⽤的是Index Range Scan的⽅式,实际返回⾏数为1658,共执⾏了12次
3、被驱动表BB索引被访问了12次,每次扫描完索引后⼜根据rowid回表读数(TABLE ACCESS BY INDEX ROWID),这样BB表就被访问了1658次
对⽐之前没有在wireless_site.bb表中的recordtype列上创建索引时的执⾏计划可以看出,创建索引后SQL花费不到1s的时间就可以出结果;没有创建索引时,需要花费1分32秒左右。
2.5、创建索引后,使⽤HINT的⽅式让优化器强制⾛nested loop(使⽤wireless_site.bb作为驱动表):
执⾏SQL语句:select /*+ leading(bb) use_nl(merchant) */ * from hant,wireless_site.bb dtype = hantid hantid like '%3210%';
等待SQL语句执⾏完毕,然后再使⽤这条SQL语句:select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));查看真实执⾏计划:

版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。