关于Oracle执行计划

所谓执行计划

执行计划是指Oracle 运行的一条SQL 语句按照某一顺序操作的动作。

使用explain plan for 语句可以查看执行计划。在plsql developer 工具中，可以直接使用explain plan window 查看SQL 语句的执行计划。

SQL 语句为：

1. select  a.doc_id, a.content, b.title   
2.   
3.   from cms_doc_body a, cms_doc_single_attr b   
4.   
5.  where a.doc_id = b.doc_id   
6.   
7.    and b.title like 'abc%'  
8.   
9.    and a.content_type = 'text/plain'  

得到的执行计划为：

1. SELECT STATEMENT, GOAL = ALL_ROWS    
2.   
3. NESTED LOOPS   
4.   
5.   TABLE ACCESS BY INDEX ROWID    
6.   
7.    INDEX RANGE SCAN   
8.   
9.   TABLE ACCESS BY INDEX ROWID   
10.   
11.    INDEX UNIQUE SCAN  

如何阅读执行计划
执行计划应该是：由上至下，从右向左的顺序运行SQL 语句。

由上至下：在执行计划中一般含有多个节点，相同级别( 或并列) 的节点，靠上的优先执行，靠下的后执行

从右向左：在某个节点下还存在多个子节点，先从最靠右的子节点开始执行。

关于表访问方式
 

1.Full Table Scan (FTS)  全表扫描

2.Index Lookup  索引扫描

There are 5 methods of index lookup:

index unique scan    -- 索引唯一扫描

Method for looking up a single key value via a unique index. always returns a single value, You must supply AT LEAST the leading column of the index to access data via the index.

index range scan    -- 索引局部扫描

Index range scan is a method for accessing a range values of a particular column. AT LEAST the leading column of the index must be supplied to access data via the index. Can be used for range operations (e.g. > < <> >= <= between) .

index full scan    -- 索引全局扫描

Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort. For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order.

index fast full scan    -- 索引快速全局扫描，不带order by 情况下常发生

Scans all the block in the index, Rows are not returned in sorted order, Introduced in 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO, may be hinted using INDEX_FFS hint, uses multiblock i/o, can be executed in parallel, can be used to access second column of concatenated indexes. This is because we are selecting all of the index.

index skip scan    -- 索引跳跃扫描，where 条件列是非索引的前导列情况下常发生

Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the first column(s) during the search.

3.Rowid  物理ID 扫描

This is the quickest access method available.Oracle retrieves the specified block and extracts the rows it is interested in. --Rowid 扫描是最快的访问数据方式