[Translated from mos] Using index quick full scan (indexffs) to avoid full table Scan

Use index quick full scan (indexffs) to avoid full table scan reference: IndexFastFullScanUsageToAvoidFullTableScans (DocID70135.1) applicable to: OracleDatabase

Use index quick Full Scan (Index ffs) To Avoid Full Table Scan reference: index Fast Full Scan Usage To Avoid Full Table Scans (Doc ID 70135.1) applicable: oracle Database-Enterprise Edition-Version 7.3.0.0 to 11.2.0.3 [Release 7.3.0 to 11.2] Information I

Using index quick full scan (index ffs) to avoid full table Scan

Reference Original:
Index Fast Full Scan Usage To Avoid Full Table Scans (Doc ID 70135.1)

Applicable:
Oracle Database-Enterprise Edition-Version 7.3.0.0 to 11.2.0.3 [Release 7.3.0 to 11.2]
Information in this document applies to any platform.
* ** Checked for relevance on 2-Dec-2013 ***

Purpose:
This article discusses how to use Index Fast Full Scans (Index FFS) to avoid Full Table Scans (FTS)

Range:

Support analysts and customers who encounter queries that do not go through index ffs

Details:

When does index ffs take precedence over fts?

From Oracle8 Server Concepts manual:

1. The index must contain all the columns that appear in the query (either after select or after where ).
2. index ffs only applies to cbo
3. The hint corresponding to Index FFS is/* + INDEX_FFS ()*/

Index ffs is introduced in oracle 7.3.
In oracle 7, set the initialization parameter V733_PLANS_ENABLED to true.

An index ffs will scan all blocks in the index. The returned data is not sorted.

Index ffs can use multiple I/O blocks, or parallel queries like fts.

Example:

Use the standard emp and dept tables for example.

Preparation: Create a composite index
Create index emp_ix on emp (empno, deptno, ename );

First case: query a single table. After the select statement of the query, all the columns in the index exist:
SQL> select/* + INDEX_FFS (emp emp_ix) */empno, deptno, ename from emp;

Execution Plan
----------------------------------------------------------
0 select statement Optimizer = CHOOSE (Cost = 4 Card = 21 Bytes = 693)
1 0 INDEX (fast full scan) OF 'emp_ix '(NON-UNIQUE) (Cost = 4 Card = 21 Bytes = 693)

Case 2: query a single table, which is the index column, either after select or after where
SQL> select/* + INDEX_FFS (emp emp_ix) */empno, ename from emp where deptno>: bind1;

Execution Plan
----------------------------------------------------------
0 select statement Optimizer = CHOOSE (Cost = 4 Card = 2 Bytes = 66)
1 0 INDEX (fast full scan) OF 'emp_ix '(NON-UNIQUE) (Cost = 4 Card = 2 Bytes = 66)

Case 3: the query contains all the columns that constitute the index and the columns that are not in the index.
SQL> select/* + INDEX_FFS (emp emp_ix) */empno, ename from emp where deptno>: bind1 and sal <: bind2;

Execution Plan
----------------------------------------------------------
0 select statement Optimizer = CHOOSE (Cost = 1 Card = 1 Bytes = 46)
1 0 table access (FULL) OF 'emp' (Cost = 1 Card = 1 Bytes = 46)

Note: The CBO optimizer selects FTS because index ffs cannot meet the requirements of all columns in query.

---> We can see that although hint is used, the execution plan still does not go through index ffs, but uses table access (FULL ). The reason is simple: the index ffs won't get the correct result.

Case 4: Composite Index columns, including in the query.
SQL> select/* + INDEX_FFS (emp emp_ix) */ename from emp;

Execution Plan
----------------------------------------------------------
0 select statement Optimizer = CHOOSE (Cost = 4 Card = 21 Bytes = 147)
1 0 INDEX (fast full scan) OF 'emp_ix '(NON-UNIQUE) (Cost = 4 Card = 21 Bytes = 147)

NOTE: index ffs still chosen if subset of index columns in query

Case 5: join)
SQL> select/* + INDEX_FFS (e emp_ix) */e. ename, d. dname from emp e, dept d where e. deptno = d. deptno;

Execution Plan
----------------------------------------------------------
0 select statement Optimizer = CHOOSE (Cost = 6 Card = 4 Bytes = 168)
1 0 hash join (Cost = 6 Card = 4 Bytes = 168)
2 1 INDEX (fast full scan) OF 'emp_ix '(NON-UNIQUE) (Cost = 4 Card = 21 Bytes = 420)
3 1 table access (FULL) OF 'dept' (Cost = 1 Card = 21 Bytes = 462)

---> As you can see, for the emp table (that is, the e table), the index ffs can also be used.