SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-35
CASE for Finding the Highest Value in a Row
CASE for Finding the Highest Value in a Row
Following are the create statement and the query in Finding the Highest Value in a
Row on page 1-47. The query retrieves the highest SAT scores from each row in the
scores table.
create table scores
(name char(30),
sat1 int NOT NULL,
sat2 int NOT NULL,
primary key name);
select name, CASE
when sat1 >= sat2 then sat1
else sat2
END
from scores;
The EXPLAIN plan shows that the optimizer uses the primary key for access and
expects to examine all the rows. CASE is not mentioned in the plan. The total cost is 3.
Example 6-21. EXPLAIN Plan for CASE for Finding the Highest Value in a Row
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Query plan 1
SQL request : Select
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
---------------------------------------------------------------------------
Plan step 1
---------------------------------------------------------------------------
Operation 1.0 : Scan
Table : \SQL1.$DATA8.PUBS.SCORES
Access type : Record locks, stable access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 3 out of 3 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. : None
Executor pred. : None
Table selectivity : Expect to select 100% of rows from table
Expected row count: 90 rows after the scan
Operation cost : 3
Total cost : 3