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

Jaap W. van Dijk wrote:
> 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.

Are you using ( or at least considering ) partition schemes for the "big tables"?

When designing a data warehouse partitioning at some point becomes the only game in town otherwise it's hard to avoid the death spiral. Received on Wed Nov 22 2006 - 11:21:54 CST