The index_fss Hint
The index fast full scan is used in cases where a query can be
resolved without accessing any table rows. Remember not to confuse
the index fast full scan with a full-index scan.
When the index_ffs is invoked, the optimizer will scan
all of the blocks in the index using multiblock reads and access the
index in non-sequence order. You can also make an index fast full
scan even faster by combining the index_fss hint with a
parallel hint.
The index_ffs hint can also be used to trick the SQL
optimizer when you need to select on the values for the second
column in a concatenated index. This is because the entire index is
accessed, and, depending on the amount of parallelism, an index fast
full scan may be faster than an index range scan. Using the
index_ffs hint is especially useful for shops where the tables
are huge and creating a new index would require gigabytes of extra
disk space. In the case of very large tables where no high-level
index key exists for the required search column, the fast full-index
scan will always be faster than a full-table scan.
For example, consider the following concatenated index on two
non-unique columns.
create index
dept_job_idx
on
emp
(deptno, job);
Now, consider the following SQL, and assume that there is no
index on the job column.
select
ename,
job,
deptno,
mgr
from
emp
where
job = 'SALESMAN'
;
Here is the execution plan. As we expect, we see a full-table
scan on the emp table:
OPERATION
----------------------------------------------------------------------
OPTIONS OBJECT_NAME
POSITION
------------------------------
---------------------------- ----------
SELECT STATEMENT
1
TABLE ACCESS
FULL EMP
1
Now, we take the same query and add the index_ffs hint,
making sure to specify the table name and the index name.
select /*+ index_ffs(emp, dept_job_idx) */
ename,
job,
deptno,
mgr
from
emp
where
job = 'SALESMAN'
;
Here we see that the full-table scan is replaced by the faster
fast full-index scan:
OPERATION
----------------------------------------------------------------------
OPTIONS OBJECT_NAME
POSITION
------------------------------
---------------------------- ----------
SELECT STATEMENT
34
TABLE ACCESS
BY INDEX ROWID
EMP 1
INDEX
FULL SCAN
DEPT_JOB_IDX 1
Note: The index fast full scan execution plan is the mechanism
behind fast index create and recreate.
In simple terms, 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 use the index_ffs hint to force a fast-full
index scan by specifying the index_ffs in the code.
The following is one more example of how the index_ffs hint
can be used:
select distinct /*+
index_ffs(c,pk_auto) /*
color,
count(*)
from
automobiles
group by
color;
The index_ffs hint 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 by using both the index_ffs and
parallel_index hints:
select /*+ index_ffs(car pk_auto)
parallel_index(car pk_auto)*/
distinct color,
count(*)
from
car
group by
color;
Whether or not performance will benefit from using index_ffs
to force a fast-full index is not intuitive. It is going to be
up to the developer and/or DBA to run test cases with and without
the index_ffs hint and review the execution plans to verify
if there is any improvement in run time.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
