6 Replies Latest reply: Mar 13, 2014 12:53 AM by NightWing RSS

    Index Fast Full Scan simple question

    NightWing

      The following quote belongs to the Jonathan Lewis (Index Operations | Oracle Scratchpad).

       

      "Index Fast Full Scan: goes to the first block of the segment, and does multi-block reads through the segment, picking up branch and leaf blocks, discarding the branches and using the data in the leaf blocks as if they were skinny tables. Does not return the data in order."

       

      I am just wondering, why Index Fast Full Scan does multi-block read from root to leaf blocks, if it will discards the branches? Doesn't it pointless to get all the segment instead of just leaf blocks? Because only leaf blocks are required for user.

       

      Regards

      Charlie

        • 1. Re: Index Fast Full Scan simple question
          Martin Preiss

          in most cases the number of branch blocks is quite small in comparison to the number of leaf blocks - so the scan does not have to do that much additional work. And the big advantage of the multiblock read is that there is no selective filtering in the first place (though I guess that blocks that are already in the buffer cache will be skipped the same way as table blocks).

          • 2. Re: Index Fast Full Scan simple question
            NightWing

            Martin Preiss wrote:

             

            in most cases the number of branch blocks is quite small in comparison to the number of leaf blocks - so the scan does not have to do that much additional work. And the big advantage of the multiblock read is that there is no selective filtering in the first place (though I guess that blocks that are already in the buffer cache will be skipped the same way as table blocks).

             

            Also, can we say If Oracle only extract the leaf blocks, does this make much more effort than get the whole part? Because when it get the leaf blocks only, it have to find leaf blocks first, then read them. To find them might be costly?

             

            Regards

            Charlie

            • 3. Re: Index Fast Full Scan simple question
              Hemant K Chitale

              Navigating from one leaf block to the next is done in range scans.  Such reads are not multiblock reads.  So, to read the whole index it is faster to do a multiblock read of the whole index segment (and discard branches as they are identified).

               

               

              Hemant K Chitale


              • 4. Re: Index Fast Full Scan simple question
                Richard Foote-Oracle

                NightWing wrote:

                 

                 

                Also, can we say If Oracle only extract the leaf blocks, does this make much more effort than get the whole part? Because when it get the leaf blocks only, it have to find leaf blocks first, then read them. To find them might be costly?

                 

                Hi Charlie

                 

                The method you describe of reading all the leaf blocks is indeed a viable optimizer access path called an Index Full Scan:

                 

                http://docs.oracle.com/cd/E16655_01/server.121/e15858/tgsql_optop.htm#CHDCBBAE

                 

                A block address pointer within the leaf block is used to find the next (or previous) leaf block in the index structure. It's "expensive" in that it can only read these leaf blocks one tiny little database block at a time (and not with large efficient multi-block reads as with the IFFS) and that the next leaf block doesn't necessarily have to be physically sequential to the previous leaf block and could potentially be "anywhere" within the tablespace, perhaps even on a different file on a totally different physical disk.

                 

                So with an IFFS, the cost of reading and ignoring the occasional branch block is far offset with the benefit of being able to perform (perhaps in parallel) much larger, more efficient multi-block reads.

                 

                Cheers

                 

                Richard Foote

                http://richardfoote.wordpress.com/

                • 5. Re: Index Fast Full Scan simple question
                  NightWing

                  Thank you so much Hemant K Chitale.

                  • 6. Re: Index Fast Full Scan simple question
                    NightWing

                    Thanks you so much for your remarkable comment Richard Foote.