 |
|
The index_ffs Hint
Oracle Tips by Burleson Consulting |
As I noted in earlier chapters, 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
fast_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.
The use_concat Hint
The use_concat hint requests that a
union all execution plan be used for all OR conditions in the
query, rewriting the query into multiple queries. The use_concat
hint is commonly invoked when a SQL query has a large amount of OR
conditions in the where clause.
For example, consider the following query
where a B-tree index exists on job, deptno, and sal.
It is interesting to note that if the indexes were bitmap indexes,
the execution would not perform a full-table scan. This is because
Oracle automatically uses bitmap indexes where a query has multiple
OR conditions on bitmap index columns.
select
ename
from
emp
where
deptno = 10
or
sal < 5000
or
job = ‘CLERK’;
Here we have two choices. Because all of the
index columns are low cardinality, we could create three bitmap
indexes on deptno, sal, and clerk, causing a bitmap
merge execution plan. Our other choice is to invoke use_concat
to break the query into three separate B-tree index scans whose
result sets will be combined with the union operator.
Here is the execution plan for this query
with B-tree indexes. Note that we must perform a full-table scan to
satisfy the multiple OR conditions in the where clause:
OPERATION
----------------------------------------------------------------------
OPTIONS
OBJECT_NAME
POSITION
------------------------------ ----------------------------
----------
SELECT STATEMENT
1
TABLE ACCESS
FULL EMP
1
If our indexes had been bitmap indexes, we
would have seen a far faster execution plan:
OPERATION
----------------------------------------------------------------------
OPTIONS
OBJECT_NAME
POSITION
------------------------------ ----------------------------
----------
SELECT STATEMENT
4
TABLE ACCESS
BY INDEX ROWID
EMP 1
BITMAP CONVERSION
TO ROWIDS
1
BITMAP OR
1
BITMAP INDEX
SINGLE VALUE
EMP_DEPTNO_BIT
1
BITMAP MERGE
2
BITMAP INDEX
RANGE SCAN
EMP_SAL_BIT
1
BITMAP INDEX
SINGLE VALUE
EMP_JOB_BIT
3
Now, returning to our example with three B-tree
indexes, let’s add the use_concat hint and see the change to
the execution plan.
select /*+
use_concat */
ename
from
emp
where
deptno = 10
or
sal < 5000
or
job = ‘CLERK’;
Here we see that the full-table scan has been
replaced with a union of three queries, each using the B-tree index
for the single columns and the CONCATENATION plan to union
the result sets:
OPERATION
----------------------------------------------------------------------
OPTIONS
OBJECT_NAME
POSITION
------------------------------ ----------------------------
----------
SELECT STATEMENT
3
CONCATENATION
TABLE ACCESS
BY INDEX ROWID
EMP
1
INDEX
RANGE SCAN
EMP_JOB
1
TABLE ACCESS
BY INDEX ROWID
EMP
2
INDEX
RANGE SCAN
EMP_SAL
1
TABLE ACCESS
BY INDEX ROWID
EMP
3
INDEX
RANGE SCAN EMP_DEPT
1
For details on tuning queries with multiple
OR conditions, please see Chapter 14. Next let’s take a look at how
parallel hints can be added to queries to improve the performance of
full-table scans.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
