SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-46
EXPLAIN Plan for Determining the Cost of Multiple
Predicate Sets
EXPLAIN Plan for Determining the Cost of
Multiple Predicate Sets
This query contains a predicate that SQL converts into multiple predicate sets. An
index is created for DEPTNUM and JOBCODE.
CREATE INDEX HLX ON EMPLOYEE (DEPTNUM, JOBCODE) CATALOG PERSNL ;
EXPLAIN
SELECT * FROM EMPLOYEE
WHERE ((DEPTNUM = 1500 AND JOBCODE BETWEEN 100 AND 300)
OR JOBCODE IN (400, 450))
AND ((SALARY = 50000 AND JOBCODE = 450) OR (JOBCODE IN
(500,600)
AND (DEPTNUM = 4000 OR DEPTNUM = 7000))) ;
Example 6-30. EXPLAIN Plan for Determining the Cost of Multiple Predicate
Sets (page 1 of 2)
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
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.PUBS.HLX
SBB for reads : Not used
MDAM predicate set: DEPTNUM = 1500 AND JOBCODE <= 300 AND JOBCODE >=
100 AND JOBCODE = 450
next set: DEPTNUM = 1500 AND ( DEPTNUM = 7000 OR 4000 ) AND
JOBCODE <= 300 AND JOBCODE >= 100 AND ( JOBCODE =
600 OR 500 )
next set: ( ALL DEPTNUM VALUES ) AND ( JOBCODE = 450 OR 400 )
AND JOBCODE = 450
next set: ( DEPTNUM = 7000 OR 4000 ) AND ( JOBCODE = 450 OR
400 AND ( JOBCODE = 600 OR 500 )
Index selectivity : Expect to examine 30.5861% of rows from index
Index pred. : Will be evaluated by the disk process
( ( DEPTNUM = 1500 ) AND ( JOBCODE >= 100 ) AND (
JOBCODE <= 300 ) ) OR ( JOBCODE = 400 ) OR (
JOBCODE = 450 )