首页 > 数据库 > mysql教程 > 关于Oracle执行计划

关于Oracle执行计划

WBOY
发布: 2016-06-07 17:20:33
原创
1460 人浏览过

执行计划是指Oracle 运行的一条SQL 语句按照某一顺序操作的动作。使用explain plan for 语句可以查看执行计划。在plsql develope

所谓执行计划

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

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

SQL 语句为:

得到的执行计划为:

如何阅读执行计划
执行计划应该是:由上至下,从右向左的顺序运行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. > >=

 

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 扫描是最快的访问数据方式

linux

相关标签:
来源:php.cn
本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板