Lesson 1

Indexes are one of the primary ways you can enhance the performance of your database. The Oracle database has a number of enhancements that improve and enhance the capabilities of the indexes you can use.

In this module, you will learn how to:

1. Describe the types of indexes that can be created in Oracle
2. Create a descending index
3. Describe the bitmap index improvements of Oracle
4. Create a reverse index
5. Describe two methods of creating statistics
6. Describe the new Oracle feature of domain indexes
7. Use the ONLINE parameter for indexes
8. Define function-based indexing
9. Create a function-based index

An index is an optional structure, associated with a table or table cluster, that can sometimes speed data access. By creating an index on one or more columns of a table, you gain the ability in some cases to retrieve a small set of randomly distributed rows from the table. Indexes are one of many means of reducing disk I/O. If a heap-organized table has no indexes, then the database must perform a full table scan to find a value. For example, without an index, a query of location 2700 in the hr.departments table requires the database to search every row in every table block for this value. This approach does not scale well as data volumes increase. For an analogy, suppose an HR manager has a shelf of cardboard boxes. Folders containing employee information are inserted randomly in the boxes. The folder for employee Whalen (ID 200) is 10 folders up from the bottom of box 1, whereas the folder for King (ID 100) is at the bottom of box 3. To locate a folder, the manager looks at every folder in box 1 from bottom to top, and then moves from box to box until the folder is found. To speed access, the manager could create an index that sequentially lists every employee ID with its folder location:

ID 100: Box 3, position 1 (bottom)
ID 101: Box 7, position 8
ID 200: Box 1, position 10

Similarly, the manager could create separate indexes for employee last names, department IDs, and so on. In general, consider creating an index on a column in any of the following situations:

1. The indexed columns are queried frequently and return a small percentage of the total number of rows in the table.
2. A referential integrity constraint exists on the indexed column or columns. The index is a means to avoid a full table lock that would otherwise be required if you update the parent table primary key, merge into the parent table, or delete from the parent table.
3. A unique key constraint will be placed on the table and you want to manually specify the index and all index options.

In the next lesson, we will describe index types.