Re: Couple Questions about Explain Plan

I'm not going to commit myself on a buffer (sort) just yet, but the Cartesian Merge Join may be perfectly acceptable in a number of cases.

E.G. One of the tables is accessed to produce a known single row (e.g. PK or UK index), in which the cartesian join doesn't multiple any rows counts.

E.G.2 By doing a cartesian merge of two very small row sources, Oracle can produce a small combination of pairs of values for access into a third table using both columns of an index. (This was the basis for the original star (not star transformation) join).

E.G.3 By doing a cartesian merge of two small row sources, Oracle may be able to produce a set that can be hash distributed across parallel query slaves in a way that allows a parallel hash join to avoid an extreme skew - and also eliminate the need for a second, large, hash join.

and so on.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____USA_(FL)_May 2nd


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


Ryan Gaffuri wrote in message
<1efdad5b.0302070345.471ff321_at_posting.google.com>...


>1. What is a 'buffer sort'? Didnt see that one explained in the

>documentation. My best guess is that its a memory sort when you have

>an order by?

>

>2. 'Cartesian Merge Join'. I think I read somewhere that this isnt

>always a bad thing and its not always a sign that your joins are

>incorrect. Anyone have any more information?