SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-40
Using Indexes
Using Indexes
The use of an index improves join performance by eliminating sort operations. In
situations where the fastest possible response time is required, do not specify joins
where no index exists. If you are not sure which columns are keys or indexes, check
with your database administrator.
Eliminating Implicit Joins
When tables are joined, each new row is formed by concatenating two rows, one from
each of the original tables. The paired rows must have the same value in the joining
column. You do not need to specify a join predicate for a join, but the use of a
predicate can improve the performance of the join operation.
If you specify multiple tables in the FROM clause without a search predicate, SQL
forms a Cartesian product (or cross product) by concatenating, in turn, each row of
each table with every other row of every other table. This strategy is known as an
implicit join.
A Cartesian product involving two tables, one of size M and the other of size N, is of
size M x N. If the tables are large, the performance overhead can be quite costly.
By specifying a join predicate, you can
•
Eliminate costly scans of multiple tables
•
Provide a greater choice of plans for SQL in these ways:
°
A greater number of predicates provides a greater number of ways that SQL
can evaluate possible join combinations.
°
SQL does not consider the efficient hash or merge joins unless an equality
predicate is specified for the join operations.
•
Reduce the size of the result and, consequently, the amount of work done by SQL
to produce the result
In this example, a join is created between the PARTS and ODETAIL tables. Specifying
ODETAIL O, PARTS P in the FROM clause creates an implicit join; however, because
no join column is specified, a Cartesian product is produced:
SELECT ORDERNUM
FROM ODETAIL O, PARTS P
WHERE O.PARTNUM = 5100
AND QTY_ORDERED <
(SELECT AVG(QTY_AVAILABLE)
FROM PARTS
WHERE P.PARTNUM = 5100) ;
The query returns this result:
ORDERNUM
----------