Oracle索引之index range scan/index full scan/index fast full scan

首先看个例子吧：

[PHP]

SQL> create table test (id number not null, name varchar2(30));

表已创建。

SQL> insert into test select rownum, object_name from dba_objects;

已创建30366行。

SQL> commit;

提交完成。

SQL> create index ind_test_id on test(id);

索引已创建。


SQL> exec dbms_stats.gather_table_stats(user, 'test');

PL/SQL 过程已成功完成。

SQL> set autot trace exp
SQL> select * from test where id < 10;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=9 Bytes=252)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=3 Card=9 Bytes=252)
   2    1     INDEX (RANGE SCAN) OF 'IND_TEST_ID' (NON-UNIQUE) (Cost=2Card=9)

SQL> select id from test;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=30366 Bytes=151830)
   1    0   INDEX (FAST FULL SCAN) OF 'IND_TEST_ID' (NON-UNIQUE) (Cost=4 Card=30366 Bytes=151830)

SQL> select id from test order by id;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=30366 Bytes=151830)
   1    0   INDEX (FULL SCAN) OF 'IND_TEST_ID' (NON-UNIQUE) (Cost=26 Card=30366 Bytes=151830)

[/PHP]

再来看看Tom的解释：

index full scan starts at the root block, walks down the branches on the left
hand side one by one till it gets to the first leaf block on the "left" and then
reads the index one block at a time -- leaf by leaf.  it reads the data
"sorted".

index FAST full scan reads the extent map for the index and using multi-block IO
reads the index in big chunks.  It throws out all root/branch blocks and
processes the data it finds on the leaf blocks.  It reads the data "in some
random order".

So a FAST full scan would generally read the index faster due to the multiblock
IO.

最后Oracle官方文档：

Index Scans

In an index scan, the database retrieves a row by traversing the index, using the indexed column values specified by the statement. If the database scans the index for a value, then it will find this value in n I/Os where n is the height of the B-tree index. This is the basic principle behind Oracle Database indexes.

If a SQL statement accesses only indexed columns, then the database reads values directly from the index rather than from the table. If the statement accesses columns in addition to the indexed columns, then the database uses rowids to find the rows in the table. Typically, the database retrieves table data by alternately reading an index block and then a table block.

Full Index Scan

In a full index scan, the database reads the entire index in order. A full index scan is available if a predicate (WHERE clause) in the SQL statement references a column in the index, and in some circumstances when no predicate is specified. A full scan can eliminate sorting because the data is ordered by index key.

Fast Full Index 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.

Fast full index scans are an alternative to a full table scan when both of the following conditions are met:

• The index must contain all columns needed for the query.

• A row containing all nulls must not appear in the query result set. For this result to be guaranteed, at least one column in the index must have either:

  • A NOT NULL constraint

  • A predicate applied to it that prevents nulls from being considered in the query result set

Index Range Scan

An index range scan is an ordered scan of an index that has the following characteristics:

• One or more leading columns of an index are specified in conditions. A condition specifies a combination of one or more expressions and logical (Boolean) operators and returns a value of TRUE, FALSE, or UNKNOWN.

• 0, 1, or more values are possible for an index key.

The database commonly uses an index range scan to access selective data. The selectivity is the percentage of rows in the table that the query selects, with 0 meaning no rows and 1 meaning all rows. Selectivity is tied to a query predicate, such as WHERE last_name LIKE 'A%', or a combination of predicates. A predicate becomes more selective as the value approaches 0 and less selective (or more unselective) as the value approaches 1.

Index Unique Scan

In contrast to an index range 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. An index unique scan stops processing as soon as it finds the first record because no second record is possible.