Table Joins

The impact of Join Condition on Table Join

The join condition here mainly means whether there is any valid or proper index on the join columns, which is very important for optimizer to generate an efficient execution plan.

Both sides of the Join Condition are valid

Under this circumstance, there are proper or valid indexes created on two sides of the join columns. In this case, each of the two tables can be the “driving” table and will not yield bad execution plan under most of circumstances.
However, bear in mind that to get the best performance, we need to filter as much as possible data volume before joining two tables. That’s to say, we need to choose the table that can filter more data to be the driving table.
If the optimizer chooses the wrong join order, we can instruct the optimizer to take the right join order by taking advantage of some hints (like ORDERED) or rewrite the SQL statement.
For example, suppose there are indexes created on the tab2(fld2+key2) and tab1(fld1+key1) and we know make the table tab2 as the driving table will be better, we can write the following SQL statement to make the optimizer to follow our intents,

SELECT a.*, b.* FROM tab2 b, tab1 a WHERE a.key1 = b.key2 AND b .fld2 like 'ABC%'’ AND RTRIM (a.fld1) = ‘10’;
SELECT /*+ordered*/ a.*, b.* FROM tab2 b, tab1 a WHERE a.key1 = b.key2 AND b.fld2 like 'ABC%' AND a.fld1 = '10';

One side of the join condition is invalid

Under this circumstance, only one join column is indexed. In this case, the join order is very important. Generally, the table that has join column indexed should be inner table if uses the NESTED LOOP joins, or just uses the SORT MERGE JOIN or HASH JOIN which doesn’t use indexes.

Neither side of the join condition is valid

Under these circumstances, both sides of the join columns are not indexed. As a result, the NESTED LOOP JOIN will not be a good performer, and the SORT MERGE JOIN or HASH JOIN will be a better choice.