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 ptimizer=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