We start with the simplest yet most common where
clause: the primary key lookup. For the examples throughout this chapter we use the EMPLOYEES
table defined as follows:
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 automatically creates an index for the primary key. That means there is an index on the EMPLOYEE_ID
column, even though there is no create index
statement.
Tip
Appendix C, “Example Schema” contains scripts to populate the EMPLOYEES
table with sample data. You can use it to test the examples in your own environment.
To follow the text, it is enough to know that the table contains 1000 rows.
The following query uses the primary key to retrieve an employee’s name:
SELECT first_name, last_name
FROM employees
WHERE employee_id = 123
The where
clause cannot match multiple rows because the primary key constraint ensures uniqueness of the EMPLOYEE_ID
values. The database does not need to follow the index leaf nodes—it is enough to traverse the index tree. We can use the so-called execution plan for verification:
- DB2
The following execution plan was gathered with the
last_explained
view available from the appendix.Explain Plan ------------------------------------------------------- ID | Operation | Rows | Cost 1 | RETURN | | 13 2 | FETCH EMPLOYEES | 1 of 1 (100.00%) | 13 3 | IXSCAN EMPLOYEES_PK | 1 of 1000 ( .10%) | 6 Predicate Information 3 - START (Q1.EMPLOYEE_ID = +00123.) STOP (Q1.EMPLOYEE_ID = +00123.)
The Operation
IXSCAN
is similar to Oracle’sINDEX [RANGE|UNIQUE] SCAN
. From this output, we cannot decided if it is a unique or range scan. TheFETCH
operation corresponds to Oracle’sTABLE ACCESS BY INDEX ROWID
.- MySQL
+----+-----------+-------+---------+---------+------+-------+ | id | table | type | key | key_len | rows | Extra | +----+-----------+-------+---------+---------+------+-------+ | 1 | employees | const | PRIMARY | 5 | 1 | | +----+-----------+-------+---------+---------+------+-------+
Type
const
is MySQL’s equivalent of Oracle’sINDEX UNIQUE SCAN
.- Oracle
--------------------------------------------------------------- |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)
- PostgreSQL
QUERY PLAN ------------------------------------------- Index Scan using employees_pk on employees (cost=0.00..8.27 rows=1 width=14) Index Cond: (employee_id = 123::numeric)
The PostgreSQL operation
Index Scan
combines theINDEX [UNIQUE/RANGE] SCAN
andTABLE ACCES BY INDEX ROWID
operations from the Oracle Database. It is not visible from the execution plan if the index access might potentially return more than one row.- SQL Server
|--Nested Loops(Inner Join) |--Index Seek(OBJECT:employees_pk, | SEEK:employees.employee_id=@1 | ORDERED FORWARD) |--RID Lookup(OBJECT:employees, SEEK:Bmk1000=Bmk1000 LOOKUP ORDERED FORWARD)
The SQL Server operation
INDEX SEEK
andRID Lookup
correspond to Oracle’sINDEX RANGE SCAN
andTABLE ACCESS BY ROWID
respectively. Unlike the Oracle Database, SQL Server explicitly shows theNested Loops
join to combine the index and table data.
The Oracle execution plan shows an INDEX UNIQUE SCAN
—the operation that only traverses the index tree. It fully utilizes the logarithmic scalability of the index to find the entry very quickly—almost independent of the table size.
Tip
The execution plan (sometimes explain plan or query plan) shows the steps the database takes to execute an SQL statement. Appendix A explains how to retrieve and read execution plans with other databases.
After accessing the index, the database must do one more step to fetch the queried data (
, FIRST_NAME
) from the table storage: the LAST_NAME
TABLE ACCESS BY INDEX ROWID
operation. This operation can become a performance bottleneck—as explained in “Slow Indexes, Part I”—but there is no such risk in connection with an INDEX UNIQUE SCAN
. This operation cannot deliver more than one entry so it cannot trigger more than one table access. That means that the ingredients of a slow query are not present with an INDEX UNIQUE SCAN
.
Support My Work
I offer SQL training, tuning and consulting. Buying my book “SQL Performance Explained” (from €9.95) also supports my work on this website.