
A b-Tree index is a data structure in the form of a tree - no surprises there - but it is a tree of database blocks, not rows. Imagine the leaf blocks of the index as the pages of a phone book.
Each page in the book (leaf block in the index) contains many entries, which consist of a name (indexed column value) and an address (ROWID) that tells you the physical location of the telephone (row in the table).
The names on each page are sorted, and the pages - when sorted correctly - contain a complete sorted list of every name and address
A sorted list in a phone book is fine for humans, because we have mastered "the flick" - the ability to fan through the book looking for the page that will contain our target without reading the entire page. When we flick through the phone book, we are just reading the first name on each page, which is usually in a larger font in the page header. Oracle cannot read a single name (row) and ignore the reset of the page (block); it needs to read the entire block.
If we had no thumbs, we may find it convenient to create a separate ordered list containing the first name on each page of the phone book along with the page number. This is how the branch-blocks of an index work; a reduced list that contains the first row of each block plus the address of that block. In a large phone book, this reduced list containing one entry per page will still cover many pages, so the process is repeated, creating the next level up in the index, and so on until we are left with a single page: the root of the tree.
Following are the type of index scans I know
1. Index Unique Scan
This happens on a unique index and when we need to pick single record. And this happens when ever there is a "=" operation in the where clause.
2. Index Range Scan
This happens when we select a range of records. By giving > or < like that.
3. Index fast full scan
This happen when oracle does not have to go to the table to fetch the data and all the required information is available in the index itself.
Bitmap indexes can provide considerable storage savings over the use of B-tree indexes. In databases containing only B-tree indexes, you must anticipate the columns that are commonly accessed together in a single query, and create a composite B-tree index on these columns.
Not only would this B-tree index require a large amount of space, it would also be ordered. That is, a B-tree index on (marital_status, region, gender) is useless for queries that only access region and gender. To completely index the database, you must create indexes on the other permutations of these columns. For the simple case of three low-cardinality columns, there are six possible composite B-tree indexes. You must consider the trade-offs between disk space and performance needs when determining which composite B-tree indexes to create.
Bitmap indexes solve this dilemma. Bitmap indexes can be efficiently combined during query execution, so three small single-column bitmap indexes can do the job of six three-column B-tree indexes.
Bitmap indexes are much more efficient than B-tree indexes, especially in data warehousing environments. Bitmap indexes are created not only for efficient space usage but also for efficient execution, and the latter is somewhat more important.
Do not create bitmap indexes on unique key columns. However, for columns where each value is repeated hundreds or thousands of times, a bitmap index typically is less than 25% of the size of a regular B-tree index. The bitmaps themselves are stored in compressed format.
Simply comparing the relative sizes of B-tree and bitmap indexes is not an accurate measure of effectiveness, however. Because of their different performance characteristics, keep B-tree indexes on high-cardinality columns, while creating bitmap indexes on low-cardinality columns.