SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-33
EXPLAIN Plans for CASE
EXPLAIN Plans for CASE
This subsection contains EXPLAIN plans that show how the optimizer processes
CASE.
CASE With Multiple Conditions
This query selects the last names, first names, and department numbers of employees
with job codes of 100 whose salaries are less than 100,000 and also those employees
with job codes of 600 whose salaries are less than 30,000.
EXPLAIN
SELECT LAST_NAME,FIRST_NAME,DEPTNUM FROM EMPLOYEE
WHERE SALARY < CASE JOBCODE
WHEN 100 THEN 100000
WHEN 600 THEN 30000
END ;
The plan consists of one step with one operation. The CASE expression shows as a
base table predicate that is evaluated by the disk process. The total cost is 2.
Example 6-19. EXPLAIN Plan for CASE With Multiple Conditions
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
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 3 out of 6 columns
Access path 1 : Primary
SBB for reads : Virtual
Begin key pred. : None
End key pred. : None
Index selectivity : Expect to examine 100% of rows from table
Index pred. : None
Base table pred. : Will be evaluated by the disk process
SALARY < CASE WHEN JOBCODE = 100 THEN 100000 WHEN
JOBCODE = 600 THEN 30000 END
Pred. selectivity : Expect to select 33.33% of rows from table
Executor pred. : None
Table selectivity : Expect to select 33.33% of rows from table
Expected row count: 19 rows after the scan
Operation cost : 2
Total cost : 2