SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Compiling and Executing a Query
HP NonStop SQL/MX Query Guide—523728-003
1-11
Improving Query Performance
°
The search condition is restricted to simple OR predicates:
L_SUPPKEY = 2407 OR L_PARTKEY > 34567
This query uses the DDL shown in Example 1-1 on page 1-13. The
DISPLAY_EXPLAIN OPTIONS ‘f’ output is shown in Figure 1-1 below.
PREPARE XXZ FROM SELECT * FROM LINEITEM
WHERE L_SUPPKEY = 2407 OR L_PARTKEY = 34567;
Additional indexes might enable OR optimization for additional columns.
•
Plans that do not use OR optimization
In general, OR optimization is not used when:
°
A query involves more than one table.
°
Columns in the predicate are not part of a key prefix.
°
The search condition includes an AND operator.
°
At least one single predicate—or set of predicates connected by AND
operators—contains an executor predicate, which are evaluated for each row.
•
Examples of OR optimization
Note. For execution plans that use OR optimization, the optimizer considers index-only
access in addition to index-key lookup through a nested join. Index-only access, however,
can be used only if the index contains all the columns referenced in the query.
Figure 1-1. DISPLAY_EXPLAIN OPTIONS ‘f’ Output for Nested Join With Indexes
LX1 and LX2 From LINEITEM Table
LC RC OP OPERATOR OPT DESCRIPTION CARD
--- --- --- -------- --- ----------- ----
15 . 16 root 4.30E+1
7 14 15 merge_union 1.70E+1
10 13 14 nested_join 1.40E+1
12 . 13 split_top 1:4(logph) 3.49E-1
11 . 12 partition_access 3.49E-1
. . 11 file_scan_unique fr ORCAT.LINEITEM(s) 3.49E-1
9 . 10 split_top 1:4(logph) 4.00E+1
8 . 9 partition_access 4.00E+1
. . 8 index_scan fs fr ORCAT.LX2(s) 4.00E+1
3 6 7 nested_join 3.00E+0
5 . 6 split_top 1:4(logph) 1.00E+0
4 . 5 partition_access 1.00E+0
. . 4 file_scan_unique fr ORCAT.LINEITEM(s) 1.00E+0
2 . 3 split_top 1:4(logph) 3.00E+0
1 . 2 partition_access 3.00E+0
. . 1 index_scan fs fr ORCAT.LX1(s) 3.00E+0