Brannon King <brannonking-/[EMAIL PROTECTED]>
wrote:
I would think this would be a valuable optimization for Sqlite to
perform automatically. When it finds an OR operation, it should look
to see if duplicating the query and using the UNION ALL compounder
would allow it to use additional indexes and if so then duplicate it.
It really can't. If the table happens to have rows with x=1 and y=1,
"union all" reformulation would report such rows twice, which would be a
bug. Perhaps something like this would work:
select * from vals2d where x=1
union all
select * from vals2d where y=1 and x != 1
but it's a pretty complicated transformation and would probably be
difficult to generalize beyond simplest cases. Imagine a complex logical
expression with many ANDs, ORs and NOTs. One would have to rewrite it
into disjunctive normal form, which could potentially make it grow
exponentially. Consider:
(A1 or B1) and (A2 or B2) and ... and (An or Bn)
where Ai and Bi are conditions.
An advanced optimization like this is probably better left to the
programmer.
Igor Tandetnik
