索引扫描类型分类

博主： admin
发布时间：2019 年 07 月 29 日
811次浏览
4523字数
分类：

已归录

INDEX UNIQUE SCAN

An index unique scan must have either 0 or 1 rowid associated with an index key. The database performs a unique scan when a predicate references all of the columns in a UNIQUE index key using an equality operator. 通过唯一索引查找一个数值返回单个ROWID。对于唯一组合索引，要在where的谓词“=”后包含所有列的“布尔与”。INDEX UNIQUE SCAN 只可能发生在 unique index/ primary key 等值查找。

HINT：无需指定，满足条件会自动走 INDEX UNIQUE SCAN

SQL> create unique index idx_myobj on myobject(object_id);
SQL> select * from myobject where object_id=10;

INDEX RANGE SCAN

索引范围扫描会扫描一个 root 块，（索引高度-2）个分支块，一部分叶子块，所谓的范围，就是指这一部分叶子块，范围扫描是通过叶子块之间的双向链来实现的。索引范围扫描是单块读，所以，索引范围扫描返回数据越少性能越好。当返回行数过多时，Oracle 走接走全表扫描了，一方面是回表开销，另一方面就是走索引是单块读，走全表是多块读。

使用 INDEX RANGE SCAN 的3种情况：
(a) 在唯一索引列上使用了range操作符(> < <> >= <= between)
(b) 在唯一组合索引上，对组合索引使用部分列进行查询(含引导列），导致查询出多行
(c) 对非唯一索引列上进行的任何查询（不含‘布尔或’，它使用不上索引）。

HINT：INDEX(表名/别名索引名）

SQL> create index idx_myobj on myobject(object_id);
SQL> select * from myobject where object_id=10;

INDEX RANGE SCAN DESCENDING

索引扫描默认是升序扫描，当有 order by desc 这种需求的时候，可能会进行索引降序范围扫描，这种情况多出现在于分页语句上。

HINT: INDEX_DESC(表名/别名索引名)

INDEX FULL SCAN

In a full index scan, the database reads the entire index in order.

跟 RANGE 一样的，只不过 RANGE 是扫描部分叶子块，FULL 是扫描所有的叶子块。它扫描的结果是有序的，因为索引是有序的。索引全扫描只在 CBO 模式下才有效。CBO 根据统计数值得知进行索引全扫描比进行全表扫描更有效时，才进行索引全扫描。

它通常发生在下面几种情况，但是注意，即使 SQL 满足以下情况也不一定会走索引全扫描。
1.SOL 语句有 order by 选项，并且 order by 的列都包含在索引中，并且 order by 后列顺序必须和索引列顺序一致。
2.在进行 SORT MERGE J0IN 的时候，如果要查询的列通过索引就能获得，那就不必进行全表扫描了，另外也避免了排序，因为 INDEX FULL SCAN 返回的结果已经排序。
3.当查询中有 GROUP BY，并且 GROUP BY 的列包含在索引中

HINT：INDEX(表名/别名索引名）

下面是一个例子：

SQL> update myobject set object_id = rownum;
SQL> alter table myobject modify object_id not null; 
SQL> create index idx_myobj on myobject(object_id);
SQL> select * from myobject order by object_id;
因为索引列非空，所以索引的行数能够如实地反映表的行数。索引又是有序的，所以只需要按照索引行从上到下依次回表取到需要的数据即可。

再看一个官方文档上的例子：

SQL> SELECT department_id, last_name, salary 
FROM   employees
WHERE  salary > 5000 
ORDER BY department_id, last_name;

assume that department_id, last_name, and salary are a composite key in an index. Oracle Database performs a full scan of the index, reading it in sorted order (ordered by department ID and last name) and filtering on the salary attribute.

INDEX FAST FULL SCAN

A fast full index scan is a full index scan in which the database accesses the data in the index itself without accessing the table, and the database reads the index blocks in no particular order.

当 SQL 要查询的数据能够完全从索引中获得，那么 Oracle 就不会走全表扫描了，就会走索引快速全扫描。索引快速全扫描类似全表扫描，它可以多块读，并且可以并行扫描。

扫描索引中的所有的数据块，与 index full scan 很类似，但是一个显著的区别是 full scan 是根据叶子块的双向列表顺序读取，读取的块是有顺序的，也是经过排序的，所以返回的列表也是排序的。而 fast full scan 在读取叶子块时的顺序完全由物理存储位置决定，并采取多块读，每次读取 DB_FILE_MULTIBLOCK_READ_COUNT 个块。

HINT: INDEX_FFS(表名/别名索引名)

跟上面的 INDEX FULL SCAN 情景一样，只是查询语句改为如下语句：
SQL> select count(*) from myobject;
因为索引列非空，所以只需要统计索引的行数据即可，不需要回表。

如果是列存储或者 In-Memory Option，就不需要 INDEX FAST FULL SCAN，因为可以直接把需要的列直接从表里拿出来。

INDEX FULL SCAN (MIN/MAX)

通常发生在 select max(xxx) 或者 select min(xxx) 并且 xxx 列上有索引。
因为 MIN/MAX 函数作用在列上时，本身就忽略空间，而索引也不存储空值，所以不需要索引列上有 not null 属性。

HINT：无需指定，满足条件会自动走 INDEX FULL SCAN(MIN/MAX)

SQL> create index idx_myobj on myobject(object_id);
SQL> select max(object_id) from myobject;

INDEX SKIP SCAN

当组合索引（单列索引就不存在 skip 一说了）的引导列(第一列)没有出现在条件语句中，并且引导列基数很低时，Oracle 也会走索引，是因为 9i 开始提供的 skip scan index 功能。

HINT: INDEX_SS(表名/别名索引名)

例如：索引(gender,ename,job,mgr)，Oracle发现第一个字段值很少的情况下，了为提高索引的利用率，Oracle 将这个索引拆成('M',ename,job,mgr) 和 ('F',ename,job,mgr) 两个复合索引。这样即使没有 gender 字段，oracle 也会到男索引树和女索引树进行搜索。

下面是一个例子，object_type 的可选性极低，此时，Oracle 就可能会使用 INDEX SKIP SCAN。

SQL> create index idx_myobj on myobject(object_type,object_id);
SQL> select * from myobject where object_id=10;

An index skip scan uses logical subindexes of a composite index. The database "skips" through a single index as if it were searching separate indexes. Skip scanning is beneficial if there are few distinct values in the leading column of a composite index and many distinct values in the nonleading key of the index.

改进：如果发现了 INDEX SKIP SCAN，直接把走了索引的列新建一个索引就好了。

TABLE ACCESS BY INDEX ROWID

TABLE ACCESS BY INDEX ROWID 表示回表，例如：

SQL> create index idx_myobj on myobject(object_id);
SQL> select object_id from myobject where object_id=2 and object_type='TABLE';

如果查询在索引中就能完成，则不需要回表：

SQL> create index idx_myobj on myobject(object_id,object_type);
SQL> select object_id from myobject where object_id=2 and object_type='TABLE';

-- By 许望（RHCA、OCM、VCP）

最后修改：2020 年 06 月 01 日 02 : 24 PM

如果觉得我的文章对你有用，请随意赞赏

Tags: Oracle暂未分类

索引扫描类型分类

admin • 2019 年 07 月 29 日

已归录

INDEX UNIQUE SCAN

HINT：无需指定，满足条件会自动走 INDEX UNIQUE SCAN

SQL> create unique index idx_myobj on myobject(object_id);
SQL> select * from myobject where object_id=10;

INDEX RANGE SCAN

HINT：INDEX(表名/别名索引名）

SQL> create index idx_myobj on myobject(object_id);
SQL> select * from myobject where object_id=10;

INDEX RANGE SCAN DESCENDING

索引扫描默认是升序扫描，当有 order by desc 这种需求的时候，可能会进行索引降序范围扫描，这种情况多出现在于分页语句上。

HINT: INDEX_DESC(表名/别名索引名)

INDEX FULL SCAN

In a full index scan, the database reads the entire index in order.

HINT：INDEX(表名/别名索引名）

下面是一个例子：

SQL> update myobject set object_id = rownum;
SQL> alter table myobject modify object_id not null; 
SQL> create index idx_myobj on myobject(object_id);
SQL> select * from myobject order by object_id;
因为索引列非空，所以索引的行数能够如实地反映表的行数。索引又是有序的，所以只需要按照索引行从上到下依次回表取到需要的数据即可。

再看一个官方文档上的例子：

SQL> SELECT department_id, last_name, salary 
FROM   employees
WHERE  salary > 5000 
ORDER BY department_id, last_name;

INDEX FAST FULL SCAN

A fast full index scan is a full index scan in which the database accesses the data in the index itself without accessing the table, and the database reads the index blocks in no particular order.

HINT: INDEX_FFS(表名/别名索引名)

跟上面的 INDEX FULL SCAN 情景一样，只是查询语句改为如下语句：
SQL> select count(*) from myobject;
因为索引列非空，所以只需要统计索引的行数据即可，不需要回表。

如果是列存储或者 In-Memory Option，就不需要 INDEX FAST FULL SCAN，因为可以直接把需要的列直接从表里拿出来。

INDEX FULL SCAN (MIN/MAX)

HINT：无需指定，满足条件会自动走 INDEX FULL SCAN(MIN/MAX)

SQL> create index idx_myobj on myobject(object_id);
SQL> select max(object_id) from myobject;

INDEX SKIP SCAN

HINT: INDEX_SS(表名/别名索引名)

下面是一个例子，object_type 的可选性极低，此时，Oracle 就可能会使用 INDEX SKIP SCAN。

SQL> create index idx_myobj on myobject(object_type,object_id);
SQL> select * from myobject where object_id=10;

改进：如果发现了 INDEX SKIP SCAN，直接把走了索引的列新建一个索引就好了。

TABLE ACCESS BY INDEX ROWID

TABLE ACCESS BY INDEX ROWID 表示回表，例如：

SQL> create index idx_myobj on myobject(object_id);
SQL> select object_id from myobject where object_id=2 and object_type='TABLE';

如果查询在索引中就能完成，则不需要回表：

SQL> create index idx_myobj on myobject(object_id,object_type);
SQL> select object_id from myobject where object_id=2 and object_type='TABLE';