Re: [jira] Updated: (DERBY-649) Useful indexes not used in UNION ALL

[Jeffrey Lichtman <sw...@rcn.com>]

I haven't had the time to go over this patch in detail, but I do have 
a basic question and some comments:

Is the pushing of expressions into the union done before optimization 
or during optimization? If it's done before optimization, then only 
single-table expressions involving the union are pushable. But if 
it's done during optimization, it would be possible to push joins 
into the union as long as it's in the right place in the join order.

For example:

create view v as select * from t1 union all select * from t2;

select * from v, t3 where v.c1 = t3.c2;

In this select, if t3 is the outer table then the qualification could 
be pushed into the union and optimized there, but if t3 is the inner 
table the qualification can't be pushed into the union.

If the pushing is done at preprocess time (i.e. before optimization) 
it is impossible to know whether a join qualification like this can 
be safely pushed.

There's a comment in UnionNode.optimizeIt() saying:

/* RESOLVE - don't try to push predicated through for now */

This is where I'd expect to see something for pushing predicates into 
the union during optimization.

BTW, the business of pushing and pulling predicates during 
optimization can be hard to understand and debug, so maybe it's best 
to only handle the simple cases and do it during preprocessing.

Another question: should this fix be limited to unions? What about 
other set operations like INTERSECT?


                        -        Jeff Lichtman
                                 swazoo@rcn.com
                                 Check out Swazoo Koolak's Web Jukebox at
                                 http://swazoo.com/