SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-76
EXPLAIN Plan for INSERT-SELECT
If you are using INSERT-SELECT to populate a table from another table, be aware that
the LOAD command performs this task more efficiently. The next most efficient way is
to use an INSERT-SELECT statement on tables that are audited with sequential block
buffering on. If the output table is unaudited and uses sequential block buffering, the
performance decreases measurably.
The LOAD command and the INSERT-SELECT statement differ in the ways they write
to the target table. The LOAD command writes in blocks and the INSERT-SELECT
statement writes one row at a time.
For more information on the LOAD command and the INSERT-SELECT statement,
see the SQL/MP Reference Manual.
Example 6-46. EXPLAIN Plan for INSERT With SELECT
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Query plan 1
SQL request : Insert-Select
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
---------------------------------------------------------------------------
Plan step 1
---------------------------------------------------------------------------
Operation 1.0 : Scan
Table : \SQL1.$DATA7.REG1.ORD2
with correlation name ORDTWO
Access type : Record locks, stable access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 1 out of 2 columns
Access path 1 : Primary
SBB for reads : Virtual
Begin key pred. : None
End key pred. : None
Index selectivity : Expect to examine 100% of rows from table
Index pred. : None
Base table pred. : None
Executor pred. : None
Table selectivity : Expect to select 100% of rows from table
Expected row count: 1 row after the scan
Operation cost : 1
Operation 1.1 : Insert
Table : \SQL1.$DATA7.REG1.ORD1
Access path : Primary
SBB for Insert : Requested by the optimizer
Insert Cost : 1
Total cost : 2