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

最新推荐文章于 2023-03-28 15:51:34 发布

congbu2215

最新推荐文章于 2023-03-28 15:51:34 发布

阅读量230

点赞数

文章标签：数据结构与算法数据库

对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

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

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

| 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_val，j.object_name上有索引)或行源已经在前面的步骤中被排序了。尽管合并两个行源的过程是串行的，但是可以并行访问这两个行源（如并行读入数据，并行排序）。

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

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

Nested Loops（NL）

嵌套循环（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

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

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

| 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 Join（HJ）

哈希连接（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

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

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

| 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 |

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

HJ在oracle 7.3以后引入，从理论上来说比NL与SMJ更高效，并且只能在CBO优化器中使用。

HJ的内部连接过程

（1）使用较小的行源构建hash table与bitmap。

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

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

与NL相关的参数：

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

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

总结

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

·SMJ

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

（2）如果在关联的列上都有索引，效果更好。

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

·NL

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

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

·HJ

（1）一般来说，HJ的效率要好于SMJ和NL，但是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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21129591/viewspace-1052463/

congbu2215

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle中表之间的连接方式及Hint清单

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

扫一扫