The join plan
When a query contains more than one table, Informix® joins the tables using filters in the query. The way that the optimizer chooses to join the tables is the join plan.
In the following query, the customer and orders table are joined
by the
customer.customer_num = orders.customer_num filter:
SELECT * from customer, orders
WHERE customer.customer_num = orders.customer_num
AND customer.lname = "Higgins";The join method can be a nested-loop join or a hash join.
Because of the nature of hash joins, an application with isolation level set to Repeatable Read might temporarily lock all the records in tables that are involved in the join, including records that fail to qualify the join. This situation leads to decreased concurrency among connections. Conversely, nested-loop joins lock fewer records but provide reduced performance when a large number of rows are accessed. Thus, each join method has advantages and disadvantages.