Re: How hints affect the CBO?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 2 Jul 2002 18:48:00 +0100
Message-ID: <1025632189.1446.0.nnrp-12.9e984b29@news.demon.co.uk>

Your example is not quite as contrived as you suggest. I have seen Oracle do a nested loop Cartesian join because it has PK or UK information about the first table and knows that the fts will scan only once.

Unfortunately, I have seen a nested loop FTS when Oracle calculates that the driver will return zero rows - and get it wrong ;(

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

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November
http://www.jlcomp.demon.co.uk/seminar.html

Niall Litchfield wrote in message
<3d218176$0$8506$ed9e5944_at_reading.news.pipex.net>...



>It depends on the data and the stats. The (highly contrived) example below

>illustrates this. there are no indexes on either table but the nested loops

>plan is more efficient because of the daft differences in table size.

>

Received on Tue Jul 02 2002 - 12:48:00 CDT