[DERBY-772] Handle pushing IN predicates into UNIONs. This would allow use of index if present improving performance.

• Type: Sub-task
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.0.2.0, 10.1.2.1, 10.2.1.6
• Fix Version/s: 10.1.3.1, 10.2.1.6
• Component/s: SQL
• Environment: generic
• Assignee: Unassigned
• Reporter: Satheesh E. Bandaram
• Created: 18/Dec/05 07:01
• Updated: 25/Apr/06 03:41
• Resolved: 25/Apr/06 03:41

Description

While fix for DERBY-649 allows pushing binary predicates into SELECT statements that are underneath a UNOIN, it would also be good to allow IN operations to be pushed. Derby can rewrite IN operations in a way to allow use of index, if present.

Derby currently rewrites: REF IN (1, 2, 3) to REF >=1 and REF <=3 and hence would allow use of index on REF, if present. Pushing this predicate into Union would allow use of index on REF for queries like:

Select * from
(SELECT REF, NAME from T1 UNION ALL SELECT REF, NAME from t2)
where REF IN (1,2,3)