Skip to Main Content
  • Questions
  • Difference between Full Index Scans and Fast Full Index Scans

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Gopal.

Asked: March 16, 2011 - 10:38 am UTC

Answered by: Tom Kyte - Last updated: August 08, 2013 - 5:15 pm UTC

Category: Database - Version: 10.2

Viewed 100K+ times! This question is

You Asked

Hi Tom,

What is the difference between Fast Index Scan and Fast Full Index Scan ?

As per oracle documentation,

" A full scan is available if a predicate references one of the columns in the index. The predicate does not need to be an index driver. A full scan is also available when there is no predicate, if both the following conditions are met:
•All of the columns in the table referenced in the query are included in the index.
•At least one of the index columns is not null.
A full scan can be used to eliminate a sort operation, because the data is ordered by the index key. It reads the blocks singly.


Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the
index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.
You can specify fast full index scans with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint. Fast full index scans cannot be performed against bitmap indexes.A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan ".

I am not clear about this.Could you please clarify.


Regards;
Gopal



and we said...

An index fast full scan reads the ENTIRE index, unsorted, as it exists on disk. It is basically using the index as a "skinny" version of the table. The query in question would only be accessing attributes in the index (we are not using the index as a way to get to the table, we are using the index INSTEAD of the table) We use multiblock IO and read all of the leaf, branch and the root block. We ignore the branch and root blocks and just process the (unordered) data on the leaf blocks.

An index full scan is when we read the index a block at a time - from start to finish. We'll read the root block, navigate down the left hand side of the index (or right if we are doing a descending full scan) and then when we hit the leaf block - we'll read across the entire bottom of the index - a block at a time - in sorted order. We use single block IO, not multiblock IO for this operation.


and at least one column in the
index key has the NOT NULL constraint.


not true. In the following, neither owner nor status is NOT NULL, yet an index fast full scan is performed on an index on status, owner

ops$tkyte%ORA11GR2> create table t as select * from all_objects;

Table created.

ops$tkyte%ORA11GR2> alter table t modify owner null;

Table altered.

ops$tkyte%ORA11GR2> create index t_idx on t(status,owner);

Index created.

ops$tkyte%ORA11GR2> desc t
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 OWNER                                        VARCHAR2(30)
 OBJECT_NAME                         NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                               VARCHAR2(30)
 OBJECT_ID                           NOT NULL NUMBER
 DATA_OBJECT_ID                               NUMBER
 OBJECT_TYPE                                  VARCHAR2(19)
 CREATED                             NOT NULL DATE
 LAST_DDL_TIME                       NOT NULL DATE
 TIMESTAMP                                    VARCHAR2(19)
 STATUS                                       VARCHAR2(7)
 TEMPORARY                                    VARCHAR2(1)
 GENERATED                                    VARCHAR2(1)
 SECONDARY                                    VARCHAR2(1)
 NAMESPACE                           NOT NULL NUMBER
 EDITION_NAME                                 VARCHAR2(30)

ops$tkyte%ORA11GR2> set linesize 1000
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select count(*) from t where owner = 'SCOTT';

Execution Plan
----------------------------------------------------------
Plan hash value: 1058879072

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    17 |    69   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |    17 |            |          |
|*  2 |   INDEX FAST FULL SCAN| T_IDX |    12 |   204 |    69   (2)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"='SCOTT')

Note
-----
   - dynamic sampling used for this statement (level=2)



Fast full index scans cannot be performed against bitmap indexes.
also - not true. In the follow, a bitmap index is fast full scanned.

ops$tkyte%ORA11GR2> create table t as select * from all_objects;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create bitmap index t_idx on t(owner);

Index created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select distinct owner from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1011853946

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       | 71491 |  1186K|       |   405   (1)| 00:00:05 |
|   1 |  HASH UNIQUE                 |       | 71491 |  1186K|  1696K|   405   (1)| 00:00:05 |
|   2 |   BITMAP INDEX FAST FULL SCAN| T_IDX | 71491 |  1186K|       |     7   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

ops$tkyte%ORA11GR2> set autotrace off




.A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan "

That is something that

o maybe be true
o may not be true

"It depends"



and you rated our response

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

not getting same plan

March 17, 2011 - 6:32 am UTC

Reviewer: bmk from Bangalore

Dear Tom,
I did exactly as you did but did get different plan. Instead of index fast full scan it was full table scan.


SQL> select count(*) from t where owner = 'SCOTT';

Execution Plan
----------------------------------------------------------
Plan hash value: 1842905362

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    17 |   276   (2)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   587 |  9979 |   276   (2)| 00:00:04 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(NLSSORT("OWNER",'nls_sort=''BINARY_CI''')=HEXTORAW('73636F
              747400') )

Note
-----
   - dynamic sampling used for this statement (level=2)


One more interesting thing is that though there are only 18 rows for SCOTT in the plan it is showing 587 rows.


SQL> select count(*) from t where owner='SCOTT';

  COUNT(*)
----------
        18

Why there is such difference? I am using 11gR2.

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Solaris: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Tom Kyte

Followup  

March 17, 2011 - 8:38 am UTC

look at your filter, you have different NLS settings that are preventing your index from being used.


Thank you very much

March 18, 2011 - 5:08 am UTC

Reviewer: Gopal Biswal

Hi Tom, Thank you very much for the clarification!

March 18, 2011 - 5:34 am UTC

Reviewer: Gopal Biswal

As you mentioned and demonstrated that the below statement is not true

" and at least one column in the index key has the NOT NULL constraint."

But I could see a discussion here http://forums.oracle.com/forums/message.jspa?messageID=2389020#2389020 mentioning that
the column should be NOT NULL. Am I missing something. Thanks for your time.

Regards;
Gopal

Tom Kyte

Followup  

March 18, 2011 - 8:10 am UTC


I wanna performe an index full scan on the table instead of a full table scan although that a full table sacn is likely to be faster than an index full scan.

So, i wrote down a following sql statement
SELECT /*+ INDEX(t XPKGROUP_CUSTOMER)*/t.* FROM group_customer t;



that is the beginning of that discussion. In that case - the index XPKGROUP_CUSTOMER must have at least one NOT NULL attribute because every single row in T is going to be counted and if there is a row in T with all of the indexed attributes "NULL" - it won't appear in the index. The index does not point to EVERY single row in the table.


My counter example demonstrated an index fast full scan - where by we used the smaller index instead of the table - to find all of the rows in the table SUCH THAT "filter("OWNER"='SCOTT')". Now, if OWNER = 'SCOTT', then owner is known to be NOT NULL for the set of rows of interest - therefore any index that contained OWNER would be good enough to fast full scan to count those rows.



Understand the concepts here:

a b*tree index on a set of attributes that are all NULLABLE does not point to every row in the table (does not HAVE to point to every row in the table is more accurate).

if you have to access EVERY ROW in the table, you cannot use that index.

if you have to access a subset of rows in the table, and the subset is defined as a predicate on at least one of the indexed columns AND that predicate is not of the form "column is null", then we CAN use the index to find those rows - since we know now that that attribute is NOT NULL and hence would appear in the index.

March 18, 2011 - 8:57 am UTC

Reviewer: Gopal Biswal

Hi Tom,

I understood it now. Thank you very much!

Regards;
Gopal




Expecting Index Full Scan but got INDEX_FFS

January 04, 2012 - 12:42 pm UTC

Reviewer: A reader

I was trying to reproduce your example earlier in this thread. I was expecting an index full scan, but instead got index fast full scan. Can you please explain why it's not using index_FFS?

SQL> CREATE TABLE t AS SELECT * FROM all_objects;

Table created.

SQL>
SQL> CREATE INDEX t_idx ON t(status, owner);

Index created.

SQL>
SQL> set autotrace traceonly explain
SQL> SELECT COUNT(*) FROM t WHERE owner = 'SCOTT';

Execution Plan
----------------------------------------------------------
Plan hash value: 995313729

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |    17 |     8   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |       |     1 |    17 |            |          |
|*  2 |   INDEX FULL SCAN| T_IDX |     1 |    17 |     8   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='SCOTT')
       filter("OWNER"='SCOTT')

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL> set autotrace off
SQL>
SQL> SELECT * FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Tom Kyte

Followup  

January 04, 2012 - 3:31 pm UTC

index full scan is there? where do you see something else?

it would look like this for an index fast full scan:

ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> SELECT /*+ index_ffs( t t_idx ) */ COUNT(*) FROM t WHERE owner = 'SCOTT';

Execution Plan
----------------------------------------------------------
Plan hash value: 1058879072

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    17 |    69   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |    17 |            |          |
|*  2 |   INDEX FAST FULL SCAN| T_IDX |    12 |   204 |    69   (2)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"='SCOTT')


Expecting INDEX_FFS but got Index Full Scan

January 04, 2012 - 4:04 pm UTC

Reviewer: A reader

Sorry I meant to say "expecting INDEX_FFS but got index full scan."

So without using the INDEX_FFS hint, under what conditions would Oracle use an INDEX_FFS? You mentioned that using INDEX_FFS is equivalent to using a skinnier version of the table. So I'd expect Oracle would use a IDNEX_FFS for the following SQL, but it uses an index full scan instead.


SQL> SELECT status, owner FROM t;

Execution Plan
----------------------------------------------------------
Plan hash value: 2277287974

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |  7399 |   101K|     6   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | T_IDX |  7399 |   101K|     6   (0)| 00:00:01 |
--------------------------------------------------------------------------


Tom Kyte

Followup  

January 04, 2012 - 5:49 pm UTC

interestingly, I get the FFS right out of the box:

ops$tkyte%ORA11GR2> CREATE TABLE t AS SELECT * FROM all_objects;

Table created.

ops$tkyte%ORA11GR2> CREATE INDEX t_idx ON t(status, owner);

Index created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> SELECT COUNT(*) FROM t WHERE owner = 'SCOTT';

Execution Plan
----------------------------------------------------------
Plan hash value: 1058879072

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    17 |    69   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |    17 |            |          |
|*  2 |   INDEX FAST FULL SCAN| T_IDX |    12 |   204 |    69   (2)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"='SCOTT')

Note
-----
   - dynamic sampling used for this statement (level=2)

ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> SELECT COUNT(*) FROM t WHERE owner = 'SCOTT';

  COUNT(*)
----------
         4

ops$tkyte%ORA11GR2> select * from table( dbms_xplan.display_cursor() );

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1ydhgvzv0z1w9, child number 0
-------------------------------------
SELECT COUNT(*) FROM t WHERE owner = 'SCOTT'

Plan hash value: 1058879072

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |       |       |    69 (100)|          |
|   1 |  SORT AGGREGATE       |       |     1 |    17 |            |          |
|*  2 |   INDEX FAST FULL SCAN| T_IDX |    12 |   204 |    69   (2)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"='SCOTT')

Note
-----
   - dynamic sampling used for this statement (level=2)


23 rows selected.




what is the output of:

select name || '='|| value from v$parameter where isdefault='FALSE';



on your system? that cost looks really really low.

January 05, 2012 - 10:09 am UTC

Reviewer: A reader

SQL> select name || '='|| value from v$parameter where isdefault='FALSE';

NAME||'='||VALUE
-----------------------------------------------------------------------------------------------------------------------------------
processes=1000
sessions=1524
timed_statistics=TRUE
nls_length_semantics=CHAR
disk_asynch_io=FALSE
memory_target=3221225472
control_files=C:\APP\ADMINISTRATOR\ORADATA\PROTECT\CONTROL01.CTL, C:\APP\ADMINISTRATOR\ORADATA\PROTECT\CONTROL02.CTL, C:\APP\ADMINI
STRATOR\ORADATA\PROTECT\CONTROL03.CTL

db_block_size=8192
compatible=11.2.0.0.0
db_file_multiblock_read_count=16
fast_start_mttr_target=0
undo_management=AUTO
undo_tablespace=UNDOTBS1
undo_retention=10800
remote_login_passwordfile=EXCLUSIVE
db_domain=
job_queue_processes=10
session_max_open_files=20
db_name=protect
open_cursors=300
star_transformation_enabled=FALSE
optimizer_index_cost_adj=25
optimizer_index_caching=90
query_rewrite_enabled=FALSE
aq_tm_processes=1

25 rows selected.

Tom Kyte

Followup  

January 05, 2012 - 10:21 am UTC

shame on you:

optimizer_index_cost_adj=25
optimizer_index_caching=90


that is why, you made single block IO really, artificially cheap.

it'll make it so you cannot really ask me questions here - you are unique, nothing will work the same for you as it does for 99.9999% of the rest of the world.

I would not permit the use of these:

nls_length_semantics=CHAR
db_file_multiblock_read_count=16
optimizer_index_cost_adj=25
optimizer_index_caching=90



nls_length - set it at the session level when you really really understand and know that you want it. If you have this set to char - and install any 3rd party code - you are likely unsupportable by that vendor.

db_file_multiblock_read_count should never be set since version 10g.

optimizer_index_* should be deprecated in favor of system statistics - they should not be set.

January 05, 2012 - 6:33 pm UTC

Reviewer: A reader

"optimizer_index_* should be deprecated in favor of system statistics - they should not be set."

As of what version does this statement apply? On p. 311 of "Effective Oracle by Design, you stated that

"The settings of OPTIMIZER_INDEX_CACHING= 90 and OPTIMIZER_INDEX_COST_ADJ = 25. These are typically appropriate for many transactoinal/OLTP systems."
Tom Kyte

Followup  

January 10, 2012 - 9:08 pm UTC

as of about 10g - maybe even 9ir2 (that was the beginning of system statistics)

Effective Oracle by Design was written when 9ir2 was production, prior to 10g R1 hitting the streets.

NLS_LENGTH

January 05, 2012 - 6:36 pm UTC

Reviewer: A reader

Can you please elaborate on this statement? Thanks.

"nls_length - set it at the session level when you really really understand and know that you want it. If you have this set to char - and install any 3rd party code - you are likely unsupportable by that vendor."
Tom Kyte

Followup  

January 10, 2012 - 9:10 pm UTC

I don't know what else to say

don't set it in the init.ora

set it in the session that really wants it - the session that meant to use the "varchar2(10 char) instead of just varchar2(10), but didn't"


use it with an alter session, not at the system level.

ops$tkyte%ORA11GR2> alter session set nls_length_semantics = char;

Session altered.

July 09, 2012 - 2:41 am UTC

Reviewer: ying broman

Hi,Tom,
I got exactly same question on index full scan and index fast full scan in the real application.
There r two server,one is prodution server,one is test server.test server is a copy from production server on Feb.
its 20s slower on production server than test server when user runs one same query,test server took only 16s .
I checked the explain sql
production server
SELECT STATEMENT ALL_ROWS Cost: 199.313
        22 SORT ORDER BY Cost: 199.313 Bytes: 101.904.638 Cardinality: 243.791
                 21 FILTER
                          1 TABLE ACCESS FULL TABLE MAXIMO.WORKORDER Cost: 26.050 Bytes: 815.237.522 Cardinality: 1.950.329
                          20 UNION-ALL
                                   8 NESTED LOOPS Cost: 4 Bytes: 414 Cardinality: 6
                                            6 NESTED LOOPS Cost: 2 Bytes: 50 Cardinality: 1
                                                     4 NESTED LOOPS Cost: 2 Bytes: 68 Cardinality: 2
                                                              2 INDEX FAST FULL SCAN INDEX MAXIMO.MAXGROUP_NDX2 Cost: 2 Bytes: 3.135 Cardinality: 209
                                                              3 INDEX UNIQUE SCAN INDEX (UNIQUE) MAXIMO.GROUPUSER_NDX1 Cost: 0 Bytes: 19 Cardinality: 1
                                                     5 INDEX UNIQUE SCAN INDEX (UNIQUE) MAXIMO.SITEAUTH_NDX1 Cost: 0 Bytes: 16 Cardinality: 1
                                            7 INDEX RANGE SCAN INDEX (UNIQUE) MAXIMO.APPAUTH_NDX1 Cost: 2 Bytes: 342 Cardinality: 18
                                   19 FILTER
                                            13 NESTED LOOPS Cost: 2 Bytes: 50 Cardinality: 1
                                                     11 NESTED LOOPS Cost: 2 Bytes: 34 Cardinality: 1
                                                              9 INDEX FAST FULL SCAN INDEX MAXIMO.MAXGROUP_NDX2 Cost: 2 Bytes: 1.305 Cardinality: 87
                                                              10 INDEX UNIQUE SCAN INDEX (UNIQUE) MAXIMO.GROUPUSER_NDX1 Cost: 0 Bytes: 19 Cardinality: 1
                                                     12 INDEX UNIQUE SCAN INDEX (UNIQUE) MAXIMO.SITEAUTH_NDX1 Cost: 0 Bytes: 16 Cardinality: 1
                                            18 NESTED LOOPS Cost: 4 Bytes: 53 Cardinality: 1
                                                     16 NESTED LOOPS Cost: 2 Bytes: 34 Cardinality: 1
                                                              14 INDEX FAST FULL SCAN INDEX MAXIMO.MAXGROUP_NDX2 Cost: 2 Bytes: 1.305 Cardinality: 87
                                                              15 INDEX UNIQUE SCAN INDEX (UNIQUE) MAXIMO.GROUPUSER_NDX1 Cost: 0 Bytes: 19 Cardinality: 1
                                                     17 INDEX RANGE SCAN INDEX (UNIQUE) MAXIMO.APPAUTH_NDX1 Cost: 2 Bytes: 38 Cardinality: 2
-------
test server

SELECT STATEMENT ALL_ROWS Cost: 149.155
        22 SORT ORDER BY Cost: 149.155 Bytes: 74.874.478 Cardinality: 201.818
                 21 FILTER
                          1 TABLE ACCESS FULL TABLE MAXIMO.WORKORDER Cost: 21.289 Bytes: 598.995.824 Cardinality: 1.614.544
                          20 UNION-ALL
                                   8 NESTED LOOPS Cost: 3 Bytes: 350 Cardinality: 5
                                            6 NESTED LOOPS Cost: 1 Bytes: 50 Cardinality: 1
                                                     4 NESTED LOOPS Cost: 1 Bytes: 68 Cardinality: 2
                                                              2 INDEX FULL SCAN INDEX MAXIMO.MAXGROUP_NDX2 Cost: 1 Bytes: 2.985 Cardinality: 199
                                                              3 INDEX UNIQUE SCAN INDEX (UNIQUE) MAXIMO.GROUPUSER_NDX1 Cost: 0 Bytes: 19 Cardinality: 1
                                                     5 INDEX UNIQUE SCAN INDEX (UNIQUE) MAXIMO.SITEAUTH_NDX1 Cost: 0 Bytes: 16 Cardinality: 1
                                            7 INDEX RANGE SCAN INDEX (UNIQUE) MAXIMO.APPAUTH_NDX1 Cost: 2 Bytes: 340 Cardinality: 17
                                   19 FILTER
                                            13 NESTED LOOPS Cost: 1 Bytes: 50 Cardinality: 1
                                                     11 NESTED LOOPS Cost: 1 Bytes: 34 Cardinality: 1
                                                              9 INDEX FULL SCAN INDEX MAXIMO.MAXGROUP_NDX2 Cost: 1 Bytes: 1.215 Cardinality: 81
                                                              10 INDEX UNIQUE SCAN INDEX (UNIQUE) MAXIMO.GROUPUSER_NDX1 Cost: 0 Bytes: 19 Cardinality: 1
                                                     12 INDEX UNIQUE SCAN INDEX (UNIQUE) MAXIMO.SITEAUTH_NDX1 Cost: 0 Bytes: 16 Cardinality: 1
                                            18 NESTED LOOPS Cost: 3 Bytes: 54 Cardinality: 1
                                                     16 NESTED LOOPS Cost: 1 Bytes: 34 Cardinality: 1
                                                              14 INDEX FULL SCAN INDEX MAXIMO.MAXGROUP_NDX2 Cost: 1 Bytes: 1.215 Cardinality: 81
                                                              15 INDEX UNIQUE SCAN INDEX (UNIQUE) MAXIMO.GROUPUSER_NDX1 Cost: 0 Bytes: 19 Cardinality: 1
                                                     17 INDEX RANGE SCAN INDEX (UNIQUE) MAXIMO.APPAUTH_NDX1 Cost: 2 Bytes: 40 Cardinality: 2

can u tell me what caused these?

March 14, 2013 - 10:23 am UTC

Reviewer: A reader


what makes optimizer switch between index full scan and index fast full scan

August 07, 2013 - 3:43 pm UTC

Reviewer: dhairyasheel from india

hello tom,
im facing issue at cust site wherein the only difference in the xplain plan for the same sql id is that for a particular index during good times we see index fast full scan and during bad times we see index full scan. the wait events observed are db file sequential read (good time) and direct path read temp (bad time). db is 11g r2. what i need to know is what factors can make the optimizer switch between these two types of index scans for a particular sql id
Tom Kyte

Followup  

August 08, 2013 - 5:15 pm UTC

care to share the SQL and the schema (just as much of the schema as we need to understand the query), that'll help.


it'll be related to cardinality estimates and the presence or lack thereof of an order by.

New AP rejected (non-guess): favored index only range scan

February 28, 2014 - 4:07 pm UTC

Reviewer: Yuan from Monmouth Junction, NJ

I ran across this message in a 10053 trace output:

New AP rejected (non-guess): favored index only range scan

The access path that was rejected was the one with the lowest cost according to the trace output. If I force its use by using an index hint, the performance is drastically better. Why is the optimizer rejecting the index?

I am on 11.1.0.7. I tried using all rows and first rows optimizer goal with no change in behavior. I did a Google search and this Russian article is the only result I found: http://iusoltsev.wordpress.com/2012/09/21/optimizer_index_cost_adj-cbo-11-1/

The behavior described in the article is the same except that the magic value of optimizer_index_cost_adj for me is different.