SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-42
EXPLAIN Plans for MDAM
EXPLAIN Plans for MDAM
These examples show how the optimizer uses MDAM.
MDAM With OR and Equality Predicate on Second Key Column
In this example, an index is created on LAST_NAME, FIRST_NAME. The query
specifies an OR on the first key column (LAST_NAME) and an equality predicate on
the second key column (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";
The OR and the equality predicates show in the MDAM predicate set. The total cost is
4.
Example 6-26. EXPLAIN Plan for MDAM With OR and Equality Predicate on
Second 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