1 2 Previous Next 17 Replies Latest reply on Apr 13, 2012 5:58 PM by John Spencer

    Reg - Index fast full scan

    795356
      Hi Experts/Gurus,
      I have been reading Oracle docs http://docs.oracle.com/cd/B19306_01/server.102/b14211/optimops.htm#i52044, to know the difference among index range scans, index full scan and index fast full scan. Below is the description for "Fast Full Index Scans"

      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.

      So the document says, for the optimizer to choose index FFS, " at least one column in the index key has the NOT NULL constraint", but what I see from below test case is in complete contrast to what I read.

      I created a table with no "NOT NULL" constraints.
      SQL> create table r_dummy(a number,b varchar2(10));
      
      Table created.
      
      SQL> insert into r_dummy select level,'hi' from dual connect by level<=20;
      
      20 rows created.
      
      SQL> commit;
      
      Commit complete.
      
      SQL> exec dbms_stats.gather_table_stats('HLODS','R_DUMMY');
      
      PL/SQL procedure successfully completed.
      
      SQL> select * from r_dummy where a <= 10;
      
               A B
      ---------- ----------
               1 hi
               2 hi
               3 hi
               4 hi
               5 hi
               6 hi
               7 hi
               8 hi
               9 hi
              10 hi
      
      10 rows selected.
      
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 2533004807
      
      -----------------------------------------------------------------------------
      | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
      -----------------------------------------------------------------------------
      |   0 | SELECT STATEMENT  |         |    10 |    60 |     3   (0)| 00:00:01 |
      |*  1 |  TABLE ACCESS FULL| R_DUMMY |    10 |    60 |     3   (0)| 00:00:01 |
      -----------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         1 - filter("A"<=10)
      
      
      Statistics
      ----------------------------------------------------------
                0  recursive calls
                0  db block gets
                8  consistent gets
                0  physical reads
                0  redo size
              329  bytes sent via SQL*Net to client
              238  bytes received via SQL*Net from client
                2  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
               10  rows processed
      As expected its doing "Full Table Scan", now if I'm going to have an index on columns (a,b), If my understanding is correct, as per docs the optimizer shouldn't do a "Index fast full scan" but interestingly it obeys to the index_ffs hint and does a index fast full scan
      SQL> create index r_dummy_idx on r_dummy(a,b);
      
      Index created.
      
      SQL> exec dbms_stats.gather_index_stats('HLODS','R_DUMMY_IDX');
      
      PL/SQL procedure successfully completed.
      
      SQL> select /*+index_ffs(r)*/ * from r_dummy r where a <= 10;
      
               A B
      ---------- ----------
               1 hi
               2 hi
               3 hi
               4 hi
               5 hi
               6 hi
               7 hi
               8 hi
               9 hi
              10 hi
      
      10 rows selected.
      
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 3219236618
      
      ------------------------------------------------------------------------------------
      | Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
      ------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT     |             |    10 |    60 |     2   (0)| 00:00:01 |
      |*  1 |  INDEX FAST FULL SCAN| R_DUMMY_IDX |    10 |    60 |     2   (0)| 00:00:01 |
      ------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         1 - filter("A"<=10)
      
      
      Statistics
      ----------------------------------------------------------
                1  recursive calls
                0  db block gets
                4  consistent gets
                0  physical reads
                0  redo size
              330  bytes sent via SQL*Net to client
              238  bytes received via SQL*Net from client
                2  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
               10  rows processed
      This clearly shows optimizer chooses "INDEX FAST FULL SCAN" even though neither of the indexed columns have "NOT NULL" constraint.

      And there is other statement that speaks about ORDERING results.

      +"It cannot be used to eliminate a sort operation, because the data is not ordered by the index key"+

      it has eliminated sort operation, I see my results to be in ascending order.
      SQL> drop table r_dummy;
      
      Table dropped.
      
      SQL> create table r_Dummy(a number);
      
      Table created.
      
      SQL> insert into r_dummy values(10);
      
      1 row created.
      
      SQL> insert into r_dummy values(3);
      
      1 row created.
      
      SQL> insert into r_dummy values(1);
      
      1 row created.
      
      SQL> insert into r_dummy values(9);
      
      1 row created.
      
      SQL> insert into r_dummy values(5);
      
      1 row created.
      
      SQL> insert into r_dummy values(4);
      
      1 row created.
      
      SQL> insert into r_dummy values(2);
      
      1 row created.
      
      SQL> insert into r_dummy values(6);
      
      1 row created.
      
      SQL> insert into r_dummy values(7);
      
      1 row created.
      
      SQL> commit;
      
      Commit complete.
      
      SQL> select * from r_dummy;
      
               A
      ----------
              10
               3
               1
               9
               5
               4
               2
               6
               7
      
      9 rows selected.
      
      SQL> create index r_dummy_idx on r_dummy(a);
      
      Index created.
      
      SQL> set autotrace on;
      SQL> exec dbms_stats.gather_table_stats('HLODS','R_DUMMY');
      
      PL/SQL procedure successfully completed.
      
      SQL> exec dbms_stats.gather_index_stats('HLODS','R_DUMMY_IDX');
      
      PL/SQL procedure successfully completed.
      Below you can see the results in ascending order.
      SQL> select /*+index_ffs(r)*/ * from r_dummy r where a<=10;
      
               A
      ----------
               1
               2
               3
               4
               5
               6
               7
               9
              10
      
      9 rows selected.
      
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 3219236618
      
      ------------------------------------------------------------------------------------
      | Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
      ------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT     |             |     9 |    27 |     2   (0)| 00:00:01 |
      |*  1 |  INDEX FAST FULL SCAN| R_DUMMY_IDX |     9 |    27 |     2   (0)| 00:00:01 |
      ------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         1 - filter("A"<=10)
      
      
      Statistics
      ----------------------------------------------------------
                1  recursive calls
                0  db block gets
                4  consistent gets
                0  physical reads
                0  redo size
              291  bytes sent via SQL*Net to client
              238  bytes received via SQL*Net from client
                2  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
                9  rows processed
      
      SQL>
      I don't really understand whether docs are wrong or I'm reading them wrong. Please help/correct me if my understanding is poor.
        • 1. Re: Reg - Index fast full scan
          Nicosa-Oracle
          Hi,
          RGH wrote:
          Below you can see the results in ascending order.
          SQL> select /*+index_ffs(r)*/ * from r_dummy r where a<=10;
          The ascending order in this case is a matter of "luck". Without an explicit order by you can't expect the result to always be ordered.

          Do the same test with an order by clause :
          select /*+index_ffs(r)*/ * from r_dummy r where a<=10 order by a;
          • 2. Re: Reg - Index fast full scan
            795356
            Hi Nicosa,
            Thanks for your reply, I appreciate your quick response. I don't think it is just luck. The order is guaranteed when everything you are trying to fetch is from INDEX only i.e without touching the table.

            Please read about 'INDEX RANGE SCAN' here http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm#sthref983
            • 3. Re: Reg - Index fast full scan
              Paul  Horth
              Why the heck would it bother doing an index FFs when a single block read of the table would be just as good.

              Your example is too artificial. Try putting in more columns that just the ones your indexing anyway and a few thousand rows. Then you might see something.

              it has eliminated sort operation, I see my results to be in ascending order.

              If you don't put in an order by you CANNOT rely on the order: you just got lucky.
              • 4. Re: Reg - Index fast full scan
                Paul  Horth
                The order is guaranteed when everything you are trying to fetch is from INDEX only i.e without touching the table.

                No, no, no! Please read my previous post. I can't see anything in the docs that say you don't have to use an ORDER BY.

                If you use an ORDER BY and the optimiser does a range scan it MAY decide to eliminate the sort as it is getting rows
                in key order. It doesn't mean you can leave out the order by though.

                Edited by: Paul Horth on 13-Apr-2012 07:48
                • 5. Re: Reg - Index fast full scan
                  Richard Martens
                  I must agree with Paul.

                  Ordering is BY DEFINITION not relevant within an Oracle Database.

                  Only an ORDER BY clause will give you reliable ordering.

                  Regards,
                  Richard
                  -----
                  blog: http://blog.warp11.nl
                  twitter: @rhjmartens
                  If this question is answered, please mark the thread as closed and assign points where earned..
                  • 6. Re: Reg - Index fast full scan
                    635471
                    RGH wrote:
                    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.

                    So the document says, for the optimizer to choose index FFS, " at least one column in the index key has the NOT NULL constraint", but what I see from below test case is in complete contrast to what I read.
                    It would be more accurate to say that the FFIS can be used when the optimizer knows that the index contains all of the information required to answer the query -- in your example your query specifically excluded the possibility that rows where A is null would be required as part of the result set, so the absence on a NOT NULL constraint was not relevant.


                    "Eliminating a sort operation" means that if you have specified "order by A", is a separate sort operation required in order to supply ordered data? An index range scan can do this because the data can be accessed in an order that matches the requested order, but an index FFS cannot because the order in which index blocks are read is not logically ordered in either ascending or descending value order.
                    1 person found this helpful
                    • 7. Re: Reg - Index fast full scan
                      795356
                      Hi Paul,
                      I'm talking about this particular line "*Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted (in ascending order) by the ROWIDs.*" from the "INDEX RANGE SCAN" definition here

                      http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm#sthref983


                      And coming back to your words 'lucky', lets see how lucky I'm now
                      SQL> drop table r_dummy;
                      
                      Table dropped.
                      
                      SQL> create table r_dummy(a number);
                      
                      Table created.
                      
                      SQL> insert into r_dummy values(10);
                      
                      1 row created.
                      
                      SQL> insert into r_dummy values(1);
                      
                      1 row created.
                      
                      SQL> insert into r_dummy values(9);
                      
                      1 row created.
                      
                      SQL> insert into r_dummy values(8);
                      
                      1 row created.
                      
                      SQL> insert into r_dummy values(2);
                      
                      1 row created.
                      
                      SQL> insert into r_dummy values(4);
                      
                      1 row created.
                      
                      SQL> insert into r_dummy values(7);
                      
                      1 row created.
                      
                      SQL> insert into r_dummy values(3);
                      
                      1 row created.
                      
                      SQL> insert into r_dummy values(5);
                      
                      1 row created.
                      
                      SQL> commit;
                      
                      Commit complete.
                      As you can see, I had inserted data 'not in any particular order'.
                      SQL> create index r_Dummy_idx on r_dummy(a);
                      
                      Index created.
                      
                      SQL> exec dbms_stats.gather_table_stats('HLODS','R_DUMMY');
                      
                      PL/SQL procedure successfully completed.
                      
                      SQL> exec dbms_stats.gather_index_stats('HLODS','R_DUMMY_IDX');
                      
                      PL/SQL procedure successfully completed.
                      
                      SQL> set autotrace on;
                      
                      SQL> select * from r_dummy where a<= 10;
                      
                               A
                      ----------
                               1
                               2
                               3
                               4
                               5
                               7
                               8
                               9
                              10
                      
                      9 rows selected.
                      
                      
                      Execution Plan
                      ----------------------------------------------------------
                      Plan hash value: 3689750501
                      
                      --------------------------------------------------------------------------------
                      | Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
                      --------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT |             |     9 |    27 |     1   (0)| 00:00:01 |
                      |*  1 |  INDEX RANGE SCAN| R_DUMMY_IDX |     9 |    27 |     1   (0)| 00:00:01 |
                      --------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                      
                         1 - access("A"<=10)
                      
                      
                      Statistics
                      ----------------------------------------------------------
                                1  recursive calls
                                0  db block gets
                                2  consistent gets
                                0  physical reads
                                0  redo size
                              286  bytes sent via SQL*Net to client
                              238  bytes received via SQL*Net from client
                                2  SQL*Net roundtrips to/from client
                                0  sorts (memory)
                                0  sorts (disk)
                                9  rows processed
                      Nice I'm still lucky, everything has been in ORDER. Let's see what would happen if ignore my INDEX
                      SQL> select /*+no_index(r)*/ * from r_dummy r where a<= 10;
                      
                               A
                      ----------
                              10
                               1
                               9
                               8
                               2
                               4
                               7
                               3
                               5
                      
                      9 rows selected.
                      I hope I got enough justification on this, as the docs say " order is guaranteed when everything you are trying to fetch is from INDEX only i.e without touching the table."
                      • 8. Re: Reg - Index fast full scan
                        795356
                        Hi Rima,
                        I too agree that only 'ORDER BY' could preserve the order in which results to be displayed. But, here it is a different context, I'm not touching the 'TABLE', trying to fetch everything from 'INDEX' ( index is structure that stores data in asc order, unlike heap tables). This is a different scenario.
                        • 9. Re: Reg - Index fast full scan
                          Nicosa-Oracle
                          RGH wrote:
                          Nice I'm still lucky, everything has been in ORDER.
                          Yeah, and I just flipped a coin 3 times and it always came head up, so we can conclude this coin will always come head up if I flip it in the future...

                          This is no proof at all.
                          The ordering without "order by" clause is an old subject.
                          Be sure to understand : not having an order by does not mean the data have to be in random order.
                          But, not having an order by, cannot guarantee that the row will be ordered (even, if it comes to be 99.9999% of the time).

                          Go rely on data to be ordered without order by clause, and your application will someday or another have a bug. +(which, in fact, won't be one, as the app was designed this way !)+

                          Also, be sure to understand that Fast Full Scan is totally different than Index Range Scan in the way blocks composing the index are visited.

                          Just for fun, let's see my luck on this friday 13th :
                          Scott@my11g SQL>create table foo (id integer);
                          
                          Table created.
                          
                          Scott@my11g SQL>
                          Scott@my11g SQL>insert into foo
                            2  select level from dual connect by level<=10;
                          
                          10 rows created.
                          
                          Scott@my11g SQL>create index foo_idx
                            2  on foo(id)
                            3  global partition by hash(id)
                            4  partitions 4;
                          
                          Index created.
                          
                          Scott@my11g SQL>begin  
                            2  dbms_stats.gather_table_stats(
                            3  ownname=>user,
                            4  tabname=>'FOO',
                            5  method_opt=>'for all columns size 1',
                            6  cascade=>true);
                            7  end;
                            8  /
                          
                          PL/SQL procedure successfully completed.
                          
                          Scott@my11g SQL>set autotrace on explain
                          Scott@my11g SQL>select /*+index_ffs(foo)*/ id from foo where id<=10;
                          
                                  ID
                          ----------
                                   6
                                   9
                                  10
                                   2
                                   5
                                   8
                                   1
                                   3
                                   4
                                   7
                          
                          10 rows selected.
                          
                          
                          Execution Plan
                          ----------------------------------------------------------
                          Plan hash value: 3808021130
                          
                          -------------------------------------------------------------------------------------------------
                          | Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
                          -------------------------------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT      |         |    10 |    30 |     3   (0)| 00:00:01 |       |       |
                          |   1 |  PARTITION HASH ALL   |         |    10 |    30 |     3   (0)| 00:00:01 |     1 |     4 |
                          |*  2 |   INDEX FAST FULL SCAN| FOO_IDX |    10 |    30 |     3   (0)| 00:00:01 |     1 |     4 |
                          -------------------------------------------------------------------------------------------------
                          
                          Predicate Information (identified by operation id):
                          ---------------------------------------------------
                          
                             2 - filter("ID"<=10)
                          I guess I won't win at the lottery today.
                          ;-)
                          1 person found this helpful
                          • 10. Re: Reg - Index fast full scan
                            32685
                            I hope I got enough justification on this, as the docs say " order is guaranteed when everything you are trying to fetch is from INDEX only i.e without touching the table."
                            If you include the ORDER BY you leave the optimiser to decide whether it is safe to rely on the order from the index. If you declare your intention in the SQL i.e. put the order by, you're not relying on any mechanism which may or may not be there in future. It may well be that you or someone else wants to change your indexing strategy in days/weeks/months/years to come and by relying on this behaviour you're relying on the execution plan which can change over time and the index which could also change. And as already pointed out by David Aldridge, Index FFS uses multiblock IO to read the blocks in whichever order they appear, not by traversing the index structure in and particular order.

                            HTH

                            David
                            • 11. Re: Reg - Index fast full scan
                              rp0428
                              >
                              I'm talking about this particular line "Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted (in ascending order) by the ROWIDs." from the "INDEX RANGE SCAN" definition here
                              . . .
                              I hope I got enough justification on this, as the docs say " order is guaranteed when everything you are trying to fetch is from INDEX only i.e without touching the table."
                              >
                              Nice try but you can't slip that one past us.

                              This last example uses an INDEX RANGE SCAN
                              INDEX RANGE SCAN| R_DUMMY_IDX |     9 |    27 |     1   (0)| 00:00:01 |
                              And the documentation is correct. For an index range scan data is returned in ascending order.

                              But that is a DIFFERENT example than the first one where the term 'lucky' was used.
                              That example used an INDEX FAST FULL SCAN
                              |*  1 |  INDEX FAST FULL SCAN| R_DUMMY_IDX |     9 |    27 |     2   (0)| 00:00:01 |
                              You are trying to pull a switcheroo and use a quote about range scans to explain a fast full scan result and that isn't going to work.
                              For range scans you will get an ordered result but they don't use multi-block reads. A multi-block read reads mutiple blocks at a time and there is no guarantee that the records in the blocks will be in any particular order.

                              As to your question about fast full index scans
                              >
                              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
                              . . .
                              If my understanding is correct, as per docs the optimizer shouldn't do a "Index fast full scan" but interestingly it obeys to the index_ffs hint and does a index fast full scan
                              >
                              The documentation is telling you what Oracle will do if that situation. In your use case you TOLD Oracle to use an INDEX FAST FULL SCAN so it did. That doesn't mean if would have used that scan on it's own; did you test to see what Oracle did if you did NOT provide the hint?

                              Also, since your predicate does not involve NULL values for column a (a <= 10) a FAST FULL index scan is a valid access path and so Oracle will obey your hint and use it.

                              This 'hint' issue came up in a thread in Feb and I ask Jonathan Lewis about it. See his reply dated Feb 19, 2012 2:26am in this thread
                              Re: BITMAP Index and NOT NULL
                              >
                              rp0428 wrote:

                              Jonathan - admitting a little off-topic but could you clarify whether hints are always 'hints' or whether there are some hints that are really mandates and required Oracle to take them. That question rears up in a lot of unrelated threads.

                              Unless you hit a piece of optimizer code with a bug, any Oracle hint must be obeyed if it has been expressed correctly, and is in context. Unfortunately the documentation is poor and people often fail to understand how hints work.

                              There are 10 items on my blog listed under the "ignoring hints" category - all of them pointing out that Oracle doesn't just "ignore" hints.The first one is something I wrote over 5 years ago: http://jonathanlewis.wordpress.com/2006/10/28/taking-a-hint/
                              >

                              So I think that explains your initial question and example.
                              • 12. Re: Reg - Index fast full scan
                                John Spencer
                                Your first example where you forced an INDEX FAST FULL SCAN came back in sorted order because a) it was a newly created index and b) it was very small, note that there were ony 4 consistent gets. Given those special conditions, the index blocks would be physically stored in a single extent, and in the "correct" order to return the rows in order.

                                Your second example uses an index range scan. That means that Oracle looked at the index blocks in logical order, not in physical order.

                                However, and INDEX FAST FULL SCAN does not guarantee order, and you do not even have to resort to partitioned indexes as Nicosa did. All you need is a larger index on a table with a lot of DML. This table form one of my databases has 5,103,320 rows currently, and is frequently updated. Once a month a large number but not all of the older records are moved to a history table and deleted from the main table. There is an index on the three columns I selected, and both acctno and claimno can be updated.

                                In the interests of full disclosure, the three columns are not even close to unique in the table, so I have removed repeating values from the sample of the output. The first block of rows is from the first couple of hundred rows returned, and the second is form the last hundred or so.
                                SQL> select acctno, claimno, compno
                                  2  from pttrans
                                  3  where acctno < '11120044000';
                                
                                First Block
                                ACCTNO                  CLAIMNO     COMPNO
                                -------------------- ---------- ----------
                                11110010171                   1          1
                                11110010171                   2          1
                                11110010172                   1          1
                                11110010173                              1
                                11110010174                   1          1
                                11110010174                   2          1
                                11110010175                   1          1
                                11110010175                   2          1
                                11110010175                   3          1
                                11110010176                   1          1
                                11110010176                   2          1
                                11110010177                   1          1
                                11110010178                   1          1
                                11110010178                   3          1
                                11110010178                   4          1
                                11110010178                              1
                                11110010180                   1          1
                                11110010180                   2          1
                                11110010181                              1
                                0                                        1
                                11110029980                   1          1
                                11110029980                              1
                                11110029981                   2          1
                                11110029983                   1          1
                                11110029983                   2          1
                                11110029984                   1          1
                                11110029985                   1          1
                                11110029989                   1          1
                                11110029989                   2          1
                                0                                        1
                                11110027855                   1          1
                                
                                Second Block
                                11110022169                   1          1
                                11110022169                              1
                                11110022169P                  1          1
                                11030219910                   1          1
                                11030219924                   1          1
                                11030219938                   1          1
                                11110027633                   1          1
                                11110027638                   1          1
                                11110027638                   2          1
                                11110027638                   2          1
                                11010031561                   1          1
                                11010031563                   1          1
                                11110024478                   1          1
                                121812 rows selected.
                                
                                Execution Plan
                                ----------------------------------------------------------
                                Plan hash value: 3137539327
                                
                                ---------------------------------------------------------------------------------
                                | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
                                ---------------------------------------------------------------------------------
                                |   0 | SELECT STATEMENT     |          |  2405K|    41M| 10410   (2)| 00:02:05 |
                                |*  1 |  INDEX FAST FULL SCAN| PTTRANS1 |  2405K|    41M| 10410   (2)| 00:02:05 |
                                ---------------------------------------------------------------------------------
                                
                                Predicate Information (identified by operation id):
                                ---------------------------------------------------
                                
                                   1 - filter("ACCTNO"<'11120044000')
                                
                                Statistics
                                ----------------------------------------------------------
                                          0  recursive calls
                                          0  db block gets
                                       9481  consistent gets
                                       1395  physical reads
                                          0  redo size
                                    2188316  bytes sent via SQL*Net to client
                                      89789  bytes received via SQL*Net from client
                                       8123  SQL*Net roundtrips to/from client
                                          0  sorts (memory)
                                          0  sorts (disk)
                                Large blocks of rows are returned in order because there happen to be a few index blocks physically contiginous that contain ordered results, but the next set of blocks in physical order can contain entries form almost anywhere in the logical order.

                                John
                                • 13. Re: Reg - Index fast full scan
                                  795356
                                  Hi Nicosa,
                                  First of all, thanks for your elaborated explanation.
                                  Nicosa wrote:
                                  Go rely on data to be ordered without order by clause, and your application will someday or another have a bug. +(which, in fact, won't be one, as the app was designed this way !)+
                                  I don't really do that, as I'm aware that even paralleling the same query may also return rows without order. But was surprised to see my results in an ordered way on NORMAL INDEX ( not partitioned). However I ensure that I use 'order by' whenever I want my results to be ordered.

                                  But still I'm not sure of , how does the lack of 'NOT NULL' constraint column in the index would stop optimizer from doing a index fast full scan
                                  • 14. Re: Reg - Index fast full scan
                                    John Spencer
                                    RGH wrote:
                                    Hi Nicosa,
                                    First of all, thanks for your elaborated explanation.
                                    Nicosa wrote:
                                    Go rely on data to be ordered without order by clause, and your application will someday or another have a bug. +(which, in fact, won't be one, as the app was designed this way !)+
                                    I don't really do that, as I'm aware that even paralleling the same query may also return rows without order. But was surprised to see my results in an ordered way on NORMAL INDEX ( not partitioned). However I ensure that I use 'order by' whenever I want my results to be ordered.

                                    But still I'm not sure of , how does the lack of 'NOT NULL' constraint column in the index would stop optimizer from doing a index fast full scan
                                    Oracle does not index rows where all of the coluumns in the index are null. So, in your first example it could be possible that there was a row wiith both a and b null. Oracle could not return that row using an index fast full scan since it would not be in the index. However, by adding a predicate on one of the indexed columns you are effectively saying column is not null.

                                    In the table I used in my example above, all three columns are nullable. Consider:
                                    SQL> select acctno, count(*) from pttrans
                                      2  group by acctno;
                                    
                                    Execution Plan
                                    ----------------------------------------------------------
                                    Plan hash value: 3294770776
                                    
                                    --------------------------------------------------------------------------------------
                                    | Id  | Operation          | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                                    --------------------------------------------------------------------------------------
                                    |   0 | SELECT STATEMENT   |         |   532K|  6239K|       | 48143   (2)| 00:09:38 |
                                    |   1 |  HASH GROUP BY     |         |   532K|  6239K|   107M| 48143   (2)| 00:09:38 |
                                    |   2 |   TABLE ACCESS FULL| PTTRANS |  5081K|    58M|       | 37895   (1)| 00:07:35 |
                                    --------------------------------------------------------------------------------------
                                    
                                    SQL> select acctno, count(*) from pttrans
                                      2  where acctno is not null
                                      3  group by acctno;
                                    
                                    Execution Plan
                                    ----------------------------------------------------------
                                    Plan hash value: 3737827862
                                    
                                    ------------------------------------------------------------------------------------------
                                    | Id  | Operation             | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                                    ------------------------------------------------------------------------------------------
                                    |   0 | SELECT STATEMENT      |          |   532K|  6239K|       | 20641   (4)| 00:04:08 |
                                    |   1 |  HASH GROUP BY        |          |   532K|  6239K|   107M| 20641   (4)| 00:04:08 |
                                    |*  2 |   INDEX FAST FULL SCAN| PTTRANS1 |  5081K|    58M|       | 10393   (2)| 00:02:05 |
                                    ------------------------------------------------------------------------------------------
                                    
                                    Predicate Information (identified by operation id):
                                    ---------------------------------------------------
                                    
                                       2 - filter("ACCTNO" IS NOT NULL)
                                    The first query needs to consider null acctno in order to get the correct count but the second, because I am explicitly asking for not null can use the index.

                                    John
                                    1 2 Previous Next