I know Oracle is capable of producing the UNION plan. but I don't know if
that's the only option. I'm curious what indexes the rewritten union-based
query used.
Josh Berkus <josh@agliodbs.com> writes:
> SELECT t1.a, t2.b
> FROM t1, t2
> WHERE t1.a = t2.a
> AND (
> ( t1.c = x
> AND t1.f IN (m, n, o)
> AND t2.d = v
> AND t2.e BETWEEN j AND k
> )
> OR
> ( t1.c = y
> AND t1.f IN (n, o, p)
> AND t2.d = v
> AND t2.e BETWEEN k AND h
> )
> OR
> ( t1.c = z
> AND t1.f IN (p, q)
> AND t2.d = w
> AND t2.e BETWEEN k AND h
> )
> )
In this case it seems like it might be possible to look for a covering set
that is guaranteed to include all the records and doesn't include any ORs. If
that covering set can be scanned quickly then the complex conditions could be
tested on the resulting records individually.
In this case it would be something like
select t1.a,t2.b from t1,t2 where t1.a = t2.a and ( t1.c in (x,y,z) and t1.f in (m,n,o,p,q) and t2.d in
(v,w) and t2.e between min(j,k) and max(k,h) ) and (.... the above constraints...)
It seems like it would be a lot of work and only help in narrow cases though.
--
greg