使用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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/6517/viewspace-145533/