| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: WHERE 1 = 1 (any info on this)
I'm personally using it for large search screens with ~100 attributes to
fulfil. At first comes head of select statement let's say
SELECT a, b FROM table_a
then comes where clause WHERE 1=1
and after that I can simply add variable v_comma = ' AND ';
So for every search attribute I can simply add to where clause
IF in_attribute_XXXX IS NOT NULL
v_where_clause = v_comma || 'attribute_XXXX = ' || in_attribute_XXXX;
END IF;
Of course where clause additions depends on attribute type, there may be
two search attributes for an attribute in the table eg. limits from both
sides for numbers or dates etc.
Of course I'm not building views, but simple dynamic select statements.
Gints
> -----Original Message-----
> From: Hately, Mike (LogicaCMG) [mailto:mike.hately_at_nedl.co.uk]
> Sent: Tuesday, October 21, 2003 4:30 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: WHERE 1 = 1 (any info on this)
>
> OK, here's the interesting thing. At 8.1.6.3 with optimizer_mode=rule
the
> statement I'm looking at returns very different explain plans
depending on
> whether the "1=1" clause is included. It's a complicated join and the
> explain plans are over 300 lines so it's not easy to see what's
happening.
> I'll try with a simpler join.
> And yes, I know the RBO is ancient technology these days. =)
>
> Cheers,
> Mike
>
> -----Original Message-----
> Sent: 21 October 2003 12:05
> To: Multiple recipients of list ORACLE-L
>
>
> I'm pretty sure the optimizer can pick up 1=1 anyway
> and ignore it as an always-true condition - so you get
> no benefit.
>
> The most common cause I've seen for 1=1 is so when
> developers are building dynamic where-clause, they
> don't need to worry about adding 'where' versus 'and'
> to the sql string being constructed.
>
> hth
> connor
>
> --- "Hately, Mike (LogicaCMG)"
> <mike.hately_at_nedl.co.uk> wrote: > Morning folks,
> >
> > the developers here are looking at a view with a
> > where clause which
> > specifies :
> >
> > WHERE 1=1 AND
> > ............... AND
> > ............... etc.
> >
> > I'd seen this used before as a way of tweaking the
> > RBO into certain
> > behaviours but it was years ago and my recollection
> > is very hazy.
> > The only explanation I've found so far is :
> >
> > "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."
> >
> > Is anyone familiar enough with this tweak to explain
> > it ?
> >
> > Cheers,
> > Mike
> >
> > PS Maybe it'll head off some replies if I make it
> > clear that this view
> > hasn't been generated by code so the "1=1" isn't an
> > accidental artifact It
> > was custom written and is definitely supposed to
> > have exactly this
> > structure.
> >
> >
> >
> >
> >
> >
> >
> >
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gints Plivna INET: g.plivna_at_softex.lv Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Tue Oct 21 2003 - 09:59:24 CDT
 |
 |