SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-80
Comparing Cost: A Scenario
WHERE INT0_DTOF6_UNIQ >= INTERVAL '0' DAY(3) AND
INT0_DTOF6_UNIQ <= INTERVAL '2' DAY(3) ;
Comparing Cost: A Scenario
The next two query examples show how you can reformulate a query and produce the
same result but with much improved performance. The DISPLAY STATISTICS and
EXPLAIN plans show you the results.
The first formulation has an estimated cost of 50. The second formulation has an
estimated cost of 4.
First Formulation
This statement prepares the first formulation of the query:
PREPARE QUERY1 FROM
SELECT DISTINCT ORDERNUM
FROM ODETAIL O, PARTS P
WHERE O.PARTNUM = 5100
AND QTY_ORDERED <
(SELECT AVG(QTY_AVAILABLE)
FROM PARTS
WHERE P.PARTNUM = 5100) ;
Example 6-49. EXPLAIN Plan With DAY Date-Time Values
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Query plan 1
SQL request : Select
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
---------------------------------------------------------------------------
Plan step 1
---------------------------------------------------------------------------
Operation 1.0 : Scan
Table : \SQL1.$DATA5.SQLDOPTS.B2UNL13
with correlation name B2UNL13
Access type : Record locks, stable access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 1 out of 10 columns
Access path 1 : Primary
SBB for reads : Virtual
Begin key pred. : INT0_DTOF6_UNIQ >= INTERVAL ' 00' DAY ( 3 ) TO DAY
End key pred. : INT0_DTOF6_UNIQ <= INTERVAL ' 02' DAY ( 3 ) TO DAY
Index selectivity : Expect to examine 0.0012% of rows from table
Index pred. : None
Base table pred. : None
Executor pred. : None
Table selectivity : Expect to select 0.0012% of rows from table
Expected row count: 2 rows after the scan
Operation cost : 3
Total cost : 3