Oracle學習系列—資料庫優化—RBO訪問路徑

Oracle推薦使用CBO方式,不過在缺乏統計資訊的情況下,RBO還是值的關注得.....

使用RBO時,優化器基於可用的訪問路徑或佇列選擇相應的執行計劃.Oracle訪問路徑佇列是啟發式的可能有多種方式執行一個SQL語句,RBO會選擇低佇列的操作.

The list shows access paths and their ranking:

RBO Path 1: Single Row by Rowid

RBO Path 2: Single Row by Cluster Join

RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key

RBO Path 4: Single Row by Unique or Primary Key

RBO Path 5: Clustered Join

RBO Path 6: Hash Cluster Key

RBO Path 7: Indexed Cluster Key

RBO Path 8: Composite Index

RBO Path 9: Single-Column Indexes

RBO Path 10: Bounded Range Search on Indexed Columns

RBO Path 11: Unbounded Range Search on Indexed Columns

RBO Path 12: Sort Merge Join

RBO Path 13: MAX or MIN of Indexed Column

RBO Path 14: ORDER BY on Indexed Column

RBO Path 15: Full Table Scan

RBO Path 1: Single Row by Rowid

這種訪問路徑僅僅在where條件中明確了單行Rowid時才有效.一般是在遊標中使用.(實際中使用不多)

For example:

SELECT * FROM emp WHERE ROWID = ’AAAA7bAA5AAAA1UAAA’;

執行計劃結果如下:

OPERATION OPTIONS OBJECT_NAME

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

SELECT STATEMENT

TABLE ACCESS BY ROWID EMP

RBO Path 2: Single Row by Cluster Join

這種訪問路徑實在具有相同的簇關聯的使用才會使用,並且需要以下兩個條件為真:

Ø WHERE條件語句中兩個關聯表的每一個簇列必須相同

Ø WHERE條件語句中必須保證返回一行值

這些條件必須使用AND操作符進行連線,執行語句時,Oracle實施一個巢狀迴圈操作.

例如,emp和dept表有相同的簇列Deptno,empno是emp表的主鍵:

SELECT *

FROM emp, dept

WHERE emp.deptno = dept.deptno

AND emp.empno = 7900;

執行計劃結果如下:

OPERATION OPTIONS OBJECT_NAME

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

SELECT STATEMENT

NESTED LOOPS

TABLE ACCESS BY ROWID EMP

INDEX UNIQUE SCAN PK_EMP

TABLE ACCESS CLUSTER DEPT

pk_emp is the name of an index that enforces the primary key.

RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key

這種訪問路徑僅在以下兩中條件下才可用:

Ø WHERE條件語句中在等式表示式中包括所有的hash cluster鍵值的列,而且必須使用AND表示式連線

Ø 語句要保證返回一條記錄,因為構成hash cluster鍵的列同時就是唯一或主鍵值

執行該語句時,Oracle使用cluster hash函式演算法從hash cluster鍵值求出hash值,Oracle使用hash值對錶進行hash掃描.

例如:line_items表和Orders表儲存在一個hash cluster中,orderno列既是orders表的簇鍵也是orders表的主鍵.

SELECT *

FROM orders

WHERE orderno = 65118968;

執行計劃如下

OPERATION OPTIONS OBJECT_NAME

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

SELECT STATEMENT

TABLE ACCESS HASH ORDERS

RBO Path 4: Single Row by Unique or Primary Key

這種訪問路徑只有當WHERE條件中包括唯一索引或者主鍵值的所有列而且必須使用AND連線時才可用,

例如:empno是emmp表的主鍵列:

SELECT *

FROM emp

WHERE empno = 7900;

執行計劃如下:

OPERATION OPTIONS OBJECT_NAME

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

SELECT STATEMENT

TABLE ACCESS BY ROWID EMP

INDEX UNIQUE SCAN PK_EMP

pk_emp is the name of the index that enforces the primary key.

RBO Path 5: Clustered Join

這種訪問路徑僅適用於有相同簇列連線的語句.假如WHERE條件中以等號和AND連線方式連線所有的簇列,就會使用Clustered Join連線,同時實施一個巢狀迴圈操作.

例如:emp和dept表在具有相同的簇列deptno

SELECT *

FROM emp, dept

WHERE emp.deptno = dept.deptno;

執行計劃如下:

OPERATION OPTIONS OBJECT_NAME

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

SELECT STATEMENT

NESTED LOOPS

TABLE ACCESS FULL DEPT

TABLE ACCESS CLUSTER EMP

RBO Path 6: Hash Cluster Key

這種訪問路進僅對WHERE條件中使用所有hash cluster鍵值的列適用,對於一個組合簇鍵,等式條件必須以AND操作符連線.

例如:orders和line_itmes表儲存在一個hash簇中,orderno列是一個簇鍵

SELECT *

FROM line_items

WHERE orderno = 65118968;

執行計劃如下:

OPERATION OPTIONS OBJECT_NAME

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

SELECT STATEMENT

TABLE ACCESS HASH LINE_ITEMS

RBO Path 7: Indexed Cluster Key

這種訪問路徑僅在WHERE語句中等式表示式且使用索引簇鍵的所有列才適用.

執行語句時,Oracle在簇索引上實施一個唯一掃描用一個特殊的簇鍵值獲取單行rowid

例如,emp表儲存在一個索引簇,depno列是一個簇鍵

SELECT * FROM emp

WHERE deptno = 10;

執行計劃:

OPERATION OPTIONS OBJECT_NAME

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

SELECT STATEMENT

TABLE ACCESS CLUSTER EMP

INDEX UNIQUE SCAN PERS_INDEX

pers_index is the name of the cluster index.

RBO Path 8: Composite Index

這種訪問路徑僅在以AND操作符且等號條件使用所有組合鍵值列的條件下適用.Oracle實施一個範圍掃描以獲取選擇行的rowid,再通過rowid訪問表.

例如:有一個在job和deptno列上的組合索引

SELECT *

FROM emp

WHERE job = ’CLERK’

AND deptno = 30;

執行計劃

OPERATION OPTIONS OBJECT_NAME

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

SELECT STATEMENT

TABLE ACCESS BY ROWID EMP

INDEX RANGE SCAN JOB_DEPTNO_INDEX

job_deptno_index is the name of the composite index on the job and deptno

columns.

RBO Path 9: Single-Column Indexes

這種訪問路進僅適用於WHERE條件中包括等式條件中使用一個或多個單鍵列的組合,且必須使用AND操作符.

例如:在emp表的job列上有一個索引

SELECT *

FROM emp

WHERE job = ’ANALYST’;

執行計劃如下:

OPERATION OPTIONS OBJECT_NAME

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

SELECT STATEMENT

TABLE ACCESS BY ROWID EMP

INDEX RANGE SCAN JOB_INDEX

job_index is the index on emp.job.

RBO Path 10: Bounded Range Search on Indexed Columns

這種訪問路徑適用於那些WHERE條件中包括單索引列或者組合索引的前導列,且滿足一下條件

column = expr

column >[=] expr AND column

column BETWEEN expr AND expr

column LIKE ’c%’

每一個條件都有上下邊界值

例如:在emp表的sal列上有索引

SELECT *

FROM emp

WHERE sal BETWEEN 2000 AND 3000;

執行計劃如下

OPERATION OPTIONS OBJECT_NAME

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

SELECT STATEMENT

TABLE ACCESS BY ROWID EMP

INDEX RANGE SCAN SAL_INDEX

sal_index is the name of the index on emp.sal.

In the following statement, there is an index on the ename column of the emp table:

SELECT *

FROM emp

WHERE ename LIKE ’S%’;

RBO Path 11: Unbounded Range Search on Indexed Columns

這種訪問路徑適用於那些WHERE條件中包括單鍵列或者組合鍵的前導列,且滿足以下條件

WHERE column >[=] expr

WHERE column

例如,Emp表的sal列上有索引

SELECT *

FROM emp

WHERE sal > 2000;

執行計劃如下

OPERATION OPTIONS OBJECT_NAME

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

SELECT STATEMENT

TABLE ACCESS BY ROWID EMP

INDEX RANGE SCAN SAL_INDEX

In the following statement, there is a composite index on the order and line columns of the line_items table:

SELECT *

FROM line_items

WHERE order > 65118968;

The access path is available, because the WHERE clause uses the order column, a leading portion of the index.

This access path is not available in the following statement, in which there is an index on the order and line columns:

SELECT *

FROM line_items

WHERE line < 4;

The access path is not available because the WHERE clause only uses the line column, which is not a leading portion of the index.

RBO Path 12: Sort Merge Join

這種訪問路徑僅適用於WHERE等式使用的列沒用儲存在同一個簇中.Oracle使用sort-merge操作,Oracle也會用巢狀迴圈操作執行join表示式

例如,emp和dept表沒有儲存在同一個簇中

In the following statement, the emp and dept tables are not stored in the same

cluster:

SELECT *

FROM emp, dept

WHERE emp.deptno = dept.deptno;

執行計劃如下:

OPERATION OPTIONS OBJECT_NAME

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

SELECT STATEMENT

MERGE JOIN

SORT JOIN

TABLE ACCESS FULL EMP

SORT JOIN

TABLE ACCESS FULL DEPT

RBO Path 13: MAX or MIN of Indexed Column

這中訪問路徑僅在SELECT語句中有效,並且以下條件為真

Ø 查詢使用MAX或MIN函式求單鍵列或者組合鍵前導列的最大最小值,這些索引非簇索引

Ø 沒有其他表示式在在select列表中

Ø 沒有where條件或者group by語法

執行該查詢語句時,Oracle對索引實施全掃描以求得最大最小值,而不需要訪問全表

例如,在emp表的sal列中有索引

SELECT MAX(sal) FROM emp;

The EXPLAIN PLAN output for this statement might look like this:

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT (AGGREGATE)

2 1 INDEX (FULL SCAN (MIN/MAX)) OF 'SAL_INDEX' (NON-UNIQUE)

RBO Path 14: ORDER BY on Indexed Column

這種訪問路徑僅適用於SELECT語法,且滿足以下條件:

Ø 查詢包括Order by語法,Order by列為單鍵列或者組合索引的前導列,且非簇索引

Ø 主鍵或者非空完整性約束,以保證Order by中的列無空值

Ø NLS_SORT引數設定為BINARY

執行查詢時,Oracle實施一個索引範圍掃描獲取選中排序列的rowid,然後通過rowid訪問表

例如emp表的empno列是主鍵

SELECT *

FROM emp

ORDER BY empno;

執行計劃如下

OPERATION OPTIONS OBJECT_NAME

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

SELECT STATEMENT

TABLE ACCESS BY ROWID EMP

INDEX RANGE SCAN PK_EMP

pk_emp is the name of the index that enforces the primary key. The primary key

ensures that the column does not contain nulls.

RBO Path 15: Full Table Scan

這種訪問路徑適用於任何不考慮WHRER條件(除了SAMPLE或SAMPLE BLOCK)的語法.全表掃描效率最低.

下列條件會導致索引訪問無效

n column1 > column2

n column1 < column2

n column1 >= column2

n column1 <= column2

where column1 and column2 are in the same table.

n column IS NULL

n column IS NOT NULL

n column NOT IN

n column != expr

n column LIKE ’%pattern’

regardless of whether column is indexed.

n expr = expr2

where expr is an expression that operates on a column with an operator or function, regardless of whether the column is indexed.

n NOT EXISTS subquery

n ROWNUM

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