[Translated from mos] Using index quick full scan (index ffs) to avoid full table scan, mosffs

Source: Internet
Author: User

[Translated from mos] Using index quick full scan (index ffs) to avoid full table scan, mosffs

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.




Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.