17.6.4 Notes about join retrieval

Organization of this subsection
(1) Nested loop join using a partitioning key

(1) Nested loop join using a partitioning key

(a) Checking the display

With a HiRDB/Parallel Server, if Join Type is NESTED LOOPS JOIN and the transfer information is BROADCAST, KEY RANGE PARTIAL BROADCAST, or PARTIAL BROADCAST, you may be able to improve performance by changing the partitioning key of an inner table for nested loop join.

This is not so effective when there are not many data items in the table, but it is effective when there are many partitions.

(b) Better method

If partitioning the inner table subject to nested loop join during join retrieval, you can achieve parallel processing by defining the table in such a manner that its partitioning key becomes the joined column. Note that parallel processing is not available with flexible hash partitioning. Make sure that the transfer information is number-CLM HASH, number-CLM KEY RANGE, or 1TO1, or number-CLM MULTIDIM.

(c) Reason

If the inner table subject to nested loop join is partitioned by the key range or hash partitioning method, and the inner table's partitioning keys are all included in the joined columns, the inner table search range can be narrowed by the table partitioning conditions, thereby reducing the number of input/output operations as well as the CPU time.

(d) Example

SELECT * FROM T1, T2 WHERE T1.C1=T2.C1 and T1.C1='a'
           [Figure]
CREATE TABLE T1 (C1 INT NOT NULL, C2 INT)
   FIX HASH HASH6 BY C1 IN (USER1,USER2,USER3,USER4)
CREATE TABLE T2 (C1 INT NOT NULL, C2 INT)
   FIX HASH HASH6 BY C1 IN (USER1,USER2,USER3,USER4)

(e) Note

If the flexible hash partitioning method is used, the utility always assumes BROADCAST for the transfer information because it cannot identify the data storage location; therefore, parallel processing is not possible. If possible, you should use the FIX hash partitioning method instead of the flexible hash partitioning method. For details about these partitioning methods, see the HiRDB Version 8 Installation and Design Guide.