Let’s Speed Things Up: An Overview of Oracle Indexing

Oracle 11g provides many types of indexes. Understanding when it’s appropriate to create an index, and what kind of index to create when you do, is essential to improving the performance of your SQL statements. So let’s take a look at what Oracle has to offer in the way of indexing.

A B-tree index is the traditional type of index that has been around since the early releases of Oracle. It’s the kind of index that will be created if you don’t specify any particular type of index in your CREATE INDEX statement. The B-tree index is appropriate if the column you’re building the index on has unique values, or at least many distinct values. We call this “high cardinality.” You’ll note that Oracle automatically builds a B-tree index on any column you declare as the PRIMARY KEY or as UNIQUE. However, other columns in your larger tables that are used as search criteria where the data in that column has high cardinality are good candidates for a B-tree index. Like just about every type of index, you do have to weigh the advantage the index provides for your SELECT statements versus the added cost of the index due to any DML activity.

Another type of index (and really just a special type of B-tree index) is the reverse key index. This index type is appropriate in a situation where a SEQUENCE value might be used to INSERT many rows into a table. If the sequence is generating 501, then 502, then 503, the reverse key index will see these values as 105, then 205, then 305. With the numbers in their original order, the index entries would all be stored on the same block, possibly causing contention on that block. However, in their reversed order, the index values will be spread out over many blocks, eliminating the contention. You treat the index values as they exist in their normal non-reversed order in your SQL statements. The software takes care of reversing the digits when that’s necessary.

A function-based index can be built on a column which is involved in a search criteria but the column has a function which is typically applied to it in the search. For example, if you query the last_name column of the emp table like this:

SELECT * FROM emp WHERE last_name = ‘Baker’

A regular B-tree index on the column last_name would be appropriate if the emp table is reasonably large, if the last_name column has high cardinality, and if queries are often made on the column last_name. But what if the logic of the application requires searches to be case-insensitive when looking for a last name? Your query may look more like this:

SELECT * FROM emp WHERE LOWER(last_name) = ‘baker’

This SELECT statement will find all employees with a last name of Baker, regardless of how it is capitalized in the database. Great, but wait a minute! As you probably know, when you perform any sort of function or mathematical operation on the column name in the search clause, the optimizer is no longer able to use the available index. The same would be true if you had an index on the salary column and you executed this query:

SELECT * FROM emp WHERE salary + 0 = 5000

Since salary is involved in a mathematical operation (which in this case is meaningless), it will still prevent the optimizer from using the available index on the salary column. The solution in both of these examples is to build a function-based index. For example, you could build an index on LOWER(last_name) in the first example. This would be a function-based index, and now the optimizer would have the option of using the function-based index when you queried on the LOWER(last_name). Keep in mind if in other SQL statements you need to apply different functions or perform other arithmetic on that column, you must build a function based index on each one of those different functions being applied to your column if you want the optimizer to have the opportunity to use it.

Finally, we have the bitmap index. The bitmap index is quite different in usage from the other types of indexes discussed thus far. The most important difference is that it is an appropriate index type on a column where there are few distinct values (low cardinality). The values in the column containing the bitmap index can be updated, but it is an “expensive” activity and it requires a significant amount of locking. Because of this, and because bitmap indexes work very nicely on huge tables, they are often seen in Data Warehousing applications. Whenever I teach a class on this topic, I always use an example of a car manufacturing plant. Each car produced is tracked by inserting a row into the CAR table. Various attributes of each car are tracked, including VIN, Make, Model, Price, Color, and so on. The VIN would be the primary key, and as such would get a B-tree index. If queries on this table often required searches based on the color of the car, a bitmap index would be a very appropriate index type. The reasons—the CAR table has a large number of rows, the Color column has poor cardinality (most car companies only have about 6 or 8 colors to choose from), the factory is going to hardly ever change the color once the car is painted, and the row is inserted into the CAR table, and as stated the application, does require us to search for cars based on color.

Whenever you’re considering adding an index, remember to pick the right kind. If you use the wrong kind of index, it could slow down your performance. Check to make sure the optimizer is using the index, and compare processing time and I/O with and without the index when running with a typical workload. With a little bit of work, you can get those SQL commands buzzing along.

Credit goes to the below website(s) :

http://transcender.wordpress.com/2011/09/22/lets-speed-things-up-an-overview-of-oracle-indexing



There are many index types within Oracle:
•
B*Tree Indexes
- These are the common indexes in Oracle. They are simular construct to a binary tree,they provide fast access by key, to an individual row or range of rows, normally requiring very fewreads to find the correct row. 

The B*Tree index has several subtypes

            Index Organised Tables                   - A table stored in a B*Tree structure (see Oracle Index organisedTables.doc)  
             B*Tree Cluster Indexes
                   - They are used to index the cluster keys (see Oracle Cluster Indexes.doc)
             Reverse Key Indexes
                  - The bytes in the key are reversed.This is used to stop sequencal keys being on the same block   like 999001, 999002, 999003 would be reversed to 100999, 200999,300999 thus these would be located on different blocks.             Descending Indexes                 - They allow data to be sorted from big to small (decending) instead of small to big (ascending).Bitmap Indexes 

   - With a bitmap index , a single index entry uses a bitmap to point to many rows simultaneously, they are used with low data that is mostly read-only. They should not be used withOLTP systems.
Function Based Indexes
- These are B*Tree or bitmap indexes that store the computed result of afunction on a row(s) - not the column data itself.

Application Domain Indexes
- These are indexes you build and store yuorself, either in Oracle or outside of Oracle

InterMedia Text Indexes
- This is a specialised index built into Oracle to allow for keyword searchingof large bodies of text.

http://www.scribd.com/doc/2675082/Oracle-Indexes