Consider example:
create table t1 (id int);
create table t2 (id int);
create table t3 (id int);
commit;
set plan;
select *
from t1, t2, t3
where t1.id = t2.id and t2.id = t3.id;
-- PLAN HASH (T1 NATURAL, T2 NATURAL, T3 NATURAL)
i.e. all three streams are joined in one pass.
Now let's replace joined fields with expressions:
select *
from t1, t2, t3
where t1.id+0 = t2.id+0 and t2.id+0 = t3.id+0;
-- PLAN HASH (HASH (T1 NATURAL, T2 NATURAL), T3 NATURAL)
i.e. two joins are used instead of one.
Consider example:
i.e. all three streams are joined in one pass.
Now let's replace joined fields with expressions:
i.e. two joins are used instead of one.