Oracle學習系列—資料庫優化—Access Path和join學習

呵呵,邊學習邊翻譯,權當筆記了...

CBO的訪問路徑(Access Path)

訪問路徑是從資料庫中查詢資料的方式.主要包括

Ø 全表掃描

Ø ROWID掃描

Ø 索引掃描

Ø 聚簇掃描

Ø HASH掃描

Ø 樣例表掃描

全表掃描

全表掃描時,讀取該表所有的記錄,然後過濾掉不滿足要求的資料.僅掃描和讀取高水平線一下的塊.

全表掃描時,所有塊被順序讀取.因為塊一半都是毗鄰的,為了加速處理,通常I/O呼叫比單塊要大些.每次塊讀取的數量是由DB_FILE_MULTIBLOCK_READ_COUNT引數決定的

為什麼選擇全表掃描

全表掃描的成本要比索引方式訪問離散的塊要小,這是因為全表掃描使用較大的I/O呼叫,而少數的大I/O呼叫要比多次呼叫成本更低

什麼時候選擇全表掃描

Ø 缺乏索引 如採用了函式,卻沒有建立函式索引

Ø 大批量資料 優化器認為查詢需要訪問表中的多數快

Ø 小表 表的(HWM下)總塊數小於DB_FILE_MULTBLOCK_READ_COUNT塊

Ø 高度並行

Ø 強制(HINT)全表掃描 /*+ FULL(Table_Name) */

ROWID掃描

行中的ROWID確定了包含該記錄的資料檔案和資料塊,通過ROWID方式定位一條記錄是訪問單記錄最快的方式,因為它直接給出了資料庫中該記錄最準確的位置.

索引掃描

包括以下幾種型別:

Ø 唯一索引掃描(Index Unique Scans)

通常是Unique Index 或者主鍵,最多返回單行記錄

Hint強制 INDEX(alias index_name)

Ø 範圍索引掃描(Index Range Scans)

範圍索引掃描是訪問資料最常用的方法,可以有或者沒有邊界值.資料將以索引欄位的升序返回,相通值以Rowid升序返回.

Col1 = :b1

Col1 < :b1

Col1 > :b1

Col1 like ‘XX%’

Hint強制 INDEX(alias index_name)

Ø 範圍索引降序掃描(Index Range Scans Descending)

範圍索引降序掃描和範圍索引掃描是一樣的,除了資料以降序形式返回.

Hint強制 INDEX_DESC(alias index_name)

Ø 跳轉索引掃描(Index Skip Scans)

跳轉索引掃描能夠改進非前導欄位的索引掃描,訪問索引塊通常要好於訪問資料快.例如組合索引中的前導列可能沒有用於查詢.通常適用於前導列選擇性比較高,後續列選擇性較低的查詢

例如 Employee(sex,employee_id,address)或者Employee(sex,employee_id)

Ø 全掃描(Full Scans)

全掃描適用於:所有待查詢列均包含在索引中,至少一個列非空.可以消除排序

Ø 快速全索引掃描(Fast Full Index Scans)

和全掃描相互替代,適用於待查詢欄位,快速全索引掃描掃描所有的,並且不能消除排序

同時需要指定OPTIMIZER_FEATURES_ENABLE引數

Hint強制 INDEX_FFS(alias index_name)

Ø 索引連線(Index Joins)

索引連線是幾個包含查詢時所有欄位索引的Hash連線

Hint強制 INDEX_JOIN(alias index_name)

Ø 點陣圖連線(Bitmap Joins)

點陣圖連線使用點陣圖,並把點陣圖位置轉換成Rowid,點陣圖能夠有效合併索引,滿足AND和OR條件的Boolean操作.

連線(Joins)

優化器評估每種連線的成本,然後選擇最低成本的方式.假如一個連線返回多行,優化器將會考慮以下三種情況.

巢狀迴圈連線 當一個連線返回大批記錄時(例如,超過10000行),巢狀迴圈是沒有效率的,優化器可能不會使用.巢狀迴圈的成本根據以下公式進行計算:

cost= access cost of A + (access cost of B * number of rows from A)

HASH連線 使用CBO時,當連線返回大批記錄時,HASH連線是最有效的.HASH連線的成本通過以下公式計算:

cost= (access cost of A * number of hash partitions of B) + access cost of B

排序合併連線 使用CBO時,當一個連線返回大批資料時,Merge連線也是最有效的.Merge連線的成本通過以下公式計算:

cost= access cost of A + access cost of B +(sort cost of A + sort cost of B)

如果資料已經預排序,排序成本則為0

SORT_AREA_SIZE(適用於shared server)和PGA_AGGREGATE_TARGET(被推薦使用)

反連線(Anti-join)返回左側斷言的行集,一般而言就是返回那些沒有匹配在右側的行集.可以用MERGE_AJ,HASH_AJ,NL_AJ強制hint

半連線(Semi-join)返回那些沒有重複行的Exist子查詢結果

可以使用MERGE_SJ,HASH_SJ,NL_SJ強制hint

星型查詢

資料倉儲被設計成星型模式,包含一個大的事實表和幾個小的維度表.星型查詢是事實表和查詢表(Lookup表)的連線,CBO能夠識別星型查詢,並且產生有效的執行計劃.

USE_HASH,USE_NL,USE_MERGE

USE_HASH使用HASH_AREA_SIZE和HASH_JOIN_ENABLED引數(shared server)或者PGA_AGGREGATE_TARGET(推薦)

USER_MERGE使用HASH_AREA_SIZE和SORT_AREA_SIZE引數(shared server)或者PGA_AGGREGATE_TARGET(推薦)

巢狀迴圈連線適用於一個大表和一個小表(volume<20000)的連線,通常以有條件限制的表為驅動表.

Hash連線適用於一個大表和一個較小表(volume>20000)的連線.通常以較小表作為驅動表,一般條件為等式連線.

排序合併連線適用於兩個相對獨立的行源,條件為行源已排序或者無需排序.或者適用於非等式連線.

影響CBO的引數

Ø CURSOR_SHARING

影響繫結變數的執行計劃

Ø DB_FILE_MULTIBLOCK_READ_COUNT

影響全表掃描和索引的判斷

Ø HASH_AREA_SIZE

影響HASH連線的操作

Ø HASH_JOIN_ENABLED

啟用和失效HASH連線操作

Ø OPTIMIZER_INDEX_CACHING

控制巢狀迴圈時索引探測成本

Ø OPTIMIZER_INDEX_COST_ADJ

調整索引訪問的成本

Ø OPTIMIZER_MAX_PERMUTATIONS

Ø OPTIMIZER_MODE

控制優化器的預設優化方式

Ø PARTITION_VIEW_ENABLED

分割槽檢視探測

Ø QUERY_REWRITE_ENABLED

物化檢視的查詢重寫功能

Ø SORT_AREA_SIZE

影響排序效能

Ø STAR_TRANSFORMATION_ENABLED

增強星型轉換方式

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6517/viewspace-145522/，如需轉載，請註明出處，否則將追究法律責任。