SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-34
CASE With Aggregates
CASE With Aggregates
This create statement and query appear under Computing Aggregates Based on
Specific Conditions on page 1-45.
create table emp
(name char(8),
age smallint NOT NULL,
dept int,
cars smallint NOT NULL,
primary key name);
select SUM(CASE when cars = 0 then 1 else 0 END),
SUM(CASE when cars = 1 then 1 else 0 END),
SUM(CASE when cars between 2 and 3 then 1 else 0 END),
SUM(CASE when cars > 3 then 1 else 0 END)
from emp;
The plan is one step with one operation. Access is by primary key. The optimizer uses
DP2 aggregates to compute each group. The total cost is 3.
Example 6-20. EXPLAIN Plan for CASE With Aggregates
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Query plan 1
SQL request : Select
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
---------------------------------------------------------------------------
Plan step 1
---------------------------------------------------------------------------
Operation 1.0 : Scan
Table : \SQL1.$DATA8.PUBS.EMP
Access type : Record locks, stable access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 1 out of 4 columns
Access path 1 : Primary
SBB for reads : Not used
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
DP2 aggregate : Computed for each group
SUM ( CASE WHEN CARS = 0 THEN 1 ELSE 0 END )
SUM ( CASE WHEN CARS = 1 THEN 1 ELSE 0 END )
SUM ( CASE WHEN ( CARS >= 2 ) AND ( CARS <= 3 )
THEN 1
ELSE 0 END )
SUM ( CASE WHEN CARS > 3 THEN 1 ELSE 0 END )
Table selectivity : Expect to select 100% of rows from table
Expected row count: 81 rows after the scan
Operation cost : 3
Total cost : 3