Index fast Scan (index fast full scan)

First, index fast Scan (index fast full scan)

The index fast full scan, which is very similar to index full scan, is also suitable for all types of B-tree indexes, including unique indexes and non-unique indexes. As with the index full scan, the index fast full scan also needs to scan all index rows for all leaf blocks of the target index.

There are three differences between index fast full scan and index full scan.

(1) Index fast full scan only applies to CBO.

(2) Index fast full scan can use multiple blocks of read, or can be executed in parallel.

(3) The execution result of index fast full scan is not necessarily orderly . This is because Oracle scans the index rows in the physical order of storage on disk, rather than in the logical order of index rows, so the scan results are not necessarily ordered (the physical storage order and logical storage order of the index rows in a single index leaf block are consistent But for indexed leaf blocks adjacent to the physical storage location, the physical storage order of indexed rows between blocks and blocks is not necessarily logically ordered.

Fast Full index scans is an alternative to a full table scan, the index contains all the columns that is needed For the query(the columns in the composite index contain all the columns that need to be queried), and at least one, the index key have the NOT NULL constraint With a non-null constraint). A Fast full scan accesses the data in the index itself, without accessing the table. It cannot was used to eliminate a sort operation, because the data was not ordered by the index key. It reads the entire index using Multiblock reads, unlike a full index scan, and can be parallelized.

You can specify fast full index scans with the initialization parameter optimizer_features_enable or the index_ffs hint. Fast Full index scans cannot is performed against bitmap indexes.

A fast full scan was faster than a normal full index scan in the IT can use multiblock I/O(read multiple blocks at a time, as with the whole table scan) /c3> and can is parallelized just like a table scan.

Ii. examples

1. EMP Table for Scott

Select  from EMP;

Continue inserting data

BEGIN  for inch 0..  + LOOP INSERT  into VALUES (I,concat ('TBL', I)); END LOOP; END;

Re-collect statistics for the table EMP and primary key index:

Table Compute Statistics  for Table  for  All  for  all indexes;

Re-execute

Select  from EMP;

The load policy becomes fast full Index Scans

Third, compare index fast full Scans with index fast full Scans

Index full scan with index FAST full scan two looks like, is a female compatriots, therefore has both its commonness, also has its individuality. In both cases, the commonality is not scanning.
Table instead, you can directly return all the data you need by indexing. This is undoubtedly one of the rare ways to improve query performance, because the data stored in the index is often
is much smaller than the original table's data. Here's a look at the similarities and differences between the two.

Let's compare index fast full Scans with index fast full Scans

Select /*  * /from EMP;

Select /*  * /from EMP;

Unlike the index full scan, the result of the index fast fully scan is not sorted by the index key value leading column empno of the primary key index Pk_emp, that is, the execution result of the index fast scan is indeed not necessarily orderly.

Iv. Conclusion

• Index full scan with index Fast full scan occurs when both select and where columns are present
• Index fast full scan reads indexed blocks using multiple reads, resulting in a DB file scattered reads event that reads efficiently, but is unordered read
• Index full scan reads indexed blocks sequentially using a single block read, resulting in a DB file sequential reads event, which is inefficient when reading large numbers of index scans in this way

Reference

Index full scan vs. index FAST full Scan

Index Fast Full Scan

Index fast Scan (index fast full scan)