The DML operators usually have only one child operator. However, they can have as many as two additional child operators to enforce referential integrity constraints and to deallocate text data in the case of alter table drop of a text column.
The DML operators modify data by inserting, deleting, or updating rows belonging to a target table.
Child operators of DML operators can be scan operators, join operators, or any data streaming operator.
The data modification can be done using different update modes, as specified by this message:
The Update Mode is <Update Mode>.
The table update mode may be direct, deferred, deferred for an index, or deferred for a variable column. The update mode for a worktable is always direct. See the Performance and Tuning: Monitoring and Analyzing, Chapter 5, “Using set showplan,” for more information.
The target table for the data modification is displayed in this message:
TO TABLE <Table Name>
Also displayed is the I/O size used for the data modification:
Using I/O Size <N> Kbytes for data pages.
The next example uses the delete DML operator:
1> use pubs2 2> go 1> set showplan on 2> go 1> delete from authors where postalcode = '90210' 2> go QUERY PLAN FOR STATEMENT 1 (at line 1). 2 operator(s) under root The type of query is DELETE. ROOT:EMIT Operator |DELETE Operator | The update mode is direct. | | |SCAN Operator | | FROM TABLE | | authors | | Table Scan. | | Forward Scan. | | Positioning at start of table. | | Using I/O Size 4 Kbytes for data pages. | | With LRU Buffer Replacement Strategy for data pages. | | TO TABLE | authors | Using I/O Size 4 Kbytes for data pages.
