For a narrow subset of queries with OR operations, NonStop SQL/MX uses a feature called OR optimization. OR optimization uses more than one access path to obtain the data and eliminates duplicate predicates to produce the result. In many cases, OR optimization results in the query running significantly faster. While partitioning is not required for OR optimization benefits to take effect, performance benefits might be further enhanced with partitioned tables.
•
OR optimization and application exampleOR optimization might be useful in querying large transaction tables where the expected output is only a few rows.
•
MDAM and OR optimizationMDAM is another feature that works with OR operators. It provides some benefits that the OR optimization feature does not:
°
MDAM eliminates duplicate key values in OR predicates at run time. It does so before NonStop SQL/MX accesses any tables, so there is no performance penalty.°
MDAM can process multiple tables in a query. It can be used on the inner and outer tables of nested joins and on the outer tables of sort merge, hash, and key-sequenced merge joins.°
WHERE clauses need not be in disjunctive normal form.MDAM is enabled by default.
•
Choosing optimized OR plansSQL might use an optimized OR plan when all these conditions are satisfied:
°
Two or more search conditions connected by OR operators.°
Each search condition contains predicates used as keys on an index. That is, the predicates involve columns that belong to the key prefix of an index. Note. OR optimization in NonStop SQL/MX has a more restrictive set of conditions than its NonStop SQL/MP counterpart.Note. A search condition in disjunctive normal form has only one level of OR operations; for example, (P1 and P2) or (P4 and P5) or (P6 and P7 and P8). A search condition not in normal disjunctive form can be transformed into one. For example, (A or B) and C can be changed to (A and C) or (B and C). A search condition in conjunctive normal form has one level of AND operations; for example, P1 and P2 and P3.
Compiling and Executing a Query
HP NonStop SQL/MX Query Guide —640323-001 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 EXPLAIN statement 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 optimizationIn 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 ANDoperators—contains an executor predicate, which are evaluated for each row.
•
Examples of OR optimizationNote. 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. EXPLAIN statement 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
Compiling and Executing a Query
HP NonStop SQL/MX Query Guide —640323-001 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, NonStop SQL/MX, unlike NonStop 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')
Compiling and Executing a Query
HP NonStop SQL/MX Query Guide —640323-001 1-13
Improving Query Performance
Example 1-1. OR Optimization DDL
CREATE TABLE LINEITEM
( L_ORDERKEY INT NOT NULL , L_PARTKEY INT NOT NULL , L_SUPPKEY INT NOT NULL , L_LINENUMBER INT NOT NULL , L_QUANTITY NUMERIC(12,2) NOT NULL , L_EXTENDEDPRICE NUMERIC(12,2) NOT NULL , L_DISCOUNT NUMERIC(12,2) NOT NULL , L_TAX NUMERIC(12,2) NOT NULL , L_RETURNFLAG CHAR(1) NOT NULL , L_LINESTATUS CHAR(1) NOT NULL , L_SHIPDATE DATE NOT NULL , L_COMMITDATE DATE NOT NULL , L_RECEIPTDATE DATE NOT NULL , L_SHIPINSTRUCT CHAR(25) NOT NULL , L_SHIPMODE CHAR(10) NOT NULL , L_COMMENT VARCHAR(44) NOT NULL , PRIMARY KEY (L_ORDERKEY, L_LINENUMBER))
STORE BY PRIMARY KEY PARTITION (
ADD FIRST KEY (100001) LOCATION $DATA08
NAME PARTN1
EXTENT (1024, 1024) MAXEXTENTS 512 ,ADD FIRST KEY (200001)
LOCATION $DATA07 NAME PARTN2
EXTENT (1024, 1024) MAXEXTENTS 512 ,ADD FIRST KEY (300001)
LOCATION $DATA06 NAME PARTN3
EXTENT (1024, 1024) MAXEXTENTS 512 );
CREATE INDEX LX1 ON LINEITEM (L_PARTKEY)
LOCATION $DATA09
ATTRIBUTE EXTENT (1024, 1024) MAXEXTENTS 512 PARTITION
( ADD FIRST KEY (100001) LOCATION $DATA08
,ADD FIRST KEY (200001) LOCATION $DATA07
,ADD FIRST KEY (300001) LOCATION $DATA06
);
CREATE INDEX LX2 ON LINEITEM (L_SUPPKEY)
LOCATION $DATA09
ATTRIBUTE EXTENT (1024, 1024) MAXEXTENTS 512 PARTITION
( ADD FIRST KEY (2500) LOCATION $DATA08 ,ADD FIRST KEY (5000) LOCATION $DATA07 ,ADD FIRST KEY (7500) LOCATION $DATA06 );
Compiling and Executing a Query
HP NonStop SQL/MX Query Guide —640323-001 1-14
Improving Query Performance