SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-44
MDAM With IN List on Key Column
MDAM With IN List on Key Column
This creates an index on LAST_NAME, FIRST_NAME. The query uses MDAM to
process an IN list for the LAST_NAME key column.
CREATE INDEX XEMPNAME
ON EMPLOYEE
(LAST_NAME, FIRST_NAME);
EXPLAIN SELECT * FROM EMPLOYEE
WHERE LAST_NAME IN ("Marks","Jones") AND FIRST_NAME = "Mary";
The plan converts the IN list for the key column LAST_NAME to a list of OR
predicates. The total cost of the query is 4.
MDAM With Multiple Predicate Sets, LIKE, and Missing Key
Column
This query uses MDAM to process the following:
•
A predicate that specifies both key columns
•
A predicate with a missing key column
•
LIKE
Example 6-28. EXPLAIN Plan for MDAM With IN List on 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: ( LAST_NAME = "Jones" OR "Marks" ) AND FIRST_NAME =
"Mary"
Index selectivity : Expect to examine 3.5088% of rows from index
Index pred. : None
Base table pred. : None
Executor pred. : None
Table selectivity : Expect to select 3.5088% of rows from table
Expected row count: 1 row after the scan
Operation cost : 4
Total cost : 4