Re: 1=1

Daniel Morgan <dmorgan_at_exesolutions.com> wrote in message news:<3DA5C68D.56CFE5F2_at_exesolutions.com>...
> Karsten Farell wrote:
>
> > Joel Garry wrote:
> > > I was hoping that wouldn't be the answer. But... it's the _last_
> > > one...
> > > [and I've seen others ending with AND 1=1) AND 1=1)]
> > >
> > > I just have some vague memory from years ago that it had some sort
> > > of useful effect in rule-based.
> > >
> > > jg
> > Maybe the 1=1 on the end is tacked on as a result of not selecting
> > anything from a drop-down list on the UI (as the value of the default
> > item). I suspect the cost-based optimizer throws away the 1=1 clauses
> > anyhow (no matter where they are).
> >
> > You might be remembering the old tricks we used on the rule-based
> > optimizer (did it really optimize anything?) to avoid index usage - eg,
> > select column+0 or column||''. Perhaps they still work with CBO (unless
> > it has grown so smart it knows when to ignore us).
>
> There are times when using 1=1 makes a significant difference in the
> execution plan selected by the CBO. And thus, it can dramatically improve
> performance of a query.
>
> As to why? I can't say. But I had a query once where dragging DUAL into it
> provided the same improvement.

Actually, I don't think anyone can say. For an interesting paper (especially if you read carefully with the idea of looking for an explanation of this DUAL effect) see the Milsap paper at hotsos.com about when to add an index.

In that context, the 1=1 becomes a way of saying there is low block-selectivity, a language that generates OCI calls might do it whenever a where clause on a small field in a large row is used.

>
> Go figure. Not doubt Howard, Jonathan, Tom, and other can. I can't.
>
> Daniel Morgan

jg

--
@home is bogoid.