Hi,This is Halim,working as a Sr. Oracle DBA and PL/SQL developer in Atlanta, USA. I very much like to play with SQL, PL/SQL and scripting languages along with my DBA job. I got a recognition for PL/SQL. Please see here at 16th position(http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html). I am maintaining this blog for helping myself while in work and sharing my knowledge with others. If you have any question. you can reach me at infohalimdba@gmail.com Thanks.

Monday, September 28, 2009

Different Index Type

There are several index types available, and each index has benefits for certainsituations.

The following list gives performance ideas associated with each index type.

1. B-Tree Indexes -------------These indexes are the standard index type, and they are excellent forprimary key and highly-selective indexes. Used as concatenated indexes, B-treeindexes can be used to retrieve data sorted by the index columns.

2. Bitmap Indexes-------------- These indexes are suitable for low cardinality data. Throughcompression techniques, they can generate a large number of rowids with minimalI/O. Combining bitmap indexes on non-selective columns allows efficient AND and ORoperations with a great number of rowids with minimal I/O. Bitmap indexes areparticularly efficient in queries with COUNT(), because the query can be satisfied within the index.

3. Function-based Indexes --------------------These indexes allow access through a B-tree on a value derived from a function on the base data. Function-based indexes have some limitations with regards to the use of nulls, and they require that you have the query optimizer enabled.Function-based indexes are particularly useful when querying on composite columnsto produce a derived result or to overcome limitations in the way data is stored in the database. An example is querying for line items in an order exceeding a certain value derived from (sales price - discount) x quantity, where these were columns in the table. Another example is to apply the UPPER function to the data to allow case-insensitive searches.

4. Partitioned Indexes -----------------Partitioning a global index allows partition pruning to take placewithin an index access, which results in reduced I/Os. By definition of good range orlist partitioning, fast index scans of the correct index partitions can result in very fast query times.

5. Reverse Key Indexes-------------------These indexes are designed to eliminate index hot spots on insertapplications. These indexes are excellent for insert performance, but they are limited in that they cannot be used for index range scans.

6. Index-Organized Tables or Appending Columns to an Index --------------------------------------------------------One of the easiest ways to speed up a query is to reduce the number of logical I/Os by eliminating a table access from the execution plan. This can be done by appending to the index all columns referenced by the query. These columns are the select listcolumns, and any required join or sort columns. This technique is particularly usefulin speeding up online applications response times when time-consuming I/Os arereduced. This is best applied when testing the application with properly sized data for the first time. The most aggressive form of this technique is to build an index-organized table (IOT). However, you must be careful that the increased leaf size of an IOT does notundermine the efforts to reduce I/O.