Tuesday, 11 October 2011

BitmapB-Tree Index (Difference, Advantages) Other Index

{\rtf1\ansi\ansicpg1252\deff0\deflang16393{\fonttbl{\f0\froman\fprq2\fcharset0 Times New Roman;}{\f1\fnil\fcharset0 Calibri;}}
{\colortbl ;\red0\green0\blue0;}
{\*\generator Msftedit 5.41.21.2509;}\viewkind4\uc1\pard\li720\lang1033\f0\fs18 A traditional B-Tree (balanced tree) index stores the key values and pointers in an inverted tree structure. The key to good B-Tree index performance is to build the index on columns having a lot of different values. Oracle describes this as "good selectivity" Oracle is able to quickly bypass rows that do not meet the search criteria when searching through indexes built on columns having a high degree of selectivity.\par
Conversely, bitmapped indexes perform better when the selectivity of an index is poor. The fewer different values a bitmapped index contains, the better it will perform. \par
Bitmap indexes, in certain situations, can provide impressive performance benefits. Bitmapped indexes are most appropriate for complex and ad-hoc queries that contain lengthy \i WHERE\i0 clauses on columns that have a limited number of different values (poor selectivity). \par
\cf1 In a bitmap index, a bitmap for each key value is used instead of a list of rowids.\par
Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then it means that the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so the bitmap index provides the same functionality as a regular index even though it uses a different representation internally. If the number of different key values is small, then bitmap indexes are very space efficient. Bitmap indexing is of great benefit to data warehousing applications.\cf0\par
Standard B-tree indexes are most effective for columns containing a high number of different values (good selectivity) and bitmapped indexes are most appropriate for columns with a limited number (poor selectivity) of possible values. \par
Bitmap indexes are good for:\par
Low cardinality columns have a small number of distinct values (compared to the number of rows) \line e.g. Gender or Marital Status\par
High cardinality columns have large numbers of distinct values (over 100).\par
Bitmap indexes include rows that have NULL values, and can dramatically improve the performance of ad hoc queries.\par
Bitmap indexing efficiently merges indexes that correspond to several conditions in a WHERE clause. Rows that satisfy some, but not all, conditions are filtered out before the table itself is accessed. This improves response time, often dramatically.\par
Unlike traditional a B-tree indexes, Bitmap indexes are typically only a fraction of the size of the indexed data in the table.\par
Bitmap indexes are also not suitable for columns that are primarily queried with less than or greater than comparisons. For example, a salary column that usually appears in WHERE clauses in a comparison to a certain value is better served with a B-tree index.\par
Bitmap indexes are not suitable for OLTP applications with large numbers of concurrent transactions modifying the data. These indexes are primarily intended for decision support in data warehousing applications where users typically query the data rather than update it.\par
The advantages of bitmap indexes are greatest for low cardinality columns: that is, columns in which the number of distinct values is small compared to the number of rows in the table.\par
Unlike most other types of index, Bitmap indexes include rows that have NULL values. This can be useful for queries such as SELECT COUNT(*) FROM EMP;\par
You can create bitmap indexes local to a partitioned table (not a global index).\par
\cf1\b Bitmap Join Indexes\cf0\b0\line A join index is an index on one table that involves columns of one or more different tables through a join.\par
Similar to the materialized join view, a bitmap join index pre-computes the join and stores it as a database object. The difference is that a materialized join view materializes the join into a table while a bitmap join index materializes the join into a bitmap index. See the Oracle concepts manual for a full example.\par
\cf1\b Unique and Nonunique Indexes\cf0\b0\line Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns).\line For performance reasons, Oracle recommends that unique indexes be created explicitly, and not through enabling a unique constraint on a table. (Unique integrity constraints are enforced by automatically defining an index.)\par
You can create many indexes for a table as long as the combination of columns differs for each index.\par
\cf1 An index is merely a fast access path to the data. The query optimizer can use an existing index to build another index. This results in a much faster index build.\par
\b Index multiple columns\cf0\line\b0 A composite index is an index that you create on multiple columns in a table. \line This can speed retrieval of data if the SQL WHERE clause references all (or the leading portion) of the columns in the index. Therefore, the order of the columns used in the definition is important - the most commonly accessed or most selective columns go first.\par
\cf1\b Rebuilding indexes\b0\line Although indexes can be modified with ALTER INDEX abc REBUILD it is a commonly held myth about rebuilding indexes that performance will \i automatically\i0 improve.\line By contrast redesigning an index to suit the SQL queries being run will give measurable results.\par
\b Function-Based Indexes\cf0\b0\line You can create indexes on functions and expressions that involve columns in the table being indexed. \line A function-based index pre-computes the value of the function or expression and stores it in an index (B-tree or bitmap).\par
Function-based indexes defined on UPPER (column_name) or LOWER (column_name) can facilitate case-insensitive searches. To use function-based indexes you must gather optimizer statistics. (Not compatible with Rule-based optimization.)\par
If the \i function\i0 is a PL/SQL function or package function, any changes to the function specification will cause the index to be automatically disabled.\cf1\par
\b Key Compression\cf0\b0\line Like any form of compression, Key compression can lead to a huge saving in space, letting you store more keys in each index block, which can lead to less I/O and better performance. Although key compression reduces the storage requirements of an index, it can increase the CPU time required to reconstruct the key column values during an index scan. It also incurs some additional storage overhead.\par
\cf1\b Reverse Key Indexes\b0\line Creating a reverse key index, compared to a standard index, reverses the bytes of each column indexed (except the rowid) while keeping the column order. By reversing the keys of the index, the insertions become distributed across all leaf keys in the index.\line CREATE INDEX i ON t (my_id) REVERSE;\cf0\par
The more even distribution of "hits" on the various leaf blocks is the RKI's best feature. In a heavy, concurrent insert environment, rather than having everyone wanting access to *the* block, you spread the blocks being hit and hence reduce the potentially expensive buffer busy waits.\par
\cf1 The main disadvantage is the inability to perform index range scans as such values are now distributed all over the place, only fetch-by-key or full-index (table) scans can be performed.\par
\cf0 You can specify the keyword NOREVERSE to REBUILD a reverse-key index into one that is not reverse keyed: Indexes. Rebuilding a reverse-key index without the NOREVERSE keyword produces a rebuilt, reverse-key index. You cannot rebuild a normal index as a reverse key index. You must use the CREATE statement instead.\par
\par
\cf1\b Dimensions\cf0\b0\line Define hierarchical relationships between pairs of columns or column sets. (Typically data warehouse parent-child relationships.)\line The columns in a dimension can come either from the same table (denormalized) or from multiple tables (fully or partially normalized). \line To define a dimension over columns from multiple tables, connect the tables using the JOIN clause of CREATE DIMENSION HIERARCHY.\par
\pard\sa200\sl276\slmult1\lang9\f1\fs22\par
}

No comments:

Post a Comment