Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 26, 1998

Tools (Cont'd) - Page 7

By Sharon Dooley

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

Multi-Index operations

Merge join

Hash join

Hash aggregation

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 sharond@compuserve.com and tell her thanks!


MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM