13 Replies Latest reply: Mar 14, 2012 5:41 AM by Rainer Stenzel RSS

    Full Index Scan and Fast Full Index Scans

    ahb72
      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
          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
            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
              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
                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
                  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
                    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
                      Excellent, makes a lot more sense than what i had supposed :)

                      Thanks Jonathan!
                      • 8. Re: Full Index Scan and Fast Full Index Scans
                        ahb72
                        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
                          Rainer Stenzel
                          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
                            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
                              Rainer Stenzel
                              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
                                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
                                  Rainer Stenzel
                                  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