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 = 123The 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_explainedview 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
IXSCANis similar to Oracle’sINDEX [RANGE|UNIQUE] SCAN. From this output, we cannot decided if it is a unique or range scan. TheFETCHoperation corresponds to Oracle’sTABLE ACCESS BY INDEX ROWID.- MySQL
+----+-----------+-------+---------+---------+------+-------+ | id | table | type | key | key_len | rows | Extra | +----+-----------+-------+---------+---------+------+-------+ | 1 | employees | const | PRIMARY | 5 | 1 | | +----+-----------+-------+---------+---------+------+-------+Type
constis 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 Scancombines theINDEX [UNIQUE/RANGE] SCANandTABLE ACCES BY INDEX ROWIDoperations 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 SEEKandRID Lookupcorrespond to Oracle’sINDEX RANGE SCANandTABLE ACCESS BY ROWIDrespectively. Unlike the Oracle Database, SQL Server explicitly shows theNested Loopsjoin 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_NAMELAST_NAMETABLE 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.
