Shulou(Shulou.com)06/01 Report--

There are three common situations that are useful to index full scan.

1. A query column is an index column

two。 When order by an index column

3. When calculating the aggregation of the cable column

Optimize it through case study-- Index FULL SCAN and Index FAST FULL SCAN

How Index FULL SCAN and ndex FAST FULL SCAN work:

The application of Index FULL SCAN and Index FAST FULL SCAN: when the columns we want to select are included in the index, we can use IFS or FFS instead of a full table scan to get the desired results.

INDEX FULL SCAN:

HINT is written as INDEX (table name index name)

How it works: ORACLE locates to the ROOT BLOCK of the index, then to BRANCH BLOCK (if any), then to the first LEAF BLOCK, and then reads according to LEAF BLOCK's two-way linked list order. The blocks it reads are all ordered and sorted.

INDEX FAST FULL SCAN:

HINT is written as INDEX_FFS (table name index name)

Principle: starting from the beginning of the segment, reading contains bitmap blocks, ROOT BLOCK, all BRANCH BLOCK,LEAF BLOCK, the order of reading is completely determined by the physical storage location, and take multi-block reads, reading DB_FILE_MULTIBLOCK_READ_COUNT blocks at a time. When querying the total number of records in a table, PRIMARY KEY-based INDEX FAST FULL SCAN is often the most efficient.

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.

Http://download-west.oracle.com/doc... Imops.htm#51111

Full Table Scans:

This type of scan reads all rows from a table and filters out those that do not meet the selection criteria. During a full table scan, all blocks in the table that are under the high water mark are scanned. Each row is examined to determine whether it satisfies the statement's WHERE clause.

When Oracle performs a full table scan, the blocks are read sequentially. Because the blocks are adjacent, I/O calls larger than a single block can be used to speed up the process. The size of the read calls range from one block to the number of blocks indicated by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT. Using multiblock reads means a full table scan can be performed very efficiently. Each block is read only once.

Http://download-west.oracle.com/doc... Imops.htm#44852

Provenance:

Http://blog.51cto.com/tiany/1582044

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.