Enhancement
See the case below, the best join order is joining t1 and t2 first, but both our join-order and leading can't support it:
create table t1(id int not null primary key,name varchar(100));
create table t2 like t1;
create table t3 like t1;
create table t4 like t1;
explain select /*+ leading(t1, t2) */ * from t1 inner join t3 on t1.id=t3.id left join t4 on t4.id=t3.id join t2 on t1.id=t2.id where t3.name like 'test3' or t4.name like 'test4';
The root cause is that the predicate t3 like or t4 like splits the whole query into 2 join groups, and both our join-order and leading can't support changing join orders of 2 tables across 2 different join groups:
One solution might be we can pull this predicate up first, and then we can merge these 2 join groups in to 1 and join t1 and t2 first:
Enhancement
See the case below, the best join order is joining
t1andt2first, but both our join-order andleadingcan't support it:The root cause is that the predicate
t3 like or t4 likesplits the whole query into 2 join groups, and both our join-order andleadingcan't support changing join orders of 2 tables across 2 different join groups:One solution might be we can pull this predicate up first, and then we can merge these 2 join groups in to 1 and join
t1andt2first: