7.3TC7 Optimizer foibles
I am playing with queries of the form
select t0.a, t0.b, t0.c
from mytable t0, mytable t1
where t0.x = 'A' or t0.x = 'B' or (t0.x = 'C' and t1.x = 'D')
Once I add the last term I seem to be unable to coax the use of the
index on the t0.x attribute out of the optimizer.
Anybody have any good insights into this problem, ways to rewrite the
query to avoid this problem, or ideas about other versions of the
software which might work better?
Cheers
Jay,
You're getting a partial Cartesian product. For every row in t1,
every row in t0 is being joined. You have forgotten to join t1 & t0 (i.e
t1.col = t0.col). I think what you want is a UNION.
Terry Hillick
CSCSi
Jay Walters wrote:
> I am playing with queries of the form
>
> select t0.a, t0.b, t0.c
> from mytable t0, mytable t1
> where t0.x = 'A' or t0.x = 'B' or (t0.x = 'C' and t1.x = 'D')>
> Once I add the last term I seem to be unable to coax the use of the
> index on the t0.x attribute out of the optimizer.
>
> Anybody have any good insights into this problem, ways to rewrite the
> query to avoid this problem, or ideas about other versions of the
> software which might work better?
>
> Cheers
Sorry on the SQL, I transcribed it wrong, there was a cartesian product
problem around the or terms, but not on the self-join.
I called customer support and they told me maybe UDS would handle complex
boolean expressions ((A&&B)||C||D doesn't seem complex to me).
Now I am trying
select t0.b, count(distinct t0.a)
from mytable t0, mytable t1
where (t0.a = t1.a) and
(((t0.x = 'C' or t0.x = 'D') and t0.x = t1.x) or (t0.x = 'A' and
t1.x = 'B'))
group by t0.b;
Even with optimizer directives it will not use indexes. This statement costs
out at 1108003072. If we eliminate one term from the or, for example t0.x
= 'B' then it will use indexes.
ORACLE has no problems using indexes without hints/directives.
thillick wrote:
> Jay,
>
> You're getting a partial Cartesian product. For every row in t1,
> every row in t0 is being joined. You have forgotten to join t1 & t0 (i.e
> t1.col = t0.col). I think what you want is a UNION.
>
> Terry Hillick
> CSCSi
>
> Jay Walters wrote:
>
> > I am playing with queries of the form
> >
> > select t0.a, t0.b, t0.c
> > from mytable t0, mytable t1
> > where t0.x = 'A' or t0.x = 'B' or (t0.x = 'C' and t1.x = 'D')> >
> > Once I add the last term I seem to be unable to coax the use of the
> > index on the t0.x attribute out of the optimizer.
> >
> > Anybody have any good insights into this problem, ways to rewrite the
> > query to avoid this problem, or ideas about other versions of the
> > software which might work better?
> >
> > Cheers