oracle優(yōu)化器 笛卡兒乘積 HASH JOIN

1、在表的連接當中，使用不同版本的oracle其優(yōu)化器的工作原理也不一樣，另外， 在優(yōu)化器工作過程中，哪張表作為驅(qū)動表？可選的驅(qū)動路徑有哪些？何時將訪問 每張表？哪些索引是可用的？哪些是可選的？索引的選擇將導致優(yōu)化器使用或 者限制一個索引，還將改變驅(qū)動查詢方式，并可能決定使用或限制查詢中其它索 引，其中可能出現(xiàn)哪些連接，連接順序、可選索引，用于排序和建立hash表的 可用內(nèi)存的不同都將會導致不同的結(jié)果。以上知識都會在表的連接中得到體現(xiàn)， 下面詳細討論表的連接。一、分類嵌套循環(huán)連接(nested loops)排序合并連接(sort-merge)群集連接(cluster join)笛卡兒連接(cart

2、esian)哈希連接(hash join)索引連接(index join)二、嵌套循環(huán)連接(nested loops)嵌套循環(huán)連接中，oracle從第一個行源中讀取第一行，然后和第二個行源中的 數(shù)據(jù)進行對比。所有匹配的記錄放在結(jié)果集中，然后再從第一個行源中讀取第二 行。按這種方式直至第一個行源中所有行都經(jīng)過處理。第一個行源稱為外部表， 或者驅(qū)動表，第二個行源稱為內(nèi)部表。嵌套循環(huán)連接是一種從連接結(jié)果中提取第 一批記錄的最快速的方法。因為這個時候，在連接完成前的結(jié)果集中的記錄就可 以訪問了，也就是說，可以快速地從結(jié)果集中提取第一批記錄，而不用等待整個 結(jié)果集完全確定下來。一般在驅(qū)動行源較小，或者內(nèi)

3、部表已連接的列有唯一的索引或者高度可選的非唯 一索引時，嵌套循環(huán)連接會取得比較好的效果。如果內(nèi)部表已連接的列上無索引 或者索引不是高度可選，那么連接效率將很低，另外如果驅(qū)動行源表非常大，那 么效果也是非常差。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分別將第一個行源、第二個行源按它們各自要連接的 列排序，然后將兩個已經(jīng)排序的源表合并，如果找到匹配的數(shù)據(jù)就放到結(jié)果

5、集中。 在缺乏數(shù)據(jù)的選擇性或者可用的索引時，或者兩個源表都過于龐大(超過記錄數(shù) 5%)時，排序合并連接將比嵌套連接更加高效，但排序合并只用于條件為等價連 接時，排序合并連接需要臨時的內(nèi)存塊，以用于排序(sort_area_size設(shè)置太 小的話)，會導致臨時表空間占用更多的內(nèi)存和磁盤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)實際上是嵌套循環(huán)連接的一種特例。如果連接的兩個 行源表是群集中的表，并且連接是群集鍵間的等價連接，那么在oracle中就能 使用群集連接，這種情況下，oracle從第一個行源表中讀取第一行，并在第二 個行源表中使用cluster索引查找所有匹配的項。群集連接效率極高，因為所有行實際上在同一數(shù)據(jù)塊上，限制就是不可能進行沒 有群集的群集連接，所以該連接實際上使用很少。五、笛卡兒連接(cartesian)笛卡爾乘積是一個表的每一行依次

8、與另一個表中的所有行匹配，當兩個行源做連 接，但是它們之間沒有關(guān)聯(lián)條件時，就會在兩個行源中做笛卡兒乘積，在特殊情 況下如在星形連接中我們可以使用笛卡兒乘積，一般盡避免使用，有時關(guān)聯(lián)條件 無效或者沒有關(guān)聯(lián)條件下，會造成笛卡兒連接。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)當內(nèi)存能夠提供足夠的空間時，哈希連接是oracle優(yōu)化器通常的選擇。在hash join中，oracle訪問一張表，通常是較小的表，并在內(nèi)存中建立一張基于連接 鍵的哈希表，然后它掃描連接中其

10、他的表(通常較大的表)，并根據(jù)哈希表檢測 是否有匹配的記錄。只有在初始化參數(shù)hash_join_enalbed二true，并且為參數(shù) pga_aggregate_target設(shè)置了一個足夠大的值的時候，oracle才會使用哈希連 接，這點同嵌套循環(huán)有點類似一一oracle先建立一張哈希表以利于操作進行。 當用ordered提示時，from字句當中的第一張表將用于建立哈希表。當缺少有 用的索引時，哈希連接比嵌套循環(huán)連接更加有效，也可能比排序合并連接更快， 因為在這種情況下，只有一張源表需要排序，哈希連接也可能比嵌套循環(huán)連接更 快，因為處理內(nèi)存中哈希表比檢索b-樹索引更加迅速。同排序合并連接、群集

11、 連接一樣，哈希連接只能用于等價連接。和排序合并一樣，哈希連接使用內(nèi)存資 源，并且當用于排序內(nèi)存不足時，會增加臨時表空間的i/o,這將會使哈希連接 變得更慢，最后只有基于 代價的優(yōu)化器才可以使用哈希連接。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起，如果一組已存在的索引包含了查詢所需要的所有信息，那么優(yōu)化 器將在索引中有選擇地生成一組哈希表?？赏ㄟ^范圍掃描或者快速全局掃描訪問 到每一個索引，選擇哪種索引取決于where

13、字句條件，這種方法在有大兩列而只 需要訪問少兩幾列時非常有效。where字句約束越多執(zhí)行速度越快，因為此時優(yōu) 化器在評估執(zhí)行查詢的優(yōu)化路徑時，將把約束條件作為選項看待。必須在合適的列上建立索引，這樣可以確保優(yōu)化器將索引連接作為可選項之一。 相對于快速全表掃描區(qū)別在于，快速全表掃描只有一個單一索引滿足整個查詢， 索引連接可以有多個索引滿足整個查詢。八、初始化參數(shù)db_file_multiblock_read_count定義全表掃描一次從磁盤讀取多少數(shù)據(jù)塊 pga_aggregate_target (或sort_area_size)定義用于排序的內(nèi)存，排序合并 連接需要對所有表源進行排序，為排序分配內(nèi)存將對排序合并連接的性能產(chǎn)生更 為顯著的影響。如果一次完整的排序不能在內(nèi)存中完成，那么就必須在臨時表空 間中分配臨時空間。如果該參數(shù)設(shè)置過小，那么即使最小的排序也需要額外的磁 盤i/o開銷，如果設(shè)置太大，os可能會耗盡物理內(nèi)存并不得不求助于交換空間。 hash_join_enabled指示優(yōu)化器是否考慮使用哈希連接為true|fa