SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-17
EXPLAIN Plan for Simple SELECT
Plan step 1 involves a scan of the EMPLOYEE table. The SELECT * operation
requires that all columns (6 out of 6) be retrieved from the table. The EXPLAIN plan
includes this information:
•
The lock granularity, shown in the access type, is row (record). The access option
is stable (the default).
•
The lock mode is chosen by the system. (If the system chooses the lock mode, the
EXPLAIN plan does not specify which type. The plan simply states “Chosen by the
system.”)
•
The access path is by primary key.
•
Virtual sequential block buffering (VSBB) is used to read the table.
•
Index selectivity is 100 percent, which indicates that the optimizer estimates that
the entire table will be read.
•
Table selectivity is 100 percent, which indicates that all rows are selected from the
table.
Because the index and table selectivity are 100 percent, SQL performs a full table scan
with all rows returned. The table is small, however, so the total cost of the query is
small.
Example 6-7. EXPLAIN Plan for Simple SELECT
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Query plan 1
SQL request : Select
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
---------------------------------------------------------------------------
Plan step 1
---------------------------------------------------------------------------
Operation 1.0 : Scan
Table : \SQL1.$DATA8.PERSNL.EMPLOYEE
with correlation name EMPLOYEE
Access type : Record locks, stable access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 6 out of 6 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: 57 rows after the scan
Operation cost : 2
Total cost : 2