When you insert or update rows in a table that has a referential integrity constraint, the showplan output includes “FROM TABLE” and other messages indicating the method used to access the referenced table. This salesdetail table definition includes a referential integrity check on the title_id column:
create table salesdetail (
stor_id char(4),
ord_num varchar(20),
title_id tid
references titles(title_id),
qty smallint,
discount float )
An insert to salesdetail, or an update on the title_id column, requires a lookup in the titles table:
insert salesdetail values ("S245", "X23A5", "T10", 15, 40.25)
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is INSERT.
The update mode is direct.
FROM TABLE
titles
Using Clustered Index.
Index : title_id_ix
Forward scan.
Positioning by key.
Keys are:
title_id
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
TO TABLE
salesdetail
The clustered index on title_id_ix is used to verify the referenced value.
