An Overview of Oracle Indexes-Indexes

Oracle provides many different types of indexes for us to use. Briefly, they are as follows:

•\ BTree indexes: These are what I refer to as conventional indexes. They are, by far, the most common indexes in use in Oracle and most other databases. Similar in construct to a binary tree, BTree indexes provide fast access, by key, to an individual row or range of rows, normally requiring a few reads to find the correct row. It is important to note, however, that the “B” in “BTree” does not stand for binary but rather for balanced. A BTree index is not a binary tree at all, as we’ll see when we look at how one is physically stored on disk. The B*Tree index has several subtypes:

•\ Index-organized tables: These are tables stored in a B*Tree structure. Whereas rows of data in a heap table are stored in an unorganized fashion (data goes wherever there is available space), data in an IOT is stored and sorted by primary key. IOTs behave just like “regular” tables as far as your application is concerned; you use SQL to access them as normal. IOTs are especially useful for information retrieval, spatial, and OLAP applications. We discussed IOTs in some detail in Chapter 10.

•\ BTree cluster indexes: These are a slight variation of conventional BTree indexes. They are used to index the cluster keys (see the section “Index Clustered Tables” in Chapter 10) and will not be discussed again in this chapter. Rather than having a key that points to a row, as for a conventional BTree, a BTree cluster has a cluster key that points to the block that contains the rows related to that cluster key.

•\ Descending indexes: Descending indexes allow for data to be sorted from big to small (descending) instead of small to big (ascending) in the index structure. We’ll take a look at why that might be important and how they work.

•\ Reverse key indexes: These are BTree indexes whereby the bytes in the key are reversed. Reverse key indexes can be used to obtain a more even distribution of index entries throughout an index that is populated with increasing values. For example, if I am using a sequence to generate a primary key, the sequence will generate values like 987500, 987501, 987502, and so on. These sequence values are monotonic, so if I were using a conventional BTree index, they would all tend to go the same right-hand-side block, thus increasing contention for that block. With a reverse key index, Oracle will logically index 205789, 105789, 005789, and so on instead. Oracle will reverse the bytes of the data to be stored before placing them in the index, so values that would have been next to each other in the index before the byte reversal will instead be far apart. This reversing of the bytes spreads out the inserts into the index over many blocks.

•\ Bitmap indexes: Normally in a B*Tree, there is a one-to-one relationship between an index entry and a row: an index entry points to a row. With bitmap indexes, a single index entry uses a bitmapto point to many rows simultaneously. They are appropriate for highly repetitive data (data with few distinct values relative to the total number of rows in the table) that is mostly read-only. Consider a column that takes on three possible values—Y, N, and NULL—ina table of one million rows. This might be a good candidate for a bitmap index, if, for example, you need to frequently count how many rows have a value of Y. That is not to say that a bitmap index on a column with 1000 distinct values in that same table would not be valid—it certainly can be. Bitmap indexes should never be considered in an OLTP database for concurrency-related issues (which we’ll discuss in due course). Note that bitmap indexes require the Enterprise or Personal Edition of Oracle.

•\ Bitmap join indexes: These provide a means of denormalizing data in an index structure, instead of in a table. For example, consider the simple EMP and DEPT tables. Someone might ask the question, “How many people work in departments located in the city of Boston?” EMP has a foreign key to DEPT, and in order to count the employees in departments with a LOC value of Boston, we would normally have to join the tables to get the LOC column joined to the EMP recordsto answer this question. Using a bitmap join index, we can instead index the LOC column against the EMP table. The same caveat in regard to OLTP systems applies to a bitmap join index as a regular bitmap index.

•\ Function-based indexes: These are B*Tree or bitmap indexes that store the computed result of a function on a row’s column(s), not the column data itself. You can consider them an index on a virtual (or derived) column—in other words, a column that is not physically stored in the table. These may be used to speed up queries of the form SELECT * FROM T WHERE FUNCTION(DATABASE_COLUMN) = SOME_VALUE, since the value FUNCTION(DATABASE_COLUMN) has already been computed and stored in the index.

•\ Application domain indexes: These are indexes you build and store yourself, either in Oracle or perhaps even outside of Oracle. You tell the optimizer how selective your index is and how costly it is to execute, and the optimizer will decide whether or not to use your index based on that information. The Oracle text index is an example of an application domain index; it is built using the same tools you may use to build your own index. It should be noted that the index created here need not use a traditional index structure. The Oracle text index, for example, uses a set of tables to implement its concept of an index.

As you can see, there are many index types to choose from. In the following sections, I’ll present some technical details on how each one works and when it should be used. I would like to stress again that we will not cover certain DBA-related topics. For example, we will not discuss the mechanics of an online rebuild; rather, we will concentrate on practical application-related details.