SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-43
MDAM with Missing First Key Column
MDAM with Missing First Key Column
This creates an index on LAST_NAME, FIRST_NAME. The first key column is missing
from the query predicate.
CREATE INDEX XEMPNAME
ON EMPLOYEE
(LAST_NAME, FIRST_NAME);
EXPLAIN SELECT * FROM EMPLOYEE WHERE FIRST_NAME = "Mary";
Although the LAST_NAME column is missing from the query, the MDAM predicate set
shows that SQL considers all LAST_NAME values. It finds all the values for
LAST_NAME and uses them with the specified value for FIRST_NAME to retrieve only
the qualifying rows. The total cost is 169.
Example 6-27. EXPLAIN Plan for MDAM With Missing First Key Column
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
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 : Alternate \SQL1.$DATA8.PERSNL.XEMPNAME
SBB for reads : Virtual
MDAM predicate set: ( ALL LAST_NAME VALUES ) AND FIRST_NAME = "Mary"
Index selectivity : Expect to examine 96.4912% of rows from index
Index pred. : None
Base table pred. : None
Executor pred. : None
Table selectivity : Expect to select 96.4912% of rows from table
Expected row count: 1 row after the scan
Operation cost : 169
Total cost : 169