This discussion is archived
6 Replies Latest reply: Oct 7, 2012 10:13 AM by Jonathan Lewis RSS

Reg : Index Scans -

ranit B Expert
Currently Being Moderated
Hi Experts,

I was going through the concepts of Index Scan and its various types but couldn't few things. Can anybody please explain me this a little briefly?
What exactly is the Full Index scan ?

I'm referring to the below whitepaper on Explain Plans.
http://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf

Thanks in advance.
Ranit B.
  • 1. Re: Reg : Index Scans -
    damorgan Oracle ACE Director
    Currently Being Moderated
    Read the entire index ... every block.

    Just as a full table scan is read every block in the table.
  • 2. Re: Reg : Index Scans -
    ranit B Expert
    Currently Being Moderated
    Thanks Damorgan.
    Could you please explain all the types in your words? i'm not getting the bookish words properly.

    Ranit B.
  • 3. Re: Reg : Index Scans -
    rp0428 Guru
    Currently Being Moderated
    >
    Could you please explain all the types in your words? i'm not getting the bookish words properly
    >
    Then you should look up the words you are having trouble with.

    That articlel explains the different types pretty clearly. Let's start with 'Full INdex scan'. This is what the paper says:
    >
    Full Index scan - A full index scan does not read every block in the index structure, contrary to what
    its name suggests. An index full scan processes all of the leaf blocks of an index, but only enough of
    the branch blocks to find the first leaf block. It is used when all of the columns necessary to satisfy the
    statement are in the index and it is cheaper than scanning the table. It uses single block IOs. It may be
    used in any of the following situations:

    • An ORDER BY clause has all of the index columns in it and the order is the same as in the
    index (can also contain a subset of the columns in the index).

    • The query requires a sort merge join and all of the columns referenced in the query are in the
    index.

    • Order of the columns referenced in the query matches the order of the leading index columns.

    • A GROUP BY clause is present in the query, and the columns in the GROUP BY clause are
    present in the index.
    >
    You ask Dan to 'explain all the types in your words'. That description is pretty clear. It explains what the index is, what it does and when it might be used. Do you really expect someone to duplicate that much information for each index type when the article already does it?

    You supposedly read the article and read that definition and all you ask was 'What exactly is the Full Index scan' when that is exactly what the article described. You didn't even ask any question about what the article said or indicate any word or phrase that you didn't understand.

    What don't you understand in that description? If there is something specific that you don't understand ask.
  • 4. Re: Reg : Index Scans -
    ranit B Expert
    Currently Being Moderated
    Thanks for such kind words Rp.
    But just check difference between what Damorgan wrote & what you pasted from docs.

    Damorgan says -
    >
    Read the entire index ... every block.

    Just as a full table scan is read every block in the table.
    >

    From docs -
    >
    Full Index scan - A full index scan does not read every block in the index structure, contrary to what
    its name suggests.
    >

    Now what shuld i conclude?
  • 5. Re: Reg : Index Scans -
    rp0428 Guru
    Currently Being Moderated
    First, disavow yourself of any notion that I, or anyone else, is 'picking on you'. All we are doing is trying to reinforce that it is your responsibilty to research your problem and read the relevant documentation.

    And to your credit you original question was EXCELLENT! You found a doc, had a section you didn't understand and ask about it.

    But then when Dan gave an answer you suddenly wanted him to give you his take on every other index type the doc mentioned. Dan can certainly do that if he wants but I doubt if he or anyone else would.

    Then after my reply you again show that you know how to ask INTELLIGENT and thoughtful questions.
    >
    But just check difference between what Damorgan wrote & what you pasted from docs.

    Now what shuld i conclude?
    >
    Now that is a SMART question. So let me show you how, with a little more effort on your part you might have been able to answer your own question.

    You should conclude that there must be a reason for the difference and should ask yourself if there is any additional information that Dan has or that the doc has that might explain the difference.

    Then, since Dan isn't immediately available but the doc is you should start with the doc to see if there is more info.

    And, lo and behold, there is. The very next sentences of the doc (which I posted) that follow the one line you cited say this
    >
    An index full scan processes all of the leaf blocks of an index, but only enough of
    the branch blocks to find the first leaf block. It is used when all of the columns necessary to satisfy the
    statement are in the index and it is cheaper than scanning the table. It uses single block IOs.
    >
    Yep - it processes ALL of the leaf blocks but only processes the branch blocks that are needed to find the first leaf block.

    Interesting. That statement means that if Oracle has the first leaf block of the index it can somehow find and process ALL of the leaf blocks without ever having to read another branch block.

    From that you should conclude that the FIRST leaf block has enough information to point to the next leaf block, and so on.

    And that should make you curious as to just how it does that. Well since the doc said something about BRANCH and LEAF you would naturally conclude that must have something to do with it.

    So if you go to your favorite search engine and search for 'oracle 11g index branch' you will be amazed to find that the second result on the first page is
    '1 Indexes and Index-Organized Tables - Oracle Documentation.

    Then if you go to that link you will see that it is Chap 3 Indexes and Index-Organized Tables of the Database Concepts doc for 11.2

    So then you should conclude that if you search that page for the word 'branch' (same word you used in the search) you might find more info.

    Again - like magic - the first reference to the word 'branch' that you find is the section 'Branch Blocks and Leaf Blocks.

    And that short section, along with Figure 3-1 Internal Structure of a B-tree Index right above it, explains EXACTLY how Oracle can start with the first leaf block and find every leaf block in the index and it tells you EXACTLY what a leaf block contains.
    >
    The leaf blocks contain every indexed data value and a corresponding rowid used to locate the actual row. Each entry is sorted by (key, rowid). Within a leaf block, a key and rowid is linked to its left and right sibling entries. The leaf blocks themselves are also doubly linked. In Figure 3-1 the leftmost leaf block (0-10) is linked to the second leaf block (11-19).
    >
    If you then combine the information in that quote above with the information from the other document (which I pasted in my reply)
    It is used when all of the columns necessary to satisfy the
    statement are in the index and it is cheaper than scanning the table. It uses single block IOs.
    >
    You should now have your answer about
    1. What a full index scan is
    2. When it is used
    3. How it works

    All from Oracle's own documentation with only a minimal effort to find the information.

    So now it is your turn to reread your own question above
    >
    But just check difference between what Damorgan wrote & what you pasted from docs.

    Now what shuld i conclude?
    >
    And post in the forum EXACTLY what you conclude.
  • 6. Re: Reg : Index Scans -
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    ranit B wrote:
    But just check difference between what Damorgan wrote & what you pasted from docs.

    Damorgan says -
    >
    Read the entire index ... every block.

    Just as a full table scan is read every block in the table.
    >

    From docs -
    >
    Full Index scan - A full index scan does not read every block in the index structure, contrary to what
    its name suggests.
    >
    Further to the comments made by rp0428, there is another very strong clue in the documentation that you quoted:
    <blockquote>+"Full Index scan - A full index scan does not read every block in the index structure, contrary to what+
    +its name suggests. "+</blockquote>

    Note how it says: +"does not read every block"+ and +"contrary to what its name suggests"+. The manual tells you that it is easy to come to the wrong conclusion about the function, and tells you exactly what that wrong conclusion might be.

    Lo and behold - the manual is telling you quite explicitly that the claim made by Dan is wrong, and that it's an easy mistake to make. This pre-emptive warning (and there aren't many such warnings in the manuals - we could do with a lot more) tells you that Dan is probably wrong.

    Regards
    Jonathan Lewis

Legend

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