SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-38
CASE for Ignoring the Largest and Smallest Values
in a Set
CASE for Ignoring the Largest and Smallest Values in a Set
Following are the create statement and the query in Ignoring the Largest and Smallest
Values in a Set on page 1-51.
create table data
(value int NOT NULL,
primary key value);
select x.value
from data x, data y
group by x.value
having SUM (CASE when y.value <= x.value then 1 else 0 END) > 1
AND SUM (CASE when y.value >= x.value then 1 else 0 END) > 1;
Example 6-23. EXPLAIN Plan for Ignoring the Largest and Smallest Values in a
Set (page 1 of 2)
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Query plan 1
SQL request : Select
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
---------------------------------------------------------------------------
Plan step 1
---------------------------------------------------------------------------
Operation 1.0 : Scan
Table : \SQL1.$DATA8.PUBS.DATA
with correlation name X
Access type : Record locks, stable access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 1 out of 1 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. : From the Having clause
( SUM ( CASE WHEN Y.VALUE <= X.VALUE THEN 1 ELSE 0
END
) > 1 ) AND ( SUM ( CASE WHEN Y.VALUE >= X.VALUE THEN
1
ELSE 0 END ) > 1 )
Executor aggr. : Computed for each group
SUM ( CASE WHEN Y.VALUE <= X.VALUE THEN 1 ELSE 0
END )
SUM ( CASE WHEN Y.VALUE >= X.VALUE THEN 1 ELS 0
END )
Table selectivity : Expect to select 100% of rows from table
Expected row count: 204 rows after the scan
Expected row count: 100 rows after the having
Operation cost : 6