Tools (Cont'd) - Page 7

25 Jan 98

Bulk load processing is also a part of the query engine and has greatly improved performance.

There are new query processing techniques. These include

Single index processing is still used and is still an important technique.

Multi-index processing allows several indexes to be used to resolve one query, even on a single table. The example provided was an order table. The query is "SELECT * FROM orders WHERE cust_id = 987 and order value >= 10000. With an index on cust_id and an index on order_value, the QP will search the cust_id index for rows that match 987. Then it will search the order_value index for rows that are >= 10000. It will intersect the two sets of row ids to get the desired result set for the query. It also uses this technique to create the equivalent of a covering index from existing indexes which themselves do not cover the query. It also uses this for an "on the fly" join index.

The merge join in the new release is not the "pseudo merge-join" of 6.5. It is used when inputs are sorted, typically when the user wants the results in the same order as the indexes. Processing strategy is

In this pseudo code, it looks like the current nested iteration, but it is done in a single pass rather than in multiple passes so it will be faster.

Hash joins are used when there are not ordered inputs:

This method is highly applicable to ad hoc queries where indexes cant be anticipated. The basic algorithm is