Hi, Note that this will negate the optimization that one usually is looking for when writing such queries:
Select * from TAB where a = 1 UNION ALL Select * from TAB where b = 1 In a database with indexes (most databases) this will allow indexes to be used on both the a column and the b column. Databases with bitmap indexes or without indexes would benefit from the rule. On Wed, Jan 19, 2022 at 4:32 PM Julian Hyde <jhyde.apa...@gmail.com> wrote: > Can you log a Jira case for this? > > I think you should make your rule work for N-way Union, not just 2-way > Union. And I think you should make it work whether or not a Project is > present. > > > On Jan 19, 2022, at 1:25 PM, Julian Hyde <jhyde.apa...@gmail.com> wrote: > > > > It sounds useful. > > > > What do you think the rule should be called? UnionFilterTransposeRule, > perhaps? > > > > A challenge when writing the rule will be to ensure that all of the > inputs to the Union are the same. The Volcano framework is not very good at > that. > > > > You should be careful of the case that the conditions overlap. For > example, the rewrite > > > > SELECT * FROM Emp WHERE deptno < 30 > > UNION ALL > > SELECT * FROM Emp WHERE deptno IN (25, 35, 45) > > > > to > > > > SELECT * FROM Emp WHERE deptno < 30 OR deptno IN (25, 35, 45) > > > > Is not valid, because rows with deptno = 25 will appear twice in the > first query, once in the second. Maybe that problem does not occur when > applied to UNION than when applied to UNION ALL. > > > > There would seem to be analogous rules for INTERSECT (combine the > conditions using AND) and EXCEPT (combine the conditions using AND NOT). > Perhaps one rule could cover all set operations (see > FilterSetOpTransposeRule). > > > > Julian > > > > > > > >> On Jan 19, 2022, at 2:38 AM, Yanjing Wang <zhuangzixiao...@gmail.com > <mailto:zhuangzixiao...@gmail.com>> wrote: > >> > >> A simple example is converting SELECT a, b FROM t WHERE c = 1 UNION ALL > SELECT a, b FROM t WHERE c = 2 to SELECT a, b FROM t WHERE c in (1, 2) > >> > >> Yanjing Wang <zhuangzixiao...@gmail.com <mailto: > zhuangzixiao...@gmail.com>> 于2022年1月19日周三 18:35写道： > >> Hi, community > >> > >> Here I recommend a new rule for converting UNION ALL sub plan to a > single input with an OR filter, the following is its conversion diagram. > >> > >> > >> The conversion prerequisites are > >> 1. left filter range has no intersection with right filter range. > >> 2. Project and Input Sub Tree must be identical. > >> > >> The rule will be used when Input Sub Tree is a computing-intensive or > large IO operation. > >> > >> I don't know whether the community supports it or not, any suggestions > will be appreciated. > >> > >> > > > >
