Re: How to optimize server for cartesian join? RESULSTS

On Feb 22, 1:12 pm, "Jack" <J..._at_none.com> wrote:
> > We have business need to do some countings.
>
> > It is using  MERGE JOIN [CARTESIAN], it will go fast with 10.000 count,
> > but
> > with 200.000 end sum it will take very long time.
> > It is using Reports 6i (a bit old;)
>
> > It does utilize very complez views etc, and plan is long as you would
> > assume.
> > I am not going to send plan.
>
> > One solution would be redesing it, but not quite sure it would help.
>
> > Server is 9.2.0.8 (multi prosessor, quite good one), parameters are now
> > very small, and
> > not optimized.
>
> > Is there some good parameters in server side which could help?
>
> "Doug Miller" <spamb..._at_milmac.com> kirjoitti
> viestissä:laosj.115$Mw..._at_nlpi068.nbdc.sbc.com...
>
> > In article <Kneoj.40$L..._at_read4.inet.fi>, "Jack" <n..._at_INVALIDmail.com>
> > wrote:
> >>We have business need to do some countings.
>
> >>It is using  MERGE JOIN [CARTESIAN], it will go fast with 10.000 count,
> >>but
> >>with 200.000 end sum it will take very long time.
> >>It is using Reports 6i (a bit old;)
>
> > The only way to truly "optimize" a Cartesian join is to eliminate it, by
> > creating an index for at least one of the tables, on the column(s) used in
> > the join.
>
> Hi
>
> Thanks Doug for your advice.
> It does have good indexes. (but not in remarksfield ;)
>
> Now after some basic parameter changes it is running 3,3x faster.
>
> It does not like more memory or paraller query.
>
> Any good advices, what next?

Merge Join Cartesian/Sort Merge Joins are typically best used when one of the two data sources contains a very small number of rows (1, 2, 3, etc.). Think of it this way, if the first data source has 100 rows, and the second also has 100 rows, a merge join Cartesian will create a resultset with 100 * 100 = 10,000 rows, and then filter out the rows that should not be in the resultset which is to be returned to the client. A nested loop join or hash join works more efficiently in most cases.

How long has it been since you ran DBMS_STATS to gather the table and index stats? Can you provide a copy of your initialization parameters? Also, post the DBMS_Xplan for the query.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Feb 22 2008 - 13:47:36 CST