What is Index?
An index is a performance-tuning method of allowing faster retrieval of
records. An index creates an entry for each value that appears in the indexed
columns. By default, Oracle creates B-tree indexes.
Oracle's two major index types are Bitmap indexes and B-Tree indexes. B-Tree
indexes are the regular type that OLTP systems make much use of, and bitmap
indexes are a highly compressed index type that tends to be used primarily for
data warehouses.
What is Bitmap Index?
A bitmap index is an indexing method that can provide both performance
benefits and storage savings. Bitmap indexes are particularly useful for data
warehousing environments because data is usually updated less frequently and ad
hoc queries are more common.
Bit-mapped indexes are best for “low-cardinality” data (such as a column to
indicate a person’s gender, which contains only two possible values: MALE and
FEMALE).
Characteristic of Bitmap Indexes:
01. For columns with very few
unique values (low cardinality)
02. Tables that have no or
little insert/update are good candidates (static data in warehouse)
03. Stream of bits: each bit
relates to a column value in a single row of table.
What is B-Tree Index?
B-tree indexes are usually created on columns containing the most unique
values. It is an ordered set of entries in which each entry a search key value
and a pointer to a specific row with that value. When a server finds a search
key value matching a constraint, the pointer is used to fetch the row.
Difference between B-Tree & Bitmap Index
No comments:
Post a Comment