Oracle Indexes   «Prev  Next»

Lesson 5 Storage considerations for indexes
Objective Separate indexes from data tables.

Separate Index from Oracle Data Tables

You have learned that I/O operations are one of the primary bottlenecks in the operation of a database. Indexes help to reduce the number of I/O operations, but you can further enhance the performance of data retrieval by separating indexes from their associated tables.

Disk contention

We discussed how disk I/O is the slowest operation in a computer, because of the need for the physical movement of the disk head. There is another factor that contributes to slow disk performance and disk contention[1].
The disk head is not only moving back and forth across the disk, but it is doing this in response to many requests for many types of information. Because the disk head can read only one piece of information at a time, these multiple requests all contend for the operations of the disk head, creating contention that forces disk requests to queue up for access. The more contention, the longer the queues, and the slower the disk access performance. When you access data through an index, you are guaranteed to request data from the index and from the database table. If these two structures are on separate disks, you can reduce disk head contention and improve I/O performance.

Separating indexes

You can specify where an index goes by adding the TABLESPACE tablespace_name clause to the CREATE INDEX SQL command described in the previous lesson, where tablespace_name is the name of an existing tablespace. This clause follows the ASC/DESC keyword.
The difference separating an index can make is shown in this Slide Show.

Separating Index Produces Results
There are other ways that you can optimize your performance.

Hash Cluster Queries

The database, not the user, determines how to hash the key values input by the user. For example, assume that users frequently execute queries such as the following, entering different department ID numbers for p_id:
FROM employees
WHERE department_id = :p_id;
FROM departments
WHERE department_id = :p_id;
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_id = :p_id;	

If a user queries employees in department_id=20, then the database might hash this value to bucket 77. If a user queries employees in department_id=10, then the database might hash this value to bucket 15. The database uses the internally generated hash value to locate the block that contains the employee rows for the requested department. Figure 5-5 depicts a hash cluster segment as a horizontal row of blocks.

Figure 5-5: As shown in the graphic, a query can retrieve data in a single I/O.
A limitation of hash clusters is the unavailability of range scans on nonindexed cluster keys. The next lesson is about altering an index.

Adding Indexes - Exercise

Click the Exercise link below to practice creating additional indexes for the COIN database.
Adding Indexes - Exercise

[1]Contention: A source of possible performance degradation caused by two or more users contending for the same resources.