In keeping with Oracle’s commitment to add intelligence to SQL query optimization, the full-index SQL execution plan has been enhanced to provide support for function-based indexes (FBIs). With Oracle8, intelligence was added to the SQL optimizer to determine if a query could be resolved exclusively within an existing index. Oracle’s index-organized table (IOT) structure is an excellent example of how Oracle is able to bypass table access whenever an index exists. In an IOT structure, all table data is carried inside the b-tree structure of the index, making the table redundant.

Whenever the Oracle SQL optimizer detects that the query is serviceable without touching table rows, Oracle invokes a full-index scan, quickly reading every block of the index, without touching the table itself. It is important to note that a full-index scan does not read the index nodes. Rather, a block-by-block scan is performed, quickly caching all of the index nodes. Best of all, Oracle invokes multiblock read capability, calling upon multiple processes to read the table.

Oracle and multiblock reads
For speeding 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 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.
  • 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 distinct /*+ index_ffs(c,pk_auto) parallel_index(automobile, pk_auto)
color, count(*)
group by color;


It is not always intuitive whether a fast full-index scan is the quickest way to service a query because of all the variables involved. Therefore, 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 must 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.

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.

Let’s use a simple example to illustrate how a full-index scan would work with a function-based index. Here is a simple student table:
create table student
(student_name varchar2(40), date_of_birth date);


Using this table, you can create a concatenated function-based index of all columns in the table. In this example, the functions are initcap (capitalize the first letter of each word) and to_char (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 that references these columns in a SQL statement 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 of the SQL predicates in the WHERE clause match those columns in the index, and the query must return enough rows for the table in order for the cost-based optimizer to recognize that the full-index scan is faster than a traditional index access. This choice of invoking 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 propensity of the cost-based optimizer to favor full-index 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 to be.
  • The presence of histograms on the index: For skewed indexes, this helps the cost-based optimizer evaluate the number of rows that are 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 their database migrates to Oracle9i. However, there are multitudes of factors that are 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.