SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-73
EXPLAIN Plan for Cursor DELETE
The plan consists of 2 steps:
•
Plan step 1 is a scan of the EMPLOYEE table.
•
Plan step 2 is a scan of the TABLES table. Plan step 2 executes once before plan
step 1.
The type of update is a cursor update, as shown in operation 1.0. Virtual sequential
block buffering is requested by the optimizer for the update operation.
EXPLAIN Plan for Cursor DELETE
The EXPLAIN plan shows a cursor DELETE operation.
The query deletes data from the EMPLOYEE table (primary key EMPNUM). The query
follows:
EXPLAIN
DELETE
FROM EMPLOYEE
WHERE DEPTNUM = 1500;
Executor pred. : On rows retrieved by the scan
EXISTS ( .. result of plan step 2 )
Pred. selectivity : Expect to select 40% of rows from table
Table selectivity : Expect to select 40% of rows from table
Expected row count: 23 rows after the scan
Operation cost : 52
---------------------------------------------------------------------------
Plan step 2
Characteristic : Executes once before plan step 1
---------------------------------------------------------------------------
Operation 2.0 : Scan
Table : \SQL1.$DATA8.PERSNL.TABLES
with correlation name TABLES
Access type : Record locks, stable access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 0 out of 12 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. : Will be evaluated by the disk process
TABLETYPE = "VI"
Pred. selectivity : Expect to select 50% of rows from table
Executor pred. : None
Table selectivity : Expect to select 50% of rows from table
Expected row count: 11 rows after the scan
Operation cost : 2
Total cost : 54
Example 6-43. EXPLAIN Plan for Cursor UPDATE (page 2 of 2)