Re: Merge Join (Cartesian)

"Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message news:Ejkpa.14$vW3.101_at_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.

you use tkprof to do that right? what tool do you use to analyze trace file? I haven't dug into this yet.

>
> > 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?
how am i supposed to know the correct join order. If Im doing a 15 table join, Ive noticed that its sometimes counter-intuitive. You think the larger table should be the inner table, but its often the opposite(or the other way around, I forget). I found this to be very tedious. Ive found that I can rarely ever improve on join order over what oracle does.

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

never heard of the leading hint? Ive read large parts of the performance tuning doc on OTN. is it there? I cant remember all the hints.
>
> The other hint that you may use in really desperate siutuation is
> "cardinality" (not sure about 8.1, though).

cardinality hint? this is one I do not remember seeing. Ill have to check it out. Its not in any of the tuning books Ive seen and Ive read several. or maybe I just dont remember it....
>
> Well, using hints is not easy, but, hey, there is room for oracle
> improvement!
>
>

so it makes a cartesian join? i thought the optimizer was supposed to be pretty good by now. does it still do that in 8i and 9i? Received on Tue Apr 22 2003 - 20:31:01 CDT