Re: OCP - Performance Tuning

Bear in mind that the cost of a nested loop is (approximately)

    cost of acquiring outer row set plus

        cardinality of outer row set times
        cost of acquiring one inner row set.

So if you double the number of rows in
an outer tablescan without changing the
number of blocks, the cost of the query
pretty much doubles - at which point the sort/merge becomes more desirable.

Of course - one easy to rig things for the purpose of this question is to run an
aggregate query so that the CBO recognises that returning the first_row on an inner step is a sub-optimal strategy for returning the first row in minimum time on the final step.

--
Regards

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

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____USA_(FL)_May 2nd


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


Niall Litchfield wrote in message
<3e37a6e4$0$245$ed9e5944_at_reading.news.pipex.net>...


>"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message

>news:b181rj$m9g$1$8302bc10_at_news.demon.co.uk...

>> It is very easy to produce an example

>> where changing the number of rows

>> (without changing the number of blocks)

>> in one of the tables causes a change in

>> the execution path between merge join

>> and nested loop join.

>>

>

>I'd be interested to see this, as the only way I can imagine the CBO

taking


>the number of rows into account would be if there was an index access

path


>(and hence selectivity changed), which may well be a good assumption

but


>indexes are not referred to at all in the question.

>