January 22, 2021
Hot Topics:

An Oracle Tune-Up: Tuning Your SQL for Maximum Performance

  • By David Thurmond
  • Send Email »
  • More Articles »

In this situation, the department_id field is an example of a foreign key. This means that a value in a table is a key value in another table. In this case, the department_id field is unique on the departments table because it is the table's primary key. It is also an attribute of the Employees table because each employee belongs to a department. Having a foreign key constraint on the Employees table insures the data integrity of the employee data because this constraint will not allow an employee record to contain any value that does not exist in the Departments table.

Having an index that contains the department_id field on the Employees table increases the efficiency of the search, as shown from the decrease in cost after the index is created. Any time that a foreign key relationship exists like the one described here, it is always best to have an index that contains that foreign key.

So, if indexes are such wonderful things for query performance, why not just create an index on every column of every table? In theory, this sounds great, but the convenience of an index comes at a price. Having many indexes on a table will slow down DML operations, such as inserts, updates, and deletes. This is because each time a row in the table is inserted, changed, or removed, the associated indexes also have to be changed to reflect the table data. Also, if there are any foreign key constraints to maintain data integrity as shown in this example, Oracle must verify that any value that is about to be inserted is valid.

This will slow down these operations significantly for tables with many columns that contain many indexes. The definition of "many" indexes is rather subjective. However, the best practice is to create indexes only where foreign key constraints or search functionality calls for them.

Take A Hint

Creating indexes can improve query performance; however, sometimes, even when appropriate indexes are created, Oracle is still not able to figure out the best way to execute a query. Consider the following example.

Suppose you have a payroll transactions table and indexes as follows:

create table payroll_transactions (
   transaction_id number(38) not null,
   transaction_type varchar2(10) not null,
   transaction_date date not null,
   employee_id varchar2(9) not null,
   amount number(9, 2) not null

alter table payroll_transactions
   add constraint payroll_transaction_pk primary key

create index payroll_transaction_idx01 on
   payroll_transactions (transaction_date);

create index payroll_transaction_idx02 on
   payroll_transactions (transaction_date, transaction_type);

create index payroll_transaction_idx03 on
   payroll_transactions (employee_id);

create index payroll_transaction_idx04 on
   payroll_transactions (transaction_date, employee_id);

Now, suppose you want to figure out how much money a particular employee has been paid via automatic deposit since his hire date:

select sum(amount)
   from payroll_transactions
   where transaction_date > to_date('01/01/2008',
                                    'mm/dd/yyyy') and
   employee_id = '123456789';

This query would use the payroll_transaction_idx03 index because it is the first index on the table containing the employee ID, as shown by the following execution plan:

Execution Plan


    (Cost=2 Card=1 Bytes=20)
    (Cost=2 Card=1 Bytes=20)

    (Cost=1 Card=2)

However, the table also has payroll_transaction_idx04, which contains both the transaction date and the employee ID, which is a perfect fit. Luckily, there is a way to show Oracle the preferred way for the query to be executed: by giving the optimizer a hint. Executing the following query:

select /*+INDEX(payroll_transactions payroll_transaction_idx04)*/
   from payroll_transactions
   where transaction_date > to_date('01/01/2008',
                                    'mm/dd/yyyy') and
   employee_id = '123456789';

yields the following execution plan, which shows that the optimizer is using the desired index:

Execution Plan


    (Cost=2 Card=1 Bytes=20)
    (Cost=2 Card=1 Bytes=20)

Although the cost for both queries is the same for the small sample data set used here, consider a payroll transaction table containing data for thousands of employees over the course of many years. The index containing the transaction date and employee ID would be the choice that would contain the most unique information. The hint forces the optimizer to use the correct index with the following:

/*+INDEX(payroll_transactions payroll_transaction_idx04)*/

The hint is always enclosed in /* and */, and must always begin with a plus sign. Then, the hint instructs Oracle to use an index by specifying the INDEX keyword. Finally, the desired table name and index on that table are specified by putting them in parentheses after the index hint. Failing to get the syntax of the hint exactly right will result in Oracle ignoring the hint.

Page 3 of 5

This article was originally published on June 2, 2008

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date