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:10:14 +0100
Message-ID: <1027008539.1355.1.nnrp-01.9e984b29@news.demon.co.uk>

Oh dear -
I have to contradict you twice in one day.

Having done a bit of stress-testing and generally having fun with the optimisers, I can tell you that one test involving 255 tables gave me an optimisation time of:

        Rule           45 minutes 22 seconds
        all_rows      3.04 seconds
        first_rows    3.04 seconds


The CBO usually finds optimal paths in complex joins despite the best efforts of designers and dbas to confuse the issue with unsuitable indexing strategies, inappropriate statistics, and misleading resource definitions.

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

Next Seminars
 UK  Sept
 Australia August
 Malaysia September
 USA x 2  November

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

Alex Filonov wrote in message
<336da121.0207180744.cea7b92_at_posting.google.com>...

>
>You can estimate. In reality, it depends also on number of indexes, table
>segments and lots of other things. However, you can safely bet that if you
have
>more than 7 tables in your query, CBO is not gonna give you optimal plan,
ever.
>
Received on Thu Jul 18 2002 - 11:10:14 CDT

Original text of this message

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