oracle优化器 笛卡儿乘积 HASH JOIN

1、在表的连接当中，使用不同版本的oracle其优化器的工作原理也不一样，另外， 在优化器工作过程中，哪张表作为驱动表？可选的驱动路径有哪些？何时将访问 每张表？哪些索引是可用的？哪些是可选的？索引的选择将导致优化器使用或 者限制一个索引，还将改变驱动查询方式，并可能决定使用或限制查询中其它索 引，其中可能出现哪些连接，连接顺序、可选索引，用于排序和建立hash表的 可用内存的不同都将会导致不同的结果。以上知识都会在表的连接中得到体现， 下面详细讨论表的连接。一、分类嵌套循环连接(nested loops)排序合并连接(sort-merge)群集连接(cluster join)笛卡儿连接(cart

2、esian)哈希连接(hash join)索引连接(index join)二、嵌套循环连接(nested loops)嵌套循环连接中，oracle从第一个行源中读取第一行，然后和第二个行源中的 数据进行对比。所有匹配的记录放在结果集中，然后再从第一个行源中读取第二 行。按这种方式直至第一个行源中所有行都经过处理。第一个行源称为外部表， 或者驱动表，第二个行源称为内部表。嵌套循环连接是一种从连接结果中提取第 一批记录的最快速的方法。因为这个时候，在连接完成前的结果集中的记录就可 以访问了，也就是说，可以快速地从结果集中提取第一批记录，而不用等待整个 结果集完全确定下来。一般在驱动行源较小，或者内

3、部表已连接的列有唯一的索引或者高度可选的非唯 一索引时，嵌套循环连接会取得比较好的效果。如果内部表已连接的列上无索引 或者索引不是高度可选，那么连接效率将很低，另外如果驱动行源表非常大，那 么效果也是非常差。SQL select /*+ use_nl(t1 t2)*/t1.object_name,t2.object_name from test2 t2,test1 t1 wheret1.object_name=t2.object_name;已选择1000行。已用时间：00: 00: 00.02Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE

4、(Cost=60010 Card=15024 Byt es=420672)0 NESTED LOOPS (Cost=60010 Card=15024 Bytes=420672)1 TABLE ACCESS (FULL) OF TEST1 (Cost=10 Card=10000 Bytes =200000)1 TABLE ACCESS (FULL) OF TEST2 (Cost=6 Card=2 Bytes=16) 三、排序合并连接(sort-merge)排序合并连接中，oracle分别将第一个行源、第二个行源按它们各自要连接的 列排序，然后将两个已经排序的源表合并，如果找到匹配的数据就放到结果

5、集中。 在缺乏数据的选择性或者可用的索引时，或者两个源表都过于庞大(超过记录数 5%)时，排序合并连接将比嵌套连接更加高效，但排序合并只用于条件为等价连 接时，排序合并连接需要临时的内存块，以用于排序(sort_area_size设置太 小的话)，会导致临时表空间占用更多的内存和磁盘i/o。SQL select /*+ use_merge(t1 t2)*/t1.object_name,t2.object_name from test2 t2,test1 t1 wheret1.object_name=t2.object_name;已选择1000行。已用时间：00: 00: 00.01Execut

6、ion Plan 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=85 Card=15024 Bytes= 420672)0 MERGE JOIN (Cost=85 Card=15024 Bytes=420672)1 SORT (JOIN) (Cost=32 Card=10000 Bytes=80000)2 TABLE ACCESS (FULL) OF TEST2 (Cost=6 Card=10000 Byte s=80000)1 SORT (JOIN) (Cost=53 Card=10000 Bytes=200000)4 TABLE ACCESS (FUL

7、L) OF TEST1 (Cost=10 Card=10000 Byt es=200000)四、群集连接(cluster join)群集连接(cluster join)实际上是嵌套循环连接的一种特例。如果连接的两个 行源表是群集中的表，并且连接是群集键间的等价连接，那么在oracle中就能 使用群集连接，这种情况下，oracle从第一个行源表中读取第一行，并在第二 个行源表中使用cluster索引查找所有匹配的项。群集连接效率极高，因为所有行实际上在同一数据块上，限制就是不可能进行没 有群集的群集连接，所以该连接实际上使用很少。五、笛卡儿连接(cartesian)笛卡尔乘积是一个表的每一行依次

8、与另一个表中的所有行匹配，当两个行源做连 接，但是它们之间没有关联条件时，就会在两个行源中做笛卡儿乘积，在特殊情 况下如在星形连接中我们可以使用笛卡儿乘积，一般尽避免使用，有时关联条件 无效或者没有关联条件下，会造成笛卡儿连接。SQL select * from test1,test4;已用时间：00： 00： 00.00Execution Plan 0 SELECT STATEMENT Optimizer二CHOOSE (Cost=822 Card=820000 Byte s=77900000)0 MERGE JOIN (CARTESIAN) (Cost=822 Card=820000 By

9、tes=7790000 0)1 TABLE ACCESS (FULL) OF TEST4 (Cost=2 Card=82 Bytes=4838)1 BUFFER (SORT) (Cost=820 Card=10000 Bytes=360000)3 TABLE ACCESS (FULL) OF TEST1 (Cost=10 Card=10000 Byt es=360000) 六、哈希连接(hash join)当内存能够提供足够的空间时，哈希连接是oracle优化器通常的选择。在hash join中，oracle访问一张表，通常是较小的表，并在内存中建立一张基于连接 键的哈希表，然后它扫描连接中其

10、他的表(通常较大的表)，并根据哈希表检测 是否有匹配的记录。只有在初始化参数hash_join_enalbed二true，并且为参数 pga_aggregate_target设置了一个足够大的值的时候，oracle才会使用哈希连 接，这点同嵌套循环有点类似一一oracle先建立一张哈希表以利于操作进行。 当用ordered提示时，from字句当中的第一张表将用于建立哈希表。当缺少有 用的索引时，哈希连接比嵌套循环连接更加有效，也可能比排序合并连接更快， 因为在这种情况下，只有一张源表需要排序，哈希连接也可能比嵌套循环连接更 快，因为处理内存中哈希表比检索b-树索引更加迅速。同排序合并连接、群集

11、 连接一样，哈希连接只能用于等价连接。和排序合并一样，哈希连接使用内存资 源，并且当用于排序内存不足时，会增加临时表空间的i/o,这将会使哈希连接 变得更慢，最后只有基于 代价的优化器才可以使用哈希连接。SQL select t1.object_name,t2.object_name from test2 t2,test1 t1 wheret1.object_name=t2.object_name;已选择1000行。已用时间：00： 00： 00.01Execution Plan 0 SELECT STATEMENT Optimizer二CHOOSE (Cost=19 Card=15024 B

12、ytes= 420672)0 HASH JOIN (Cost=19 Card=15024 Bytes=420672)1 TABLE ACCESS (FULL) OF TEST2 (Cost=6 Card=10000 Bytes= 80000)1 TABLE ACCESS (FULL) OF TEST1 (Cost=10 Card=10000 Bytes =200000)七、索引连接(index join)oracle8i起，如果一组已存在的索引包含了查询所需要的所有信息，那么优化 器将在索引中有选择地生成一组哈希表。可通过范围扫描或者快速全局扫描访问 到每一个索引，选择哪种索引取决于where

13、字句条件，这种方法在有大两列而只 需要访问少两几列时非常有效。where字句约束越多执行速度越快，因为此时优 化器在评估执行查询的优化路径时，将把约束条件作为选项看待。必须在合适的列上建立索引，这样可以确保优化器将索引连接作为可选项之一。 相对于快速全表扫描区别在于，快速全表扫描只有一个单一索引满足整个查询， 索引连接可以有多个索引满足整个查询。八、初始化参数db_file_multiblock_read_count定义全表扫描一次从磁盘读取多少数据块 pga_aggregate_target (或sort_area_size)定义用于排序的内存，排序合并 连接需要对所有表源进行排序，为排序分配内存将对排序合并连接的性能产生更 为显著的影响。如果一次完整的排序不能在内存中完成，那么就必须在临时表空 间中分配临时空间。如果该参数设置过小，那么即使最小的排序也需要额外的磁 盘i/o开销，如果设置太大，os可能会耗尽物理内存并不得不求助于交换空间。 hash_join_enabled指示优化器是否考虑使用哈希连接为true|fa