ITPub博客

首页 > 数据库 > Oracle > Oracle中表之间的连接方式及Hint清单

Oracle中表之间的连接方式及Hint清单

原创 Oracle 作者:aspen1982 时间:2011-07-14 17:03:13 0 删除 编辑

ORACLE中表之间的连接方式的摘要,备忘。摘录自徐玉金的《SQL性能的调整》,重做了实验部分并根据个人理解有所增删。顺便列出所有Hint清单,就不单独写一篇了。

关键字:sort_merge_join SMJ hash_join nested_loops NL hint

[@more@]

实验环境:

·OS

Microsoft Winsows XP Professional 版本 2002 Service Pack 3

·DB

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

需要使用的测试表脚本如下:

create table JOE_MAIN as select object_id,object_name,last_ddl_time from dba_objects where rownum < 100;

alter table JOE_MAIN add constraint UN_1 unique (OBJECT_ID);

create index IDX_JOE_MAIN_12 on JOE_MAIN (OBJECT_ID, OBJECT_NAME);

create table big_table as select object_id serialno,object_name col_val,last_ddl_time CT from dba_objects;

alter table big_table add constraint PK_BIG_TABLE primary key (serialno);

create index IDX_BIG_12 on big_table (serialno, col_val);

Sort Merge Join(SMJ)

排序-和并联接(SMJ),例子如下:

SQL> select /*+ use_merge(b,j)*/

2 b.col_val, j.object_name

3 from big_table b, joe_main j

4 where b.col_val = j.object_name

5 order by b.col_val, j.object_name;

已选择1025行。

执行计划

----------------------------------------------------------

Plan hash value: 1861371078

---------------------------------------------------------------------------------------------------

| Id | Operation|Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |

---------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT| | 1676 | 67040 | | 445 (3)| 00:00:06 |

| 1 | SORT ORDER BY | | 1676 | 67040 | | 445 (3)| 00:00:06 |

| 2 | MERGE JOIN | | 1676 | 67040 | | 444 (3)| 00:00:06 |

| 3 | SORT JOIN| | 996 | 14940 | | 4 (25)| 00:00:01 |

| 4 | INDEX FAST FULL SCAN|IDX_JOE_MAIN_12|996|14940| |3 (0)| 00:00:01 |

|* 5 | SORT JOIN| | 50582 | 1234K| 3192K| 440 (3)| 00:00:06 |

| 6 | INDEX FAST FULL SCAN|IDX_BIG_12|50582|1234K| | 65 (2)| 00:00:01 |

---------------------------------------------------------------------------------------------------

SMJ的内部连接步骤

1)生成第1个行源(row source)需要的数据,然后对这些数据按照连接操作关联列(在例子中是b.col_val)进行排序。

2)生成第2个行源需要的数据,然后对这些数据按照与(1)中对应的连接操作关联列(在例子中是j.object_name)进行排序。

3)将两个已排序的行源放在一起执行合并操作,即将2个行源按照连接条件连接起来。

如果行源在连接关联列上已经排序好了,则该连接操作就不需要再进行排序(sort)操作。这样可以大大提高SMJ操作的连接速度,因为排序是个极其费资源的操作,特别是对于较大的表。 预先排序的行源包括已经被索引的列(b.col_valj.object_name上有索引)或行源已经在前面的步骤中被排序了。尽管合并两个行源的过程是串行的,但是可以并行访问这两个行源(如并行读入数据,并行排序)。

排序是一个费时、费资源的操作,特别对于大表。基于这个原因,SMJ经常不是一个特别有效的连接方式,但是如果2个行源都已经预先排序,则这种连接方法的效率也是蛮高的。

在实验中变换了多种查询方式,也没有使CBO自动选择SMJ的连接方式,最后只好使用hint强制使用。我想这也从一个侧面说明了SMJ经常不是一个特别有效的连接方式。因为Oracle中常见的排序数据结构就是索引,而谓词条件两边都是表的索引列(复合索引还要求是前导列)并不是一个很常见的情况,而且FFS这种访问路径虽然是通过索引但是不对得出的结果作排序,所以要想匹配SMJ使用的最佳场景还真是不太容易。

Nested LoopsNL

嵌套循环(NL),例子如下:

SQL> select j.object_id, b.serialno

2 from joe_main j, big_table b

3 where j.object_id = b.serialno;

已选择996行。

执行计划

----------------------------------------------------------

Plan hash value: 2175249783

--------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1010 | 9090 | 2 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 1010 | 9090 | 2 (0)| 00:00:01 |

| 2 | INDEX FAST FULL SCAN| UN_1 | 996 | 3984 | 2 (0)| 00:00:01 |

|* 3 | INDEX UNIQUE SCAN|PK_BIG_TABLE| 1 | 5 | 0 (0)| 00:00:01 |

--------------------------------------------------------------------------------------

这个连接方法有驱动表(外部表)的概念。其实,该连接过程就是一个2层嵌套循环。外层循环的次数越少效率越高,所以应该将较小的行源作为驱动表,用于外层循环。

但是这只是指导原则,因为遵循这个原则并不能总保证使语句产生的IO次数最少。这个很好理解:外层循环次数虽然少但是如果每次循环要做的事情很多,这样总的开销不一定比每次循环做很少的事,多循环几次的总开销小。所以有时不遵守这个指导原则,反而会获得更好的效率。一切以结果导向,快的就是好。

使用NL时,如果使用这种方法,决定使用哪个表作为驱动表很重要。有时如果驱动表选择不正确,将会导致语句的性能很差、很差。驱动表选定后,我们重点要做的是提高内循环的效率。

NL的内部连接过程是用行源1中的每一行,去匹配行源2中的所有行。所以高效的NL的特点有:

1)行源1(驱动表)尽可能小。

2)对行源2的访问效率高(一般通过索引实现)。

这也是理论指导原则,目的是使整个连接操作产生最少的物理I/O次数。通常情况如果遵守这个原则,一般也会使总的物理I/O数最少。但是如果不遵从这个指导原则,反而能用更少的物理I/O实现连接操作,那尽管违反指导原则吧!因为最少的物理I/O次数才是我们的追求。

在上面的连接过程中,我们称行源1为驱动表或外部表,行源2为被探测表或内部表。

NL中,Oracle读取行源1中的每一行,然后在行源2中检查是否有匹配的行,所有被匹配的行都被放到结果集中。然后处理行源1中的下一行。这个过程一直继续,直到行源1中的所有行都被处理。这是从连接操作中可以得到第一个匹配行的最快的方法之一,这种类型的连接可以用在需要快速响应的语句中,以响应速度为主要目标。

如果驱动表比较小,并且在探测表上有高效的索引可以使用(唯一索引最佳,高选择性非唯一索引也可)时,使用这种方法可以得到较好的效率。NL的一个独特优点是:可以先返回已经连接的行,而不必等待所有的连接操作处理完才返回数据,这可以实现快速的响应。

如果不使用并行操作,最好的驱动表是那些应用了where 限制条件后,可以返回较少行数据的的表,所以大表也可能称为驱动表,只要经过谓词筛选后的行源小。对于并行查询,我们经常选择大表作为驱动表,因为大表可以充分利用并行功能。当然,有时对查询使用并行操作并不一定会比查询不使用并行操作效率高,因为最后可能每个表只有很少的行符合限制条件,而且还要看你的硬件配置是否可以支持并行(如是否有多个CPU,多个硬盘控制器),所以要具体问题具体对待。

个人觉得NL是最为灵活的一种连接方式。

Hash JoinHJ

哈希连接(HJ),例子如下:

SQL> select j.object_name, b.col_val

2 from joe_main j, big_table b

3 where j.object_name=b.col_val;

已选择1025行。

执行计划

----------------------------------------------------------

Plan hash value: 1192954078

-----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT| | 1676 | 67040 | 70 (5)| 00:00:01 |

|* 1 | HASH JOIN | | 1676 | 67040 | 70 (5)| 00:00:01 |

| 2 | INDEX FAST FULL SCAN| IDX_JOE_MAIN_12|996|14940| 3 (0)| 00:00:01 |

| 3 | INDEX FAST FULL SCAN| IDX_BIG_12|50582 |1234K | 65 (2)| 00:00:01 |

-----------------------------------------------------------------------------------------

HJoracle 7.3以后引入,从理论上来说比NLSMJ更高效,并且只能在CBO优化器中使用。

HJ的内部连接过程

1)使用较小的行源构建hash tablebitmap

2)另一个(较大的)行源用来被hash,并与(1)中行源生成的hash table进行匹配,以便进行进一步的连接。

Bitmap被用来作为一种比较快的查找方法,来检查在hash table中是否有匹配的行。特别的,当hash table比较大而不能全部容纳在内存中时,这种查找方法更为有用。这种连接方法也有NL连接中所谓的驱动表的概念,被构建为hash tablebitmap的表为驱动表,当被构建的hash tablebitmap能被容纳在内存中时,这种连接方式的效率极高。

NL相关的参数:

1HASH_JOIN_ENABLED。该参数为TRUE时,可以使用HJ10g中已经没有这个参数了,HJ的使用一直是打开的。

2hash_area_sizeHJ操作会在该参数指定大小的内存中运行,过小的参数会使HJ的效率变差。

总结

各种连接方式的特点和适用场景。

·SMJ

1)对于非等值连接,这种连接方式的效率是比较高的。

2)如果在关联的列上都有索引,效果更好。

3)对于将2个较大的行源做连接,该连接方法可能比NL连接要好一些。但是如果sort merge返回的行源过大,又会导致使用过多的rowid在表中查询数据。,因为过多的I/O,数据库性能下降。

·NL

1)如果驱动表比较小,并且在探测表上有唯一索引或高选择性非唯一索引时,使用这种方法可以得到较好的效率。

2NL的一个独特优点是:可以先返回已经连接的行,而不必等待所有的连接操作处理完才返回数据,这可以实现快速的响应。

·HJ

1)一般来说,HJ的效率要好于SMJNL,但是HJ只能用在CBO优化器中,并且需要设置合适的hash_area_size参数,才能取得较好的性能。

2)在2个较大的行源之间连接时会取得相对较好的效率,在一个行源较小时则能取得更好的效率。

3)只能用于等值连接中

HINT清单

指示优化器的方法与目标

ALL_ROWS /*+ ALL_ROWS*/

基于代价的优化器,以吞吐量为目标

FIRST_ROWS(n) /*+ FIRST_ROWS(n)*/

基于代价的优化器,以响应时间为目标

CHOOSE /*+ CHOOSE*/

根据是否有统计信息,选择不同的优化器

RULE /*+ FULL ( table ) */

使用基于规则的优化器

指示访问路径

FULL /*+ FULL ( table ) */

指定该表使用全表扫描

ROWID /*+ ROWID ( table ) */

指定对该表的访问路径使用rowid,该hint用的较少

INDEX /*+ INDEX ( table [index]) */

使用该表上指定的索引对表进行索引扫描

INDEX_FFS /*+ INDEX_FFS ( table [index]) */

使用快速全表扫描

NO_INDEX /*+ NO_INDEX ( table [index]) */

不使用该表上指定的索引进行访问,但是仍然可以使用其它的索引进行扫描

指示连接顺序

ORDERED /*+ ORDERED */

from 字句中表的顺序从左到右的连接

STAR /*+ STAR */

指示优化器使用星型查询

指示连接类型

USE_NL /*+ USE_NL ( table [,table, ...] ) */

使用嵌套连接

USE_MERGE /*+ USE_MERGE ( table [,table, ...]) */

使用排序-合并连接

USE_HASH /*+ USE_HASH ( table [,table, ...]) */

使用HASH连接

PARALLEL /*+ PARALLEL( table num) */

使用并行查询

注意:使用hint时,如果表有别名(alias),则要使用别名,而不是表名。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21129591/viewspace-1052463/,如需转载,请注明出处,否则将追究法律责任。

下一篇: DB意外宕机一则
请登录后发表评论 登录
全部评论

注册时间:2011-07-06

  • 博文量
    24
  • 访问量
    60522