Description
Is your feature request related to a problem? Please describe.
If the where expression contains consecutive or clauses, the predicate can only be executed in higher-level operators. such as
select * from a, b where (1<a.k1<3 and b.k1 in ('a')) or (2<a.k1<4 and b.k1 in ('b'))
Because the two or clauses of where involve two tables respectively. So the expression can only be calculated in the join operator. This will cause the query to scan all tables a and b.
In fact, although these two clauses do not have a common factor, we can extract a wider range to filter the data in tables a and b in advance. Reduce the number of filtered rows.
Finally, perform precise filtering on the join operator.
such as:
For a.k1, the ranges in the two clauses can be combined to obtain:
1<a.k1<4, in this way, table a can be filtered in advance to reduce the amount of join data in table a b.
After rewriting, the query looks like:
select * from a,b where (1<a.k1<4) and (b.k1 in('a','b')) and (1<a.k1<3 and b.k1 in ('a' )) or (2<a.k1<4 and b.k1 in ('b'))
Activity