3.4.4 Joining Relations Hints

Table of Contents Previous Next


3 The SQL Language : 3.4 Optimizer Hints : 3.4.4 Joining Relations Hints

Nested Loop Join – The right table is scanned once for every row in the left table.
Merge Sort Join – Each table is sorted on the join attributes before the join starts. The two tables are then scanned in parallel and the matching rows are combined to form the join rows.
Hash Join – The right table is scanned and its join attributes are loaded into a hash table using its join attributes as hash keys. The left table is then scanned and its join attributes are used as hash keys to locate the matching rows from the right table.
USE_HASH(table [...])
NO_USE_HASH(table [...])
USE_MERGE(table [...])
NO_USE_MERGE(table [...])
USE_NL(table [...])
NO_USE_NL(table [...])
In the following example, a join is performed on the branches and accounts tables. The query plan shows that a hash join is used by creating a hash table from the join attribute of the branches table.
By using the USE_HASH(a) hint, the planner is forced to create the hash table from the accounts join attribute instead of from the branches table. Note the use of the alias, a, for the accounts table in the USE_HASH hint.
Next, the NO_USE_HASH(a b) hint forces the planner to use an approach other than hash tables. The result is a nested loop.
Finally, the USE_MERGE hint forces the planner to use a merge join.
In this three-table join example, the planner first performs a hash join on the branches and history tables, then finally performs a nested loop join of the result with the accounts_pkey index of the accounts table.

3 The SQL Language : 3.4 Optimizer Hints : 3.4.4 Joining Relations Hints

Table of Contents Previous Next