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实施一个嵌套循环操作.

例如,empdept表有相同的簇列Deptno,empnoemp表的主键:

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连接时才可用,

例如:empnoemmp表的主键列:

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连接,同时实施一个嵌套循环操作.

例如:empdept表在具有相同的簇列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操作符连接.

例如:ordersline_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访问表.

例如:有一个在jobdeptno列上的组合索引

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表达式

例如,empdept表没有存储在同一个簇中

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语句中有效,并且以下条件为真

Ø 查询使用MAXMIN函数求单键列或者组合键前导列的最大最小值,这些索引非簇索引

Ø 没有其他表达式在在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条件(除了SAMPLESAMPLE 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/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/6517/viewspace-145533/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值