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.
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 Oracle 8
and beyond, intelligence was added to the SQL optimizer to determine
if a query might be resolved exclusively within an existing index.
Also, see these
important notes on using the
index fast full scan (index_ffs) hint.
The index fast full scans (full) Oracle metric is
the number of fast full
scans initiated for full segments.
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.
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 and quickly reads
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 and all of the index
nodes are quickly cached.
Best of all, Oracle invokes
multiblock read capability, invoking multiple
processes to read the table. This is the heart of the
index fast full scan, a multiblock method for reading
an Oracle index.
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:
Execution Plan
|
Index
Access Method
|
Values
Returned
|
Index full
scan
|
Sorted
|
Node by
node
|
Fast
full-index scan
|
Unsorted
|
Multi-block reads
|
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.