Oracle 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.

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. For example, the following query forces the use of a fast full-index scan with parallelism:
 
select /*+ index_ffs(car pk_auto) parallel_index(car pk_auto)*/
distinct    color,
            count(*)
from
   car
group by color;

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

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, Oracle9i 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

Oracle9i 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.

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 index—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 of 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.