Re: 1=1

From: Daniel Morgan <dmorgan_at_exesolutions.com>
Date: Mon, 14 Oct 2002 17:33:08 GMT
Message-ID: <3DAAFFCF.C068702A@exesolutions.com>

Joel Garry wrote:

> 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.

Thanks.

Daniel Morgan Received on Mon Oct 14 2002 - 12:33:08 CDT