set showplan uses the update index operator to display parallel update index statements.
set showplan on set ins_by_bulk on go insert into my_authors select * from authors
This example showplan output is for an insert-select into a table with several indexes, executed with the data load optimizations. The text shown in bold reports the parallel index update enhancements.
QUERY PLAN FOR STATEMENT 1 (at line 2).
Optimized using Parallel Mode
Executed in parallel by coordinating process and 2 worker processes.
STEP 1
The type of query is INSERT.
5 operator(s) under root
|ROOT:EMIT Operator (VA = 5)
|
| | INSERT Operator (VA = 4)
| | The update mode is direct.
| |
| | |SCAN Operator (VA = 0)
| | | FROM TABLE
| | | authors
| | | Table Scan.
| | | Forward Scan.
| | | Positioning at start of table.
| | | Using I/O Size 16 Kbytes for data pages.
| | | With LRU Buffer Replacement Strategy for data pages.
| |
| | |EXCHANGE Operator (VA = 3) (Merged)
| | |Executed in parallel by 2 Producer and 1 Consumer processes.
| | |
| | | |EXCHANGE:EMIT Operator (VA = 2)
| | | |
| | | | |UPDATE INDEX Operator (VA = 1)
| |
| | TO TABLE
| | my_authors
| | Using I/O Size 16 Kbytes for data pages.
The INSERT OPERATOR (VA = 4) is using BULK INSERT
(23 rows affected)
Without these data load enhancements, the data insertion is done in serial mode, with all the indexes being updated sequentially after the insertion of each row. With the enhanced data load optimizations, data pages are populated in bulk mode by one insert thread, followed by index updates which are performed by multiple threads each updating one or more indexes for all the data rows inserted on one page.
The line:
The INSERT OPERATOR (VA = 4) is using BULK INSERT
shows that bulk inserts are being performed.
The plan fragment UPDATE INDEX Operator (VA = 1) shows that the index updates are being performed using parallel worker threads.