SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-45
MDAM With Multiple Predicate Sets, LIKE, and
Missing Key Column
An index is created on LAST_NAME, FIRST_NAME.
CREATE INDEX XEMPNAME
ON EMPLOYEE
(LAST_NAME, FIRST_NAME);
EXPLAIN SELECT * FROM EMPLOYEE
WHERE ((LAST_NAME = "Marks" OR LAST_NAME = "Jones") AND
FIRST_NAME = "Mary")
OR
(FIRST_NAME LIKE "Ha%");
SQL converts the predicates to two MDAM predicate sets.
In the first predicate set, both key columns are specified. In the second predicate set,
the leading key column (LAST_NAME) is missing. SQL finds all values for this column.
Duplicate key predicates and contradictory key predicates are eliminated at run time.
SQL converts the LIKE to a range.
Example 6-29. EXPLAIN Plan for MDAM With Multiple Predicate Sets, LIKE, and
Missing Leading 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"
next set: ( ALL LAST_NAME VALUES ) AND FIRST_NAME < "Hb" AND
FIRST_NAME >= "Ha"
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: 13 rows after the scan
Operation cost : 173
Total cost : 173