Re: Merge Join (Cartesian)

From: Mikito Harakiri <mikharakiri_at_ywho.com>
Date: Tue, 22 Apr 2003 16:27:26 -0700
Message-ID: <Ejkpa.14$vW3.101@news.oracle.com>

"Ryan" <rgaffuri_at_cox.net> wrote in message news:Gtjpa.149616$yh1.9551575_at_news1.east.cox.net...
> why does adding a column lead to a cartesian join?

Costing formulas include number of bytes processed by each rowsource. More columns means more bytes to process. Therefore, adding a column certainly disturbs all the plan costing analysis performed by optimizer. If you are really interested, you can capture 10053 trace in both cases and compare the diffs.

> using ordered is a real
> pain when you have a big join. its not always that easy to get the table
> orders correct? anything you can do?

But you know the "correct" join order already, right?

"Leading" is much better hint, of course. You don't have to specify all the tables in the leading hint too.

The other hint that you may use in really desperate siutuation is "cardinality" (not sure about 8.1, though).

Well, using hints is not easy, but, hey, there is room for oracle improvement! Received on Tue Apr 22 2003 - 18:27:26 CDT