An Oracle Tune-Up: Tuning Your SQL for Maximum Performance
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 (transaction_id); 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:
---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=20) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PAYROLL_TRANSACTIONS' (TABLE) (Cost=2 Card=1 Bytes=20) 3 2 INDEX (RANGE SCAN) OF 'PAYROLL_TRANSACTION_IDX03' (IND EX) (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)*/ sum(amount) 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:
---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=20) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PAYROLL_TRANSACTIONS' (TABLE) (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:
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