When the INSERT, UPDATE,
and DELETE operators are
used on a table that has one or more referential integrity constraints,
the showplan output also shows the DIRECT
RI FILTER and DEFERRED
RI FILTER child operators of the DML operator. The type
of referential integrity constraint determines whether one or both
of these operators are present.
The following example is for an insert into the titles table of the pubs3 database. This table has a column called pub_id that references the pub_id column of the publishers table. The referential integrity constraint on titles.pub_id requires that every value that is inserted into titles.pub_id must have a corresponding value in publishers.pub_id.
The query and its query plan are:
use pubs3
go
set showplan on
insert into titles values ("AB1234", "Abcdefg", "test", "9999", 9.95, 1000.00, 10, null, getdate(),1)
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is INSERT.
4 operator(s) under root
ROOT:EMIT Operator (VA = 3)
|INSERT Operator (VA = 2)
| The update mode is direct.
|
| |SCAN Operator (VA = 1)
| | FROM CACHE
|
| |DIRECT RI FILTER Operator has 1 children.
| |
| | |SCAN Operator (VA = 0)
| | | FROM TABLE
| | | publishers
| | | Index : publishers_6240022232
| | | Forward Scan.
| | | Positioning by key.
| | | Index contains all needed columns. Base table will not be read. | | | Keys are: | | | pub_id ASC | | | Using I/O Size 2 Kbytes for index leaf pages. | | | With LRU Buffer Replacement Strategy for index leaf pages. | | TO TABLE | titles | Using I/O Size 2 Kbytes for data pages.
In the query plan, the INSERT operator’s
left child operator is a CACHE SCAN, which
returns the row of values to be inserted into titles. The INSERT operator’s
right child is a DIRECT RI FILTER operator.
The DIRECT RI FILTER operator
executes a scan of the publishers table to find
a row with a value of pub_id that
matches the value of pub_id to be
inserted into titles. If a matching row is
found, the DIRECT RI FILTER operator
allows the insert to proceed, but if a matching
value of pub_id is not found in publishers,
the DIRECT RI FILTER operator
aborts the command.
In this example, the DIRECT RI FILTER can
check and enforce the referential integrity constraint on titles for
each row that is inserted, as it is inserted.
The next example shows a DIRECT RI FILTER operating
in a different mode, together with a DEFERRED
RI FILTER operator:
use pubs3 go set showplan on go update publishers set pub_id = '0001' QUERY PLAN FOR STATEMENT 1 (at line 1). STEP 1 The type of query is UPDATE. 13 operator(s) under root ROOT:EMIT Operator (VA = 13) |UPDATE Operator (VA = 1) | The update mode is deferred_index. | | |SCAN Operator (VA = 0) | | FROM TABLE | | publishers | | Table Scan. | | Forward Scan. | | Positioning at start of table. | | Using I/O Size 2 Kbytes for data pages. | | With LRU Buffer Replacement Strategy for data pages. | | |DIRECT RI FILTER Operator (VA = 7) has 1 children. | | | | |INSERT Operator (VA = 6) | | | The update mode is direct. | | | | | | |SQFILTER Operator (VA = 5) has 2 children. | | | | | | | | |SCAN Operator (VA = 2) | | | | | FROM CACHE | | | | | | | | Run subquery 1 (at nesting level 0). | | | | | | | |QUERY PLAN FOR SUBQUERY 1 (at nesting level 0 and at line 0). | | | | | | | | Non-correlated Subquery. | | | | Subquery under an EXISTS predicate. | | | | | | | | |SCALAR AGGREGATE Operator (VA = 4) | | | | | Evaluate Ungrouped ANY AGGREGATE. | | | | | Scanning only up to the first qualifying row. | | | | | | | | | | |SCAN Operator (VA = 3) | | | | | | FROM TABLE | | | | | | titles | | | | | | Table Scan. | | | | | | Forward Scan. | | | | | | Positioning at start of table. | | | | | | Using I/O Size 2 Kbytes for data pages. | | | | | | With LRU Buffer Replacement strategy for data pages. | | | | | | | | END OF QUERY PLAN FOR SUBQUERY 1. | | | | | | TO TABLE | | | Worktable1. | | |DEFERRED RI FILTER Operator has (VA = 12) 1 children. | | | | |SQFILTER Operator (VA = 11) has 2 children. | | | | | | |SCAN Operator (VA = 8) | | | | FROM TABLE | | | | Worktable1. | | | | Table Scan. | | | | Forward Scan. | | | | Positioning at start of table. | | | | Using I/O Size 2 Kbytes for data pages. | | | | With LRU Buffer Replacement Strategy for data pages. | | | | | | Run subquery 1 (at nesting level 0). | | | | | | QUERY PLAN FOR SUBQUERY 1 (at nesting level 0 and at line 0). | | | | | | Non-correlated Subquery. | | | Subquery under an EXISTS predicate. | | | | | | |SCALAR AGGREGATE Operator (VA = 10) | | | | Evaluate Ungrouped ANY AGGREGATE. | | | | Scanning only up to the first qualifying row. | | | | | | | | |SCAN Operator (VA = 9) | | | | | FROM TABLE | | | | | publishers | | | | | Index : publishers_6240022232 | | | | | Forward Scan. | | | | | Positioning by key. | | | | | Index contains all needed columns. Base table will not be read. | | | | | Keys are: | | | | | pub_id ASC | | | | | Using I/O Size 2 Kbytes for index leaf pages. | | | | | With LRU Buffer Replacement Strategy for index leaf pages. | | | | | | END OF QUERY PLAN FOR SUBQUERY 1. | | TO TABLE | publishers | Using I/O Size 2 Kbytes for data pages.
The referential integrity constraint on titles requires that for every value of titles.pub_id there must exist a value of publishers.pub_id. However, this example query is changing the values of publisher.pub_id, so a check must be made to maintain the referential integrity constraint.
The example query can change the value of publishers.pub_id for several rows in publishers, so a check to make sure that all of the values of titles.pub_id still exist in publisher.pub_id cannot be done until all rows of publishers have been processed.
This example calls for deferred referential integrity checking:
as each row of publishers is read, the UPDATE operator
calls upon the DIRECT RI FILTER operator
to search titles for a row with the same value
of pub_id as the value that is about
to be changed. If a row is found, it indicates that this value of pub_id must
still exist in publishers to maintain the referential
integrity constraint on titles, so the value
of pub_id is inserted into WorkTable1.
After all of the rows of publishers have
been updated, the UPDATE operator
calls upon the DEFERRED RI FILTER operator
to execute its subquery to verify that all of the values in Worktable1 still
exist in publishers. The left child operator
of the DEFERRED RI FILTER is
a SCAN which reads the
rows from Worktable1. The right child is a SQFILTER operator
that executes an existence subquery to check for a matching value
in publishers. If a matching value is not found,
the command is aborted.
The examples in this section used simple referential integrity
constraints, between only two tables. Adaptive Server allows up
to 192 constraints per table, so it can generate much more complex
query plans. When multiple constraints must be enforced, there is
still only a single DIRECT RI FILTER or DEFERRED
RI FILTER operator in the query plan, but these
operators can have multiple subplans, one for each constraint that
must be enforced.
