User's Guide
   PART 4. Database Administration and Advanced Use
     CHAPTER 24. Monitoring and Improving Performance       

Using keys to improve query performance

The foreign key and the primary key are used for validation purposes. However, these keys are also used to improve performance where possible.

Example 

The following example illustrates how keys are used to make commands execute faster.

SELECT *
FROM employee
WHERE emp_id = 390

The simplest way for the server to perform this query would be to look at all 75 rows in the employee table and check the employee ID number in each row to see if it is 390. This does not take very long since there are only 75 employees, but for tables with many thousands of entries the search can take a long time.

The emp_id column is the primary key for the employee table. There is a built-in index mechanism for finding primary and foreign key values quickly. (This mechanism is used for the validation you saw in Validity checking.)

The same mechanism is used automatically to find the employee number 390 quickly. This quick search takes almost the same amount of time whether there are 100 rows or 1,000,000 rows in the table.

  Using Interactive SQL to examine query performance

The Interactive SQL Statistics window tells you when keys are being used to improve performance.

Information in the statistics window 

If you execute a query to look at every row in the employee table:

SELECT *
FROM employee

two lines appear in the Statistics window:

75 rows in query (I/O estimate 14)

PLAN> employee (seq)

The first line indicates the number of rows in the query. Sometimes the database knows exactly, as in this case where there are 75 rows; other times it estimates the number of rows. The first line also indicates an internal I/O estimate of how many times the server will have to look at the database on your hard disk to examine the entire employee table.

The second line summarizes the execution plan for the query: the tables that are searched, any indexes used to search through a table. This plan says that the server will look at the employee table sequentially (that is, one page at a time, in the order that the rows appear on the pages). The letters seq inside parentheses mean that all the rows of the table need to be examined. This makes sense, since the query fetches the entire table.

Resetting statistics 

You may notice, when working through the tutorial yourself, that the statistics window contains estimates than are different from what is given here. This may happen because the optimizer has decided to optimize a query differently. The optimizer maintains statistics as it evaluates queries and uses these statistics to optimize subsequent queries. These statistics can be reset by executing the following statement:

DROP OPTIMIZER STATISTICS

Note that you must have DBA authority to execute this statement. In production environments, dropping the optimizer statistics can cause queries to execute slower, as the optimizer has less information about the actual distribution of data in the database tables.

  Using primary keys to improve query performance

A primary key is used to improve performance on the following statement:

SELECT *
FROM employee
WHERE emp_id = 390

Statistic window information 

The statistics window contains the following two lines:

Estimated 1 rows in query (I/O estimate 2)

PLAN> employee (employee)

Whenever the name inside the parentheses in the Statistics window PLAN description is the same as the name of the table, it means that the primary key for the table is used to improve performance. Also, the Statistics window shows that the database optimizer estimates that there will be one row in the query and that it will have to go to the disk twice.

  Using foreign keys to improve query performance

The following query lists the orders from customer with customer ID 113:

SELECT *
FROM sales_order
WHERE cust_id = 113

Statistic window information 

The statistics window contains the following information:

Estimated 2 rows in query (I/O estimate 2)

PLAN> sales_order (ky_so_customer)

Here ky_so_customer refers to the foreign key that the sales_order table has for the customer table.

Primary and foreign keys are just special indexes that also maintain entity and referential integrity. The integrity is maintained by extra information that is placed in the indexes.