index fast full scan tips

Question:  What is the fast full scan scan and how does an index fast full scan differ from an index range scan operation?

Answer:  Index full scans are related to fast full-index scans, which were introduced in Oracle 7.3. There are some SQL queries that can be resolved by reading the index without touching the table data. For example, the following query does not need to access the table rows, and the index alone can satisfy the query.

select distinct
   color,
   count(*)
from
   automobiles
group by
   color;

Oracle enhanced the fast full-index scan to make it behave similar to a full-table scan. Just as Oracle has implemented the initialization parameter db_file_multiblock_read_count for full-table scans (deprecated in 11g), Oracle allows this parameter to take effect when retrieving rows for a fast full-index scan. Since the whole index is accessed, Oracle allows multi-block reads.

There is a huge benefit to not reading the table rows, but there are some requirements for Oracle to invoke the fast full-index scan.

• All of the columns required must be specified in the index. That is, all columns in the select and where clauses must exist in the index.

• The query returns more than 10 percent of the rows within the index. This 10 percent figure depends on the degree of multi-block reads and the degree of parallelism.

• You are counting the number of rows in a table that meet a specific criterion. The fast full-index scan is almost always used for count(*) operations.

The cost-based optimizer will make the decision about whether to invoke the fast full-index scan in accordance with the table and index statistics. You can also force a fast full-index scan by specifying the index_ffs hint:

select distinct /*+ index_ffs(c,pk_auto) /*
color,
count(*)
from
automobiles
group
by color;

It is not always intuitive whether a fast full-index scan is the fastest way to service a query, because of all of the variables involved. Hence, most expert SQL tuners will time any query that meets the fast full-index scan criteria and see if the response time improves.

The index fast full scans (full) Oracle metric is the number of fast full scans initiated for full segments.

• All of the columns required by SQL must reside in the index tree; that is, all columns in the SELECT and WHERE clauses must exist in the index.
  
• The query accesses a substantial number of rows. Depending on which expert you ask, this percentage varies from 10 percent to 25 percent, but this figure depends heavily on the settings for db_file_multiblock_read_count and the degree of parallelism for the query. Click here for more details on setting db_file_multiblock_read_count.
  
• Because the index nodes are not retrieved in index order, the rows will not be sequenced. Hence, an ORDER BY clause will require an additional sorting operation.

Oracle provides a SQL hint to force a full-index scan. You can also force a fast full-index scan by specifying the index_ffs hint, and this is commonly combined with the parallel_index hint to improve performance.

The Full-Index Scan Report

The next report shows all full index scans. As you will recall, the Oracle optimizer will sometimes perform an full index scan in lieu of a large sort in the TEMP tablespace. You will commonly see full-index scans in blocks of SQL code that have the ORDER BY clause.

Mon Feb 29                                                   page    1
                         Index full scans and counts

OWNER     TABLE_NAME           INDEX_NAME              NBR_SCANS
--------- -------------------- -------------------- ------------
DONALD    BOOK                 BOOK_ISBN                   2,295
DONALD    PAGE                 ISBN_SEQ_IDX                  744

WARNING: Do not confuse the index full scan execution plan with the fast full-index scan. The index full scan reads each index node in SORTED order, while the fast full-index scan is used to retrieve table rows from the index in UNSORTED order.

Let�s make sure you know the differences between an index full scan and a fast full-index scan:

Table 1: The Types of Full-Index Execution Plans

Index full scan Oracle will choose an index full scan when the CBO statistics that indicate that a full-index scan is going to be more efficient than a full-table scan and a sort of the result set. The full-index scan is normally invoked when the CBO determines that a query will return numerous rows in index order, and a full-table scan and sort option may cause a disk sort to the TEMP tablespace.

Fast full-index scan This execution plan is invoked when a index contains all of the values required to satisfy the query and table access is not required. The fast full-index scan execution plan will read the entire index with multi-block reads (using db_file_multiblock_read_count) and return the rows in unsorted order. In Oracle8i, fast full-index scans are available by default in the CBO, while in Oracle8 you must set the fast_full_scan_enabled initialization parameter. In Oracle7, you must set the v733_plans_enabled initialization parameter. You can force a fast full-index scan with the index_fss hint.

To see how the CBO evaluates a query for a full-index scan, let�s take a simple example. The database could service the SQL command select * from customer order by cust_nbr; in two ways:

• It could perform a full-table scan and then sort the result set. The full-table scan could be performed very quickly with db_file_multiblock_read_count initialization parameter set, or the table access could be parallelized by using a parallel hint. However, the result set must then be sorted in the TEMP tablespace.

• It could obtain the rows in index order by invoking the full-index scan by reading the rows via the index, thus avoiding a sort.

Oracle index scans and multiblock reads

To speed table and index block access, Oracle uses the db_file_multiblock_read_count parameter (which defaults to 8) to aid in getting full-table scan and full-index scan data blocks into the data buffer cache as fast as possible. However, this parameter is used only when a SQL query performs a full-table scan, and in most cases, a query uses an index to access the table.

For full-index scans, Oracle imposes some important restrictions:

• All of the columns required by SQL must reside in the index tree; that is, all columns in the SELECT and WHERE clauses must exist in the index.
   
• The query accesses a substantial number of rows. Depending on which expert you ask, this percentage varies from 10 percent to 25 percent, but this figure depends heavily on the settings for db_file_multiblock_read_count and the degree of parallelism for the query. Click here for more details on setting db_file_multiblock_read_count.
   
• Because the index nodes are not retrieved in index order, the rows will not be sequenced. Hence, an ORDER BY clause will require an additional sorting operation.

Basics of function-based indexes

Prior to Oracle9i, full-index scans were possible only when the index was created without any null values. In other words, the index had to be created with a NOT NULL clause for Oracle to be able to use the index. This has been greatly enhanced in Oracle9i with support for index-only scans using function-based indexes.  Click here to see how to use a function-based index to index on NULL values.

As a quick review, function-based indexes were an important enhancement in Oracle8, because they provided a mechanism for the virtual elimination of the unnecessary, long-table full scan. Because a function-based index can exactly replicate any column in the WHERE clause of a query, Oracle will always be able to match the WHERE clause of a SQL query with an index.

Here, I will use a simple example of a student table to illustrate how a full-index scan would work with a function-based index:
 
create table student
(student_name varchar2(40), date_of_birth date);
 

Using this table, create a concatenated function-based index of all columns of the table. In this example, the functions are initcap (i.e., capitalize the first letter of each word) and to_char (i.e., change a date to a character):
 
create index whole_student
on student
(
   (initcap(student_name),
   to_char(date_of_birth,?MM-DD-YY?)
);
 

With the function-based index defined, Oracle will recognize that any SQL statement that references these columns will be able to use the full-index scan. Here is an example of some SQL queries that match the function-based index:
 
select * from student
where initcap(student_name) = ?Jones?;

select * from student
where to_char(date_of_birth,?MM-DD=YY?) = ?04-07-85?;
 

Invoking the full-index scan with a function-based index

Oracle will always use the function-based index whenever possible and will invoke a full-index scan on the function-based index. It will do so when the cost-based SQL optimizer statistics indicate that the full-index scan will be faster than a b-tree access via the index.

Once a function-based index is created, you need to create CBO statistics, but beware that there are numerous bugs and issues when analyzing a function-based index.  See these important notes on statistics and function-based indexes.

Troubleshooting tip!  For testing, you can quickly test the effect of another optimizer parameter value at the query level without using an ?alter session? command, using the new opt_param SQL hint:

select /*+ opt_param('optimizer_mode','first_rows_10') */ col1, col2 . . .

select /*+ opt_param('optimizer_index_cost_adj',20) */ col1, col2 . .

Here are the criteria for invoking an index-only scan with a function-based index. All SQL predicates in the WHERE clause match those columns in the index; the query must return enough rows from the table for the cost-based optimizer to recognize that the full-index scan is faster than a traditional index access. The decision to invoke a full-index scan depends on several parameter settings:

• Proper statistics for the cost-based optimizer:  The schema should have been recently analyzed, and the optimizer_mode parameter must not be set to RULE.
   
• The degree of parallelism on the index:  Note that the parallel degree of the index is set independently; the index does not inherit the degree of parallelism of the table.
   
• The setting for optimizer_index_cost_adj:  This controls the relative cost of index access.  The smaller the value, the less expensive index access becomes, relative to full scans.
   
• The setting for db_file_multiblock_read_count:  This parameter factors in the cost of the full-index scan. The higher the value, the 'cheaper' the full-index scan will appear.
   
• The presence of histograms on the indexed colum:  For skewed indexes, this helps the cost-based optimizer evaluate the number of rows returned by the query.

An important Oracle enhancement

The fast full-index scan on function-based indexes is another enhancement in Oracle9i. Many databases automatically begin to use this new execution plan when the database migrates to Oracle9i. However, there are several factors considered by the cost-based SQL optimizer when choosing to invoke a full-index scan.

It's important that the Oracle professional have the appropriate parameter settings to ensure that the cost-based optimizer does not use a fast full-index scan in an inappropriate fashion.