This discussion is archived
13 Replies Latest reply: Mar 14, 2012 3:41 AM by 499773 RSS

Full Index Scan and Fast Full Index Scans

ahb72 Newbie
Currently Being Moderated
Hi all,

In the example below, Why INDEX FAST FULL SCAN is more expensive than INDEX FULL SCAN? Shouldn't it be faster ?

Oracle 11g R1
Windows 2003 SP2
create table try ( a number constraint try_pk primary key , b varchar2(5), c date )
 
create index indexb on try ( b );
 
create index indexc on try ( c );
 
insert into try  values ( 1, 'A', to_date('01-01-2010','dd-mm-yyyy')) ; 
 
insert into try  values ( 2, 'B', to_date('01-01-2009','dd-mm-yyyy')) ;
 
insert into try  values ( 3, 'C', to_date('01-01-2008','dd-mm-yyyy')) ;
 
insert into try  values ( 4, 'B', to_date('01-10-2008','dd-mm-yyyy')) ;
 
analyze table try compute statistics;

SQL> ed
Wrote file afiedt.buf

  1  select
  2   b
  3* from try t
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 3500627029

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     4 |     4 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | INDEXB |     4 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL> ed
Wrote file afiedt.buf

  1  select  /*+ index_ffs(t indexb) */
  2   b
  3* from try t
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 958880282

-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |     4 |     4 |     2   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| INDEXB |     4 |     4 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------
  • 1. Re: Full Index Scan and Fast Full Index Scans
    Tubby Guru
    Currently Being Moderated
    Because you have a table with 4 rows (i don't think you're going to find any measurable difference between ANY access path on a single table lookup with 4 rows like you have).

    Because you are using the ANALYZE command which has been obsolete for gathering statistics for 3 major releases of Oracle (use the DBMS_STATS package).
  • 2. Re: Full Index Scan and Fast Full Index Scans
    ahb72 Newbie
    Currently Being Moderated
    Hi Tubby,

    Here we go, a large table analyzed by dbms_stat package. The example gives same result.
    SQL> desc names3
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
    
     ID                                                 NUMBER
     NAME                                               VARCHAR2(50)
     HDATE                                              DATE
     SAL                                                NUMBER
     REGION                                             VARCHAR2(1)
    
    SQL> create index names3i1 on names3(name) ;
    Index created.
    
    SQL> exec dbms_stats.gather_table_stats('HRS','NAMES3') ;
    PL/SQL procedure successfully completed.
    
    SQL> select count(*) from names3 ;
    
      COUNT(*)
    ----------
        200056
    
    SQL>
    SQL> set autotrace traceonly exp
    SQL>
    SQL> select name
      2  from names3
      3  where name='xyz' ;
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1351657106
    
    -----------------------------------------------------------------------------
    | Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |          |     1 |    21 |     3   (0)| 00:00:01 |
    |*  1 |  INDEX RANGE SCAN| NAMES3I1 |     1 |    21 |     3   (0)| 00:00:01 |
    -----------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("NAME"='xyz')
    
    SQL> ed
    Wrote file afiedt.buf
    
      1  select /*+ index_ffs(n names3i1) */
      2  name
      3  from names3 n
      4* where name='xyz'
    SQL> /
    
    
    ---------------------------------------------------------------------------------
    
    | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time|
    
    ---------------------------------------------------------------------------------
    
    |   0 | SELECT STATEMENT     |          |     1 |    21 |   246   (1)| 00:00:03|
    
    |*  1 |  INDEX FAST FULL SCAN| NAMES3I1 |     1 |    21 |   246   (1)| 00:00:03|
    
    ---------------------------------------------------------------------------------
    Edited by: ahb72 on Apr 5, 2010 10:57 PM
  • 3. Re: Full Index Scan and Fast Full Index Scans
    Tubby Guru
    Currently Being Moderated
    ahb72 wrote:
    Hi Tubby,

    Here we go, a large table analyzed by dbms_stat package. The example gives same result.
    No, they do not.

    In your first example you had a INDEX FULL SCAN and a FAST FULL SCAN.

    In this example you have a INDEX RANGE SCAN and a FAST FULL SCAN.
  • 4. Re: Full Index Scan and Fast Full Index Scans
    ahb72 Newbie
    Currently Being Moderated
    Hi Tubby,

    You were right about the last comment.

    I tried to fix the code but couldn't enforce the optimizer to use the index without a predict. When I added a dummy predict, FSS got cheaper cost.

    I don't know why full index scan wasn't used by the optimizer despite the hint. Is it because Full Table Scan cheaper?

    Thanks for the comments.
    SQL> ed
    Wrote file afiedt.buf
    
      1  select /*+ index(n names3i1) */ name
      2* from names3 n
    SQL> /
    
    
    ----------------------------------------------------------------------------
    | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |        |    10 |   210 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| NAMES3 |    10 |   210 |     2   (0)| 00:00:01 |
    ----------------------------------------------------------------------------
    
    SQL>
    SQL>
    SQL> ed
    Wrote file afiedt.buf
    
      1  select /*+ index_ffs(n names3i1) */ name
      2* from names3 n
    SQL> /
    
    ----------------------------------------------------------------------------
    | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |        |    10 |   210 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| NAMES3 |    10 |   210 |     2   (0)| 00:00:01 |
    ----------------------------------------------------------------------------
    
    SQL>
    SQL> ed
    Wrote file afiedt.buf
    
      1  select /*+ index_ffs(n names3i1) */ name
      2  from names3 n
      3* where name=name
    SQL> /
    
    ---------------------------------------------------------------------------------
    
    | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time|
    
    ---------------------------------------------------------------------------------
    
    |   0 | SELECT STATEMENT     |          |    10 |   210 |     2   (0)| 00:00:01|
    
    |*  1 |  INDEX FAST FULL SCAN| NAMES3I1 |    10 |   210 |     2   (0)| 00:00:01|
    
    ---------------------------------------------------------------------------------
    
    SQL>
    SQL> ed
    Wrote file afiedt.buf
    
      1  select /*+ index(n names3i1) */ name
      2  from names3 n
      3* where name=name
    SQL> /
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2237722473
    
    -----------------------------------------------------------------------------
    | Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |          |    10 |   210 |     3   (0)| 00:00:01 |
    |*  1 |  INDEX FULL SCAN | NAMES3I1 |    10 |   210 |     3   (0)| 00:00:01 |
    -----------------------------------------------------------------------------
  • 5. Re: Full Index Scan and Fast Full Index Scans
    Tubby Guru
    Currently Being Moderated
    If you specify a valid hint, Oracle is duty bound to obey it. Only when you have specified an invalid hint, OR Oracle rewrites your query (using view merging for example) can Oracle ignore your hint.

    http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/hintsref.htm#CHDJDIAH

    "
    Specifying one of these hints causes the optimizer to choose the specified access path only if the access path is available based on the existence of an index or cluster and on the syntactic constructs of the SQL statement. If a hint specifies an unavailable access path, then the optimizer ignores it.
    "

    As best i can tell (i couldn't find supporting documentation for my supposition), since you have no WHERE clause on your query, your index hint becomes an invalid access path. So although you could argue index access should be possible given no WHERE clause, i can't imagine many situations in which you'd practically care enough to raise a service request with Oracle about this, though if you do, please update this thread as i'd be interested to see what is said about it :)
  • 6. Re: Full Index Scan and Fast Full Index Scans
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Tubby wrote:
    If you specify a valid hint, Oracle is duty bound to obey it. Only when you have specified an invalid hint, OR Oracle rewrites your query (using view merging for example) can Oracle ignore your hint.

    http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/hintsref.htm#CHDJDIAH

    "
    Specifying one of these hints causes the optimizer to choose the specified access path only if the access path is available based on the existence of an index or cluster and on the syntactic constructs of the SQL statement. If a hint specifies an unavailable access path, then the optimizer ignores it.
    "

    As best i can tell (i couldn't find supporting documentation for my supposition), since you have no WHERE clause on your query, your index hint becomes an invalid access path. So although you could argue index access should be possible given no WHERE clause, i can't imagine many situations in which you'd practically care enough to raise a service request with Oracle about this, though if you do, please update this thread as i'd be interested to see what is said about it :)
    In this case the hint is illegal because obeying it could end up with Oracle returning the wrong results. The indexed column has not been declared as "NOT NULL". See also: Re: Index hint does not work

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
    
    +"I believe in evidence. I believe in observation, measurement, and reasoning, confirmed by independent observers. I'll believe anything, no matter how wild and ridiculous, if there is evidence for it. The wilder and more ridiculous something is, however, the firmer and more solid the evidence will have to be."+
    Isaac Asimov                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 7. Re: Full Index Scan and Fast Full Index Scans
    Tubby Guru
    Currently Being Moderated
    Excellent, makes a lot more sense than what i had supposed :)

    Thanks Jonathan!
  • 8. Re: Full Index Scan and Fast Full Index Scans
    ahb72 Newbie
    Currently Being Moderated
    Thanks a lot Jonathan for the update.
    SQL> ALTER TABLE NAMES3
      2  MODIFY ( NAME VARCHAR2(50) NOT NULL ) ;
    
    Table altered.
    
    SQL> exec dbms_stats.gather_table_stats('HRS','NAMES3') ;
    
    PL/SQL procedure successfully completed.
    
    SQL>
    SQL> set autotrace traceonly exp
    SQL> select /*+ index(n names3i1) */ name from names3 n;
    
    
    -----------------------------------------------------------------------------
    | Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |          |    10 |   210 |     3   (0)| 00:00:01 |
    |   1 |  INDEX FULL SCAN | NAMES3I1 |    10 |   210 |     3   (0)| 00:00:01 |
    -----------------------------------------------------------------------------
    
    SQL> select /*+ index_ffs(n names3i1) */ name from names3 n;
    
    ---------------------------------------------------------------------------------
    
    | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time|
    
    ---------------------------------------------------------------------------------
    
    |   0 | SELECT STATEMENT     |          |    10 |   210 |     2   (0)| 00:00:01|
    
    |   1 |  INDEX FAST FULL SCAN| NAMES3I1 |    10 |   210 |     2   (0)| 00:00:01|
    
    ---------------------------------------------------------------------------------
  • 9. Re: Full Index Scan and Fast Full Index Scans
    499773 Newbie
    Currently Being Moderated
    same problem on 10.2.0.4, costs for INDEX (FULL SCAN) is lower as for INDEX (FAST FULL SCAN). What is the reason ?
    Test case:
    drop table testffs
    create table testffs as 
    select rownum i,cast(rownum as char(100)) ci from dual connect by rownum<500000;
    
    create index itestffs on testffs(i);
    alter table testffs modify (i not null);
    
    select count(i) from testffs
    union all
    select /*+ INDEX_FFS (testffs,itestffs)*/ count(i) from testffs 
    
    SQL> /
    
    Ausführungsplan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=457 Card=2)
       1    0   UNION-ALL
       2    1     SORT (AGGREGATE)   
       3    2       INDEX (FULL SCAN) OF 'ITESTFFS' (INDEX) (Cost=119 Card          =540919)
       4    1     SORT (AGGREGATE)   
       5    4       INDEX (FAST FULL SCAN) OF 'ITESTFFS' (INDEX) (Cost=338           Card=540919)
  • 10. Re: Full Index Scan and Fast Full Index Scans
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user496770 wrote:
    same problem on 10.2.0.4, costs for INDEX (FULL SCAN) is lower as for INDEX (FAST FULL SCAN). What is the reason ?
    Test case:
    ----------------------------------------------------------
    0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=457 Card=2)
    1    0   UNION-ALL
    2    1     SORT (AGGREGATE)   
    3    2       INDEX (FULL SCAN) OF 'ITESTFFS' (INDEX) (Cost=119 Card          =540919)
    4    1     SORT (AGGREGATE)   
    5    4       INDEX (FAST FULL SCAN) OF 'ITESTFFS' (INDEX) (Cost=338           Card=540919)
    I can think of several explanations, but one of the simplest and most likely is that you've set optimizer_index_cost_adj to a value in the region of 10. That's unlikely to be a good choice of value for any version of Oracle, and it's not a parameter you should fiddle with in 10g except in very special circumstances.


    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
    
    +"I believe in evidence. I believe in observation, measurement, and reasoning, confirmed by independent observers. I'll believe anything, no matter how wild and ridiculous, if there is evidence for it. The wilder and more ridiculous something is, however, the firmer and more solid the evidence will have to be."+
    Isaac Asimov                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 11. Re: Full Index Scan and Fast Full Index Scans
    499773 Newbie
    Currently Being Moderated
    Hello Jonathan,
    sorry for stealing your time. You are absolutely right, optimizer_index_cost_adj is low (vendor recommendation), but in this case the INDEX FULL SCAN is really faster than INDEX FAST FULL SCAN, so this setting seems to be appropriate.
    Best reagrds,
    Rainer Stenzel
    Der Mensch sollte sich besser bemühen, selber intelligent zu werden.
    +(ZEIT WISSEN, Interview über künstliche Intelligenz)+
    Stanislaw Lem
  • 12. Re: Full Index Scan and Fast Full Index Scans
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    user496770 wrote:
    sorry for stealing your time. You are absolutely right, optimizer_index_cost_adj is low (vendor recommendation), but in this case the INDEX FULL SCAN is really faster than INDEX FAST FULL SCAN, so this setting seems to be appropriate.
    Vendor recommendation, or vendor demand ? The latter is on of those "special circumstances".

    I'm a little surprised that the index full scan is faster on this test - at 500,000 rows I wouldn't have expected to see any significant difference (although with a small buffer cache you may be seeing some physical reads with the FFS). I copied your test case onto my laptop, and found the opposite (which also surprised me): the FFS regularly took about 0.04 seconds, and the full scan about 0.07.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
    
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 13. Re: Full Index Scan and Fast Full Index Scans
    499773 Newbie
    Currently Being Moderated
    old thread, new aspect/question:
    I was wondering about very different Full Index Scan performances on different servers/storages.
    Shouldn't a Full Index Scan perform much better when
    1) logically adjacent leaf blocks are located adjacently on physical storage (on rather freshly rebuilded indexes ?), so enabling os read ahead capabilities and
    2) Oracle is not using direct IO but utilizing some kind of file system cache/buffer providing read ahead capabilities instead ?
    Any experiences/opinions ?
    Best Regards,
    Rainer Stenzel

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points