SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-37
CASE for Converting Long, Narrow Tables Into
Short, Wide Ones
The plan contains one step with one operation. The optimizer uses the primary key to
access the table and uses DP2 aggregates to compute the name groups. The total
cost is 3.
Example 6-22. EXPLAIN Plan for Converting Long, Narrow Tables Into Short,
Wide Ones
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Query plan 1
SQL request : Select
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
---------------------------------------------------------------------------
Plan step 1
---------------------------------------------------------------------------
Operation 1.0 : Scan
Table : \SQL1.$DATA8.PUBS.BONUS
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 : 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 MONTH = 1 THEN AMOUNT ELSE 0 END )
SUM ( CASE WHEN MONTH = 2 THEN AMOUNT ELSE 0 END )
SUM ( CASE WHEN MONTH = 3 THEN AMOUNT ELSE 0 END )
SUM ( CASE WHEN MONTH = 4 THEN AMOUNT ELSE 0 END )
SUM ( CASE WHEN MONTH = 5 THEN AMOUNT ELSE 0 END )
SUM ( CASE WHEN MONTH = 6 THEN AMOUNT ELSE 0 END )
SUM ( CASE WHEN MONTH = 7 THEN AMOUNT ELSE 0 END )
SUM ( CASE WHEN MONTH = 8 THEN AMOUNT ELSE 0 END )
SUM ( CASE WHEN MONTH = 9 THEN AMOUNT ELSE 0 END )
SUM ( CASE WHEN MONTH = 10 THEN AMOUNT ELSE 0 END )
SUM ( CASE WHEN MONTH = 11 THEN AMOUNT ELSE 0 END )
SUM ( CASE WHEN MONTH = 12 THEN AMOUNT 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