Indexing

Apply for Business Intelligence Certification Now!!

Indexing is used to store some information about data for the fast retrieval of data. The main advantage of storing indexes is to retrieving relevant information within time. Without indexes the search may require to go through every piece of data or information available in data warehouse, which is more time consuming and waste a lot of computing power.

B-Tree

A B-Tree is especially designed for information stored on disk. In a B-Tree, each node may contain a large number of keys. In a similar manner the sub tree nodes may also contain a large number of key data. An optimized B-Tree has a lot of branches and a large number of keys, so that the height of tree reduces, which will lead to faster data retrieval. This means only few nodes are required to read from the disk for the retrieval of data. The goal of B-tree is to retrieve data in faster manner form the disk device, which means reading a very less number of records.

Fig. 11.1 Example of B-TREE

Clustered

A CLUSTERED index is used to reorder the data. Similar to the telephone directory, if the number is added into the directory it rearranges all the data based on the last name and first name. In similar manner a clustered index in relational database dictates the physical order of data in the table. Thus only one cluster index is permitted for a table, although a clustered index (composite index) might be composed of one or more columns. This is efficient in cases where frequent searches are made between two ranges. For example, if in a organization reports needs the data between two dates, then the cluster index return the first row begin with start date, and keep on incrementing itself to the next row until the end date is matched. This can help improving the performance. Clustered index are also efficient in finding the rows where the column indexed with unique key.

http://www.vskills.in/certification/Certified-Business-Intelligence-Professional