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

On Wed, 22 Nov 2006 10:13:49 -0000, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

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

Hi Jonathan,

I'm trying to improve runtime for some large tables, ranging from 1 to 20 GB. My pga_aggregate_target is set to 3GB, so a lot of data is buffered in TEMP.

It's a datawarehouse, and the chosen generated solutions were allright for the first small sources, but don't suffice anymore for these big tables, the latest addition to the warehouse.

Regards, Jaap. Received on Wed Nov 22 2006 - 04:57:26 CST