0

I think it will give you a better understanding about where I'm coming from by letting you know how I understand how Btree indices work fundamentally. I'm not a DBA and I'm asking this question as a layman with basic understanding of data structures.

The basic idea of an index is that it speeds up searches by skipping significant amount of records when searching through a database.

AFAIK, binary tree data structure, which I presume where Btree indices are based on, helps us to search without scanning the entire database by dividing the data into nodes. For oversimplified example, words that start from A to M are stored in left node, and words that start with N to Z are stored in right node on the first level of the tree. In this case when we search for the word "Jackfruit" it will only search on the left node skipping the right node saving us significant amount of time and IO.

In this sense, how does a bitmap index let us not scan the entire database when searching? If not, how does it speed up searches? Or is it just meant for compression?

enter image description here

Image taken from here

The image above is a conceptual illustration of a bitmap. Using that structure, how does a DB find rows? Does it scan all rows? In binary tree, that fact that you don't have to scan everything is exactly how it helps speed up the search. I can't see any explanation how exactly a DB gets an advantage in searching for rows using bitmap other than the fact that bitmap takes less space.

supertonsky
  • 2,563
  • 6
  • 38
  • 68
  • Btree = balanced tree. When building the btree the tree is kept balanced, even if requireing rearrangement. Btree and Bitmap indexes are suited for specific types of data. i.e. bitmap indexes _can_ be more effective for non-selective data (like gender), whereas btree indexes are usually _ineffective_ for non selective data like gender. From the link provided below: _As the number if distinct values increases, the size of the bitmap increases exponentially, such that an index with 100 values may perform thousands of times faster than a bitmap index on 1,000 distinct column values – Nick.Mc Jul 01 '16 at 04:14
  • Thanks. But what I really want to know is how bitmap are used to find specific data like the way I explained how a binary trees are used to quickly find data. – supertonsky Jul 01 '16 at 06:16
  • Another way to put it, in binary tree data structure, you don't have to scan all the rows one by one to check for a match. You simply have to follow the node that satisfies the condition like I mentioned. In bitmap structure, how does a DB find a match? Does it scan all the rows? How does it prevent scanning all the rows? – supertonsky Jul 01 '16 at 06:23
  • The link below explains in detail. Basically the index is a two dimensional bitmap. (which is only really efficient for low cardinalities). The real power is when you compare 2 bitmaps - you can basically use bitmap algorithms (i.e. AND a bitmap) to overlay and identify matching rows. – Nick.Mc Jul 01 '16 at 07:20
  • @Nick.McDermaid, if you look at the illustration, it tells us how each row is represented in the bitmap. Is AND-ing a bitmap the same as scanning all rows and see what matches? Probably you might want to elaborate on the algorithm. – supertonsky Jul 01 '16 at 08:12

2 Answers2

1

Btree indexes are good for key searching (duplicates allowed, but mainly distinct values in the column, ie. SSN). Bitmap indexes are better in cases when you have a few distinct values like 'sex', 'state', 'color', and so on.

abrittaf
  • 537
  • 5
  • 11
  • I need the explanation on exactly why it is good or better and how exactly it helps speeding up searches. – supertonsky Jul 01 '16 at 03:28
  • Speeding up queries depends mainly on structure and volume. There are circumstances under which index access slows down the entire process. Maybe you should read more about this topic and try to reformulate your question in order to ask in a more precise way. – abrittaf Jul 01 '16 at 03:42
  • If you carefully read my question, I even included an example of explanation on how Btree index helps speed up searches. I'm expecting the same level of detail on how bitmap index helps speed up searches. – supertonsky Jul 01 '16 at 03:54
0

Oracle bitmap indexes are very different from standard b-tree indexes. In bitmap structures, a two-dimensional array is created with one column for every row in the table being indexed. Each column represents a distinct value within the bitmapped index. This two-dimensional array represents each value within the index multiplied by the number of rows in the table.

Please see http://www.dba-oracle.com/oracle_tips_bitmapped_indexes.htm for a more detailed explanation.

abrittaf
  • 537
  • 5
  • 11
  • It only explains the structure of the bitmap but it doesn't explain how it finds the data using the bitmap the same way I explained how to find data using Btree. – supertonsky Jul 01 '16 at 06:12