Re: 7.3TC7 Optimizer foibles
Jay Walters wrote:
>
> 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?
The suggestion to replace the OR with a separate query joined by UNION
to the other sounds promising to me.
Art S. Kagel
That's what I wound up doing, but it costs me an extra step in my process, and an
extra temp table with potentially lots of rows during a transaction which needs to
have a fast response time.
How safe is a view of a union? That might get me out of some of my temp table
hell.
"Art S. Kagel" wrote:
> Jay Walters wrote:
> >
> > 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?
>
> The suggestion to replace the OR with a separate query joined by UNION
> to the other sounds promising to me.
>
> Art S. Kagel