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.

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.

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.

SELECT *
FROM employees
WHERE department_id = :p_id;
SELECT *
FROM departments
WHERE department_id = :p_id;
SELECT *
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_id = :p_id;	

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.