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

On Wed, 22 Nov 2006 08:56:15 -0000, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>"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
>
>
>

Dimitre and Jonathan,

Thanks for clearing this up for me.

This statement is part of a generated package. I don't want to use hidden parameters, so I guess if I have to fiddle with the generated code, I might as well read the one_row_table, put the values in PL/SQL variables and add those to the SELECT FROM big_table.

Regards, Jaap. Received on Wed Nov 22 2006 - 04:06:07 CST