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
Get row from outer table
Get next row from inner table with same key
If found, output and loop on inner table
If not found, loop on outer table
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:
No appropriate indexes
No requirement for order of output
This method is highly applicable to ad hoc queries where indexes cant be anticipated. The basic algorithm is
Read the smaller table. Hash the key value and put key and record id into a bucket. Repeat for all the rows in the smaller table.
Read the larger table. Hash the key value and see if it is in the hash. If so, output key and both record ids. Repeat for all the rows in the larger table.
Many thanks to Sharon for providing these notes - drop her a note at email@example.com and tell her thanks!