运管所职务: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 <[=] expr

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 <[=] expr

例如,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