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

"Jaap W. van Dijk" <j.w.vandijk.removethis_at_hetnet.nl> wrote in message news:45641f4e.1253828_at_news.hetnet.nl...
>
> 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.

Jaap,

I don't think it really matters that you can see a 'Cartesian' or a 'sort' in there. If there is only one row in the small table, then the it's not a real sort - the data just happens to be going into a buffer unnecessarily, and it's not a multiplicative Cartesian - which is where the costs usually appear.

I suppose you might want to test the side-effects on the buffering if you have a very big 'large table', in case the buffer starts to over-flow into the temp tablespace, but I can't think of any real issue with the plan otherwise.

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