Oracle and DB2, Comparison and Compatibility/Storage Model/Indexes

Overview

Indices are performance structures. Generally, indexes speed access to data. An index operates like a directory – it is organized in a way that you can quickly find the entry you need. Indexes work on the contents of columns in your tables. When you use an index, the database will scan the sorted index to locate the data you need. If you have many indexes it can actually clobber database performance because each index needs to be maintained every time the data changes (inserts, updates and deletes). Just because indexes are a good thing they are like anything else in life; you can have too much of a good thing.

Indexes in some instances may not be the fastest way to access data. If you have very small tables, it is quicker to read the whole table to get the entry you want rather than reading the index first and then reading the data. If it the job of the cost-based Optimizer to decide whether to use an index. If you are positive you want you're query to use a specific index, you can override the optimize with a Hint.

Both Oracle and DB2 have unique, non-unique, and composite indexes, and handle them in much the same way. Both use a balanced B-tree index to provide equal access to any row in the table. B-trees are optimized for systems that read and write large blocks of data (i.e. databases and file systems.) A B-tree is a tree data structure that keeps data sorted and allows searches, sequential access, insertions, and deletions. A B-tree is kept balanced by requiring that all leaf nodes are at the same depth. This depth increases as elements are added to the tree, but the overall depth does not increase much.

Unique indexes guarantee that now two columns in the index have the same value. Unique indexes are automatically created for each primary key on a table. This ensures that each row has a unique identifier.

Non-unique indexes allow columns in an index to have the same value. A simple example would be to create an index on last name. You could then access all the people with the last name ‘Smith’.

Composite indexes are comprised of more than one column. This is useful where two columns in a table are often accessed together.

The following sections on Oracle and DB2 cover how each handle indexes differently. The summary contains an overview of how each DBMS handles clustering, which in most cases depend on indexes.