/ / INDEX Concept in Data Warehouse
Data warehouse

INDEX Concept in Data Warehouse

Edit
Index is a database object which stores the index column values separately in ascending or descending order which improves the performance of select operation. At the same time having more indexes on a table which hamper   the  performance of insert operations. Following different types of indexes. 
  • BTREE
  • BITMAP
  • FUNCTIONBED
  • REVERSE KEY
  • GLOBAL
  • LOCAL
Here BTREE and BITMAP are widely used.
BTREE INDEX 
  • The most common type of indexing technique is the B-TREE index
  • It is built on the primary key automatically.
  • This index structure contains a sorted list of all the actual discrete column values.
  •  Ech value in the index is associated with a list of pointers to the rows in the original table that contain value.
  • On query executing, the database engine's query optimizer
                    1) First determines whether or not to use index. If the database chooses to use the                                 index, Then the database engine
                        a) Traverses the B-Tree to find the appropriate keys in the index list.
                        b) Collects the associated list of pointers to the rows in the table.
  • To allow the database engine to quickly find any element in the sorted index list, the index is stored internally using a binary tree(B-Tree) representation.
  • B-Tree indexes are most effective for high- cardinality data(Data with many possible values).
  • Using indexes a full scan can be replaced by a quick red of the index followed by read of only those disk blocks that contain the rows needed
  • B-Tree performance is good for both small and large tables and does not degrade as the size of a table grows.
BITMAP INDEX 
  • Bit map indexes are typically used for
              a) Large mount of data and ad-hoc queries and low level of concurrent DML transactions.
  • Bitmap indexes are most effective for queries that contain multiple conditions in the WHERE clause.
  • In Bitmap index, a bitmap for each key value replaces a list of row ids
  • Each bit in bitmap corresponds to a possible rowid.
  • A mapping function converts the bit position to an actual rowid, so that the bitmap index provides the same functionality as a  regular index.
  • The advantages of using bitmap indexes are greatest for low cardinality columns 
Advantages of  BITMAP INDEX   
  • Reduced response time for large classes of d-hoc qqueries.
  • A substantial reduction of space usage compared to other indexing techniques.
  • Dramatic performance gains even in very low end hardware.
  • Very efficient parallel DML and loads

             
                               


Powered by Blogger.