OpenSearch:Join methods

Nested-loop join

• >= Ha3 3.7.5

• When the system performs a nested-loop join on two tables, the system first finds the Cartesian product of the tables, expands and filters the results, and then performs a non-equi join.

SELECT
  t1.id, t2.id
FROM
  tj_shop AS t1
JOIN
  tj_item AS t2
ON
  t1.id > t2.id

• Note: The performance of a nested-loop join is low because data bloat occurs.

• Note: If a table that you specify for a join is a key-value table, Pkey-Skey-value table, or summary table, specify an equivalent condition based on the primary key field of the table as a query condition to query data from the table. For more information, see Query data from a key-value table or Pkey-Skey-value table.

Hash join

• You can use this join method to join values based on equivalent conditions.

• When the system performs a hash join, the system calculates hash values of data rows in the small table based on the values in the primary key field of the small table and calculates hash values of data rows in the big table based on the values in the primary key field of the big table. The system compares the hash values of the small table with the hash values of the big table, and then joins the tables based on the specified condition.

SELECT
  t1.id, t2.id
FROM
  tj_shop AS t1
JOIN
  tj_item AS t2
ON
  t1.id = t2.id

• Note: The default join method that the system uses in optimization plans generated by the Iquan component may not be a hash join method. You can use a hint to specify hash join as the default join method. For more information, see Hints.

• Note: If a table that you specify for a join is a key-value table, Pkey-Skey-value table, or summary table, specify an equivalent condition based on the primary key field of the table as a query condition to query data from the table. For more information, see Query data from a key-value table or Pkey-Skey-value table.

Lookup join

• You can use this join method to join values based on equivalent conditions.

• You can use the primary key values of a small table as conditions to query and retrieve data entries from a big table.

SELECT
  t1.id, t2.id
FROM
  tj_shop AS t1
JOIN
  tj_item AS t2
ON
  t1.id = t2.id

• Note: When you perform a lookup join to optimize a query, make sure that one of the operands in the equivalent condition that you specify is an indexed field.

• Note: You can create indexes based on fields that you specify in the equivalent condition. Havenask V3.7.5 and later support this feature.

• Note: The default join method that the system uses in optimization plans may not be a lookup join method. You can use a hint to specify lookup join as the default join method. For more information, see Hints.

• Note: If a table that you specify for a join is a key-value table, Pkey-Skey-value table, or summary table,

• specify an equivalent condition based on the primary key field of the table as a query condition to query data from the table. For more information, see Query data from a key-value table or Pkey-Skey-value table.

• You can also include the primary key field of the table in the equivalent condition based on which you want the system to perform the join operation.