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.
>
>
>
>
>
>
>
>
> E mail Disclaimer
>
> You agree that you have read and understood this
> disclaimer and you agree to be bound by its terms.
>
> The information contained in this e-mail and any
> files transmitted with it (if any) are confidential
> and intended for the addressee only. If you have
> received this e-mail in error please notify the
> originator.
>
> This e-mail and any attachments have been scanned
> for certain viruses prior to sending but CE Electric
> UK Funding Company nor any of its associated
> companies from whom this e-mail originates shall be
> liable for any losses as a result of any viruses
> being passed on.
>
> No warranty of any kind is given in respect of any
> information contained in this e-mail and you
> should be aware that that it might be incomplete,
> out of date or incorrect. It is therefore essential
> that you verify all such information with us before
> placing any reliance upon it.
>
> CE Electric UK Funding Company
> Lloyds Court
> 78 Grey Street
> Newcastle upon Tyne
> NE1 6AF
> Registered in England and Wales: Number 3476201
>
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Hately, Mike (LogicaCMG)
> INET: mike.hately_at_nedl.co.uk
>
> 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).
Connor McDonald
web:
http://www.oracledba.co.uk
web:
http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"
Want to chat instantly with your online friends? Get the FREE Yahoo!
Messenger
http://mail.messenger.yahoo.co.uk
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hately, Mike (LogicaCMG)
INET: mike.hately_at_nedl.co.uk
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 - 08:29:32 CDT
