Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: EXPLAIN PLAN : better with RULE!

Re: EXPLAIN PLAN : better with RULE!

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 18 Jul 2002 17:06:04 +0100
Message-ID: <1027008538.1355.0.nnrp-01.9e984b29@news.demon.co.uk>

Since Oracle Corp. insist that the rule-based optimiser hasn't been enhanced since Oracle 7, your claims have to be viewed with scepticism until proven true. And since even the 9.2 Cost based optimiser can't work around the problem of

    numeric_column + 0 = constant
without the aid of a function-based index, then your specific claim 1 is most unlikely to be true.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
 UK          Sept
 Australia  August
 Malaysia September
 USA (MI) November

http://www.jlcomp.demon.co.uk/seminar.html

Alex Filonov wrote in message
<336da121.0207170923.4469328d_at_posting.google.com>...

>
>I had bad experience after upgrade from 8.0.6 to 8.1.6. Several complex
queries
>which ran fine on 7.3 and 8.0.6 with RBO started running forever on 8.1.6.
I
>found several problems.
>
>1. Old tricks like ||'' and +0 in conditions don't help in some cases even
if
>you set session parameter optimizer_mode = rule and you have no hints in a
>statement.
>2. Old RBO rule that unique indexes are choosen first doesn't apply to 8i.
>3. In complex queries, old RBO rule that tables are taken in reverse order
of
>the from clause, doesn't apply. This happens only sometimes, but it's
annoying.
>I haven't checked it on 8.1.7 yet.
>
>The most annoying part is that you can't be sure that plan you had on 8.0
is the
>same on 8.1 even with optimizer_mode = rule.
Received on Thu Jul 18 2002 - 11:06:04 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US