INDEX_FFS 能使用前提是所有访问的列都在index中join rowid 实现-CSDN博客

本文链接：https://blog.csdn.net/jnrjian/article/details/142339690

APPLIES TO:

Oracle Database - Enterprise Edition - Version 7.3.0.0 to 12.1.0.2 [Release 7.3.0 to 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

PURPOSE

This article discusses using Index Fast Full Scans (Index FFS) to avoid Full Table Scans (FTS).

SCOPE

Support Analysts and customers who are experiencing problems with their queries doing FTS as opposed to using Index FFS.

DETAILS

When Will Index FFS be used in preference to FTS?

From the Oracle8 Server Concepts manual:

The index must contain all the columns referenced in the query.
Index FFS is only available with Cost Based Optimizer (CBO) (Index hint forces CBO).
Index FFS can be hinted with /*+ INDEX_FFS() */ .

Index FFS was introduced in 7.3.
In Oracle7 it requires initialization parameter V733_PLANS_ENABLED to be set to TRUE .

An Index FFS will scan all blocks in the index. The returned data is not sorted.
Index FFS can use multiblock I/O and can be parallelized just like a Full Table Scan.

Examples:

Using the standard emp and dept tables (these can be created with UTLSAMPL.SQL) on Oracle 8.0.5 with no statistics on tables and indexes. The execution plans were generated using autotrace (see Note:43214.1) :

Preparation: Create a composite index

create index emp_ix on emp(empno, deptno, ename);

Query of single table, where query selects all columns in index
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 Ca
rd=21 Bytes=693)
Query single table, where columns of index are either in select or where clause
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 Ca
rd=2 Bytes=66)
Query with all columns of index plus column that is 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: CBO chooses FTS as INDEX FFS cannot satisfy all columns in query
Query involving some of the columns in composite index
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 Ca
rd=21 Bytes=147)

NOTE: INDEX FFS still chosen if subset of index columns in query
Query involving 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)

Index Fast Full Scan NOT used when using other columns than in the index

gumpx

i'vh currently the problem hat my table has 170GB (50million rows) size and approx 25 columns

一千万一G前提是10个column附近

i'vh one index (22GB size) with three columns of the table (in this order):

- processing_status (not null)----查处理状态吧

- billing_type (nullable)

- aggegration_id (nullable)

The final result set is about 20k-50k rows

Because of the low selectivity of all columns in the where clause, I want to use an Fast Full Index Scan to filter the data and then continue to table. The cardinality after index filte should be ok for table access.

index fast full scan不支持第二步回表

So here my Problem:

If I'm goint to use only a count(*) or the three columns in the INDEX i'll get the following plan:

Plan hash value: 1993306444

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 9 | 498K (2)| 00:00:50 |

| 1 | SORT AGGREGATE | | 1 | 9 | | |

|* 2 | INDEX FAST FULL SCAN| DMS_DATA_AGG_I | 80M| 689M| 498K (2)| 00:00:50 |

----------------------------------------------------------------------------------------

But when i'm going to add some other columns, it will fall back to Full Table Scan:

Plan hash value: 2483148220

------------------------------------------------------------------------------

------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 43M| 662M| 3791K (2)| 12:38:18 |

|* 1 | TABLE ACCESS FULL| DMS_DATA | 43M| 662M| 3791K (2)| 12:38:18 |

------------------------------------------------------------------------------

If i'm trying to force by "/*+ INDEX_FFS(this_ DATA_AGG_I)" hint it will be ignored ...

Is there a special restriction?

Kind Rgds,

Daniel

-----------------------------

Here's a quotation from Note 67522.1:

CBO can use a Index Fast Full Scan (INDEX_FFS) as long as the index contains all the columns that are needed for the query.

Therefore, a query against all (indexed )columns does not apply.

Hi top.gun,

first thy for the fast reply.

Hmm i think I will try to explain a little bit more:

Here an sample sql:

select *

from dms_data b

where b.processing_status=2

and not (b.billing_type in (-1, 0, 1))

and b.aggregation_id is null;

Normally if the selectivity of one of the where-clause columns will be good a INDEX RANGE SCAN will be the preferred way. 选择性好的话INDEX RANGE SCAN 有效，否则全部都要过一遍index，如上例not (b.billing_type in (-1, 0, 1)) 没有这个not如果返回2w行其实也会是可以的，但是not的话不行
But non of the columns are really selectiv by itself. So if i'm just calling process_status i will get 95% of the whole table,same for billing_type and aggregation_id...这里应该不对，index不是一列一列看的，而是多列一起看的，not导致的
So the combination of all three column will bring selectivity.如果这样index是可以range scan的关键是not
So far as i see i'm to going to scan always a big part of the index - in case of index range scan by single block read - slow, and in case of fast full scan by mblr（多块读） - fast ...

I think therefore the fast full index scan would be useful? But can't be used with other colums than the index ones.

------
Hi, Daniel,

When you choose another column, are you specifying it in the SELECT list, the WHERE clause, or both?

Is there any difference in behavior, dependent upon whether or not the additional column allows NULLs or not?

Sincerely,

-------------
The WHERE Clause is complete. So will just add columns in den SELECT list only. Here an example:

-- SLOW // Uncached FULL TABLE SCAN

select *

from dms_data b

where b.processing_status=2

      and not (b.billing_type in (-1, 0, 1))

      and b.aggregation_id is null;

-- FAST // Uncached FAST FULL INDEX SCAN / Direct Path Read

select processing_status

from dms_data b

where b.processing_status=2

      and not (b.billing_type in (-1, 0, 1))

      and b.aggregation_id is null;

No difference between if adding column with null or not null. Both will lead to an Full Table Scan.

--------select * 决定ffs不能使用
You are selecting all columns so a FFIS is not going to happen.

So if proved the 3 columns together is highly selective, then the index (processing_status, billing_type, aggregation_id) should be a good one.

Otherwise the FTS may truely be the quickest way.

Hi Ted,

thy for you information, this may be a feature request :-)

i'vh tried to build now a workaround sql like, doing a self join:

select /*+ LEADING(c a) */ a.*

from dms_data a,

(select /*+ INDEX_FFS(b DMS_DATA_AGG_I) CARDINALITY(b, 50000) NO_MERGE */ rowid

from dms_data b

where b.processing_status=2

      and not (b.billing_type in (-1, 0, 1))

      and b.aggregation_id is null

) c

where a.rowid = c.rowid;

Here the plan:

Plan hash value: 4044183599

----------------------------------------------------------------------------------------------

| Id | Operation                   | Name           | Rows | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                | 50000 |    21M|   548K (2)| 00:00:55 |

|   1 | NESTED LOOPS               |                | 50000 |    21M|   548K (2)| 00:00:55 |

|   2 |   VIEW                      |                | 50000 |   585K|   498K (2)| 00:00:50 |

|* 3 |    INDEX FAST FULL SCAN     | DMS_DATA_AGG_I | 50000 | 1025K|   498K (2)| 00:00:50 |

|   4 |   TABLE ACCESS BY USER ROWID| DMS_DATA       |     1 |   439 |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

It will work, fast too, data seams to be fine. But is that the truth? Is the rowid during an FFS from the table or from the index an so joinable, since the table has not been accessed.

Here some timings:

FTS: ~1900s

FFS on Index Columns ~65s

FFS combination (example above) ~70s

RANGE SCAN .. currently working but actual i'm at 2400s increasing ...

Rgds,

Daniel
------
Clever!

I wonder whether or not the NOT IN syntax prevents the index from being used. Do you see an index FFS with this query?

select *

from dms_data b

where b.processing_status=2

and b.billing_type in (2,3,4,5)

and b.aggregation_id is null;-----如果这个返回20k，你们range scan IS better

Sincerely,

Ted
Hi Ted,

no the last query also results in an range scan ...

This maybe because it's not the leading column in the index?

Rgds,

Daniel
Feb 12, 2016 6:41PM edited Feb 12, 2016 6:41PM

Well, there must be a reason that the cost-based optimizer (CBO) does not allow the index fast full scan (FFS). There probably exists a corner case, though I can't think of any, off the top of my head. What's telling is that the CBO silently ignored the INDEX_FFS hint, when you specified it, implying it's not possible to use as an access path.

The use of index FFS, versus a full table scan, is covered in Note 70135.1. However, it simply repeats what I've already posted in here from Note 67522.1 - that the index must reference all columns listed in the SELECT list.

Sincerely,