2011-11-10Primary Keys

We start with the simplest, yet most common where clause: the primary key lookup. For that purpose, we create the EMPLOYEES table, which is used for all examples in this chapter:

CREATE TABLE employees (
   employee_id   NUMBER         NOT NULL,
   first_name    VARCHAR2(1000) NOT NULL,
   last_name     VARCHAR2(1000) NOT NULL,
   date_of_birth DATE           NOT NULL,
   phone_number  VARCHAR2(1000) NOT NULL,
   CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);

The database creates an index for the primary key automatically. That means, there is an index on EMPLOYEE_ID, even though there is no create index statement.

The following query uses the primary key to retrieve an employee’s name:

SELECT first_name, last_name 
  FROM employees 
 WHERE employee_id = 123

A query on the primary key cannot deliver multiple rows. It is therefore sufficient to traverse the index tree without following the leaf nodes. The so-called execution plan can be used to verify the thesis:

---------------------------------------------------------------
|Id |Operation                   | Name         | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT            |              |    1 |    2 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |    1 |    2 |
|*2 |  INDEX UNIQUE SCAN         | EMPLOYEES_PK |    1 |    1 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPLOYEE_ID"=123)

The Oracle execution plan shows an INDEX UNIQUE SCAN—the operation that performs the tree traversal only. It takes the best benefit from the logarithmic scalability and retrieves the entry almost instantly.

The database performs an additional step, the TABLE ACCESS BY INDEX ROWID, to fetch the actual table data (FIRST_NAME, LAST_NAME). This operation can become a performance bottleneck—as explained in “Slow Indexes, Part I”—but is no risk for an INDEX UNIQUE SCAN. A unique scan delivers one entry only so that the table access is limited to one access as well. That means, the ingredients for a slow query cannot occur with an INDEX ONLY SCAN.