Oracle学习系列—数据库优化—Full Scans和Fast Full Index Scans
上一篇 / 下一篇 2007-04-17 00:00:00 / 个人分类：数据库专区
不过看Oracle9i Database Performance Tuning Guide and Reference,好像没太多区别
Full Scans
A full scan is available if a predicate references one
of the columns in the index. The predicate does not need to be an index driver.
A full scan is also available when there is no predicate, if both the following
conditions are met:
n
All of the columns in the table
referenced in the query are included in the index.
n
At least one of the index columns is not
null.
A full scan can be used to eliminate a sort operation,
because the data is ordered by the index key. It reads the blocks singly.
翻译:当一个断言使用索引列中的一个时,就会使用到全扫描.断言不需要是一个索引驱动.没有断言时,也可能会使用全扫描.但是要满足以下两个条件:
查询引用的所有列必须包含在索引中
至少一个索引字段不能为空
全扫描被用来消除排序操作,因为索引键中的数据已经预先排列好,它将会逐一读取数据块.
Fast Full Index Scans
Fast full index scans are an alternative
to a full table scan when the index contains all the columns that are needed for the query, and at least one column
in the index key has the NOT NULL constraint. A fast full scan accesses the
data in the index itself, without accessing the table. It cannot be used to
eliminate a sort operation, because the data is not ordered by the index
key. It reads the entire index using multiblock reads, unlike a full index
scan, and can be parallelized. Fast full scan is available only with the CBO.
You can specify it with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint.
Fast full index scans cannot be performed
against bitmap indexes. A fast full scan is faster than a normal full index
scan in that it can use multiblock I/O and can be parallelized just like a
table scan.
快速全索引扫描时全扫描的替代,当索引包含查询所需要的全部列的时候.并且至少索引键中的一列不能为空,Fast Full Index Scans仅仅访问索引中的数据.它不能消除排序操作,索引键中的数据时未排序的. Fast
Full Index Scans通过多块读取方式读取全部索引,(和全扫描不同),也能构并行化. Fast Full Index Scans只在CBO模式下有效,可以通过设置OPTIMIZER_FEATURES_ENABLE
or the INDEX_FFS的方式强制使用Fast full index scans,全扫描不能用在位图索引上, Fast full index scans要比全扫描快一些,因为它能够多块读取,并且像全表扫描一样并行化.
index full scan需要先从root定位到第一个leaf block，然后按顺序一个一个读取所有的leaf block，所以index full
scan可以用来避免某些sort操作，这个full scan的名字有点误导人，其实并不是所有的index block都被读取的，某些分支块是不会读到的
index fast full scan则读取index的所有block，包括branch block，并且是multiblock的读取方式，所以index fast
full scan不能用来消除sort
index full scan是避免排序，因为索引已经排序，不是读取索引的全部块。通过链接读取下一块。
index fast full scanFast Full Index Scans，全部读取，包括根，叶等结点。充分发挥多块读的特性。
摘自 http://www.dbanotes.net/Oracle/Index_full_scan_vs_index_fast_full_scan.htm
当进行index full scan的时候 oracle定位到索引的root block，然后到branch block（如果有的话），再定位到第一个leaf block, 然后根据leaf block的双向链表顺序读取。它所读取的块都是有顺序的，也是经过排序的。
而index fast full scan则不同，它是从段头开始，读取包含位图块，root
block,所有的branch
block, leaf block，读取的顺序完全有物理存储位置决定，并采取多块读，没次读取db_file_multiblock_read_count个块。
Example:
|
drop table
testindex; create table
testindex as select * from dba_objects; alter table
testindex modify owner not null; alter table
testindex modify object_name not null; alter table
testindex modify object_type not null; create index
testfullindex on testindex(owner,object_name,object_type); analyze table
testindex compute statistics; analyze index
testfullindex compute statistics; |
|
select /*+
index(testindex TESTFULLINDEX)*/ owner,object_name,object_type from testindex select
owner,object_name,object_type from testindex order by
owner,object_name,object_type |
|
select
owner,object_name,object_type from testindex |
导入论坛 引用链接 收藏 分享给好友 推荐到圈子 管理 举报
TAG:
标题搜索
日历
|
|||||||||
| 日 | 一 | 二 | 三 | 四 | 五 | 六 | |||
| 1 | 2 | 3 | 4 | 5 | |||||
| 6 | 7 | 8 | 9 | 10 | 11 | 12 | |||
| 13 | 14 | 15 | 16 | 17 | 18 | 19 | |||
| 20 | 21 | 22 | 23 | 24 | 25 | 26 | |||
| 27 | 28 | 29 | 30 | 31 | |||||
数据统计
- 访问量: 18920
- 日志数: 64
- 建立时间: 2007-12-07
- 更新时间: 2008-07-24
