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

Compiling and Executing a Query
HP NonStop SQL/MX Query Guide—523728-003
1-12
Improving Query Performance
For the DDL to these examples, see the LINEITEM table in Example 1-1 on
page 1-13. The primary key is L_ORDERKEY + L_LINENUMBER, and there are
indexes on L_PARTKEY (LX1) and L_SUPPKEY (LX2).
If a query on table LINEITEM contains these predicates, OR optimization might be
performed:
WHERE (L_PARTKEY = 200001 OR L_SUPPKEY = 10)
OR optimization would use index access on LX1 for the first predicate and an
index access through LX2 for the second predicate. In the absence of an index,
SQL reads the table sequentially to search for rows that satisfy the query.
Another option might be between using a single index versus using MDAM. Table
LINEITEM could have three indexes on columns (L_PARTKEY, L_SUPPKEY),
(L_SUPPKEY), (L_LINESTATUS). The query can either use all three indexes, or it
can use the index (L_PARTKEY, L_SUPPKEY) with MDAM for the first two
disjuncts and an index L_LINESTATUS for the last disjunct.
SELECT * FROM LINEITEM
WHERE L_PARTKEY=10999765 OR L_SUPPKEY=19 OR L_LINESTATUS=30
•
Examples that do not enable OR optimization
°
Indexes must exist for OR optimization to be considered. If the table,
LINEITEM, has secondary indexes on columns L_PARTKEY and L_SUPPKEY,
OR optimization is not considered for this query because there is no index on
column L_LINESTATUS:
SELECT * FROM LINEITEM
WHERE L_PARTKEY=1099987 OR L_SUPPKEY=20 OR
L_LINESTATUS='S'
°
A combination of conjuncts and disjuncts does not enable OR optimization:
SELECT * FROM LINEITEM
WHERE L_PARTKEY=10998765 OR L_SUPPKEY=20
AND L_LINESTATUS='Y'
°
Even if the indexes L_PARTKEY, L_SHIPINSTRUCT and L_SUPPKEY,
L_SHIPMODE exist on table LINEITEM, SQL/MX, unlike SQL/MP, does not
consider OR optimization for these types of queries:
SELECT * FROM LINEITEM
WHERE(L_PARTKEY=10997965 AND L_SHIPINSTRUCT='SAIL')
OR (L_SUPPKEY=20 AND L_SHIPMODE ='ANCH')