SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-47
EXPLAIN Plan for Selectivity for Range Predicates
SQL converts the predicates into four MDAM predicate sets. In the first set, a
positioning takes place for each unique JOBCODE value between 100 and 300.
In following predicate sets, SQL converts IN lists for JOBCODE values into OR
predicates. A positioning is done on each of the equal predicates for JOBCODE. The
total cost for the query is 48.
EXPLAIN Plan for Selectivity for Range
Predicates
This query contains range predicates:
UPDATE STATISTICS FOR TABLE EMPLOYEE;
EXPLAIN
SELECT * FROM EMPLOYEE
WHERE JOBCODE >= 150
AND JOBCODE <= 500 ;
Example 6-31 on page 6-48 shows the selectivity.
Pred. selectivity : Expect to select 30.5861% of rows from index
Base table pred. : Will be evaluated by the disk process
( ( SALARY = 50000 ) AND ( JOBCODE = 450 ) ) OR ( (
( JOBCODE = 500 ) OR ( JOBCODE = 600 ) ) AND ( (
DEPTNUM = 4000 ) OR ( DEPTNUM = 7000 ) ) )
Pred. selectivity : Expect to select 30.5861% of rows from table
Executor pred. : None
Table selectivity : Expect to select 31.3476% of rows from table
Expected row count: 1 row after the scan
Operation cost : 48
Total cost : 48
Example 6-30. EXPLAIN Plan for Determining the Cost of Multiple Predicate
Sets (page 2 of 2)