Re: Cartesian join: What is this BUFFER SORT accomplishing?

"Jaap W. van Dijk" <j.w.vandijk.removethis_at_hetnet.nl> wrote in message news:456367a0.2501765_at_news.hetnet.nl...
> Hi,
>
> a. What does the BUFFER SORT accomplish? Nothing needs to be compared,
> so why need a sort?
> b. Why is it called a BUFFER SORT, and not SORT (JOIN) as for normal
> mergejoins? Is it something else?
> c. If we need sorts, why then is the first table not sorted?
>

Dimitre has already answered (a).
My assumptions about (b) and (c) are:

    It's labelled a BUFFER SORT to give you a clue     that it's only using the buffering mechanism of sorting,     but not really doing a sort

    You don't actually "need" a sort - the indications from     the 10032 trace for this example suggest that Oracle     does "compare" rows as it reads them, but it doesn't     seem to be comparing anything that exists, because     every comparison results in an 'already in order' result.

As Dimitre says, it's just a way of using a buffering mechanism. The mechanism is invoked because although YOU know that there will be only one row, Oracle is only ESTIMATING one row - so buffering the data to avoid extra tablescans against real data blocks if the estimate is wrong is a defensive mechanism.

Try running the query with a unique index created on the small table, and add a predicate which selects the one row by unique key - I suspect the buffer sort mechanism will disappear and you'll get a nested loop with simple full tablescan on the large table.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

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