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.
>
> Go figure. Not doubt Howard, Jonathan, Tom, and other can. I can't.
>
> Daniel Morgan

My apologies to all, I forgot to put version info on the original post (after just having read that thread about putting it in!), and that this is explicitly not CBO.

Anyways, this made me take another look at RBO in the (8i) performance tuning guide, and I'm now fairly certain the 1=1 is in there to avoid doing repetitive index scans for single rowids, when the app "knows" the result set is going to be manipulated rowid's for a large subset of the table. I would guess your DUAL/CBO example had some similar effect.

The answer to the obscure problem I was looking to solve was to dynamically add an index when the report is run. It turned out the query that was doing the 1=1 was running well, but a query being driven by that was doing a full table scan for every row. 10's of K's for 10's of K's... Can't add the index full-time because it would interfere with all sorts of online update stuff.

But I guess we won't need DBA's any more. :-)

jg

--
@home is some Japanese company, not me.  Even if DNS still lists my home PC.