Re: Is Richard Niemiec Right

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<blom02$atq$1$8300dec7_at_news.demon.co.uk>...
> Notes in-line
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> "Joel Garry" <joel-garry_at_home.com> wrote in message
> news:91884734.0310021619.31c47b2b_at_posting.google.com...
> >
> > Maybe he has some other book, but that is not how it is written in
> my
> > copy. It says "In a three-table join, the driving table is the
> > intersection table or the table that has a join condition to each of
> > the other two tables in the join. Try to use the most limiting
> table
> > as the driving table (or intersection table) so that your result set
> > from the join of the first two tables is small when you join it to
> the
> > third table." Seems reasonable enough.
> >
>
> Consider the options for joining three tables (ignoring
> degenerate cases like joining non-related tables
> using cartesian joins, one-to-one joins, and closed
> loops). Take tables A, B, C and use the
> text "-<" to mean one-to-many, and ">-" to mean
> many to one. Then a general three table join
> could be one of three things:
>
> A -< B -< C
> A -< B >- C
> A >- B -< C
>
> (The fourth possibility would be just the same as the
> first possibility back to front).
>
> The second example features B as 'the intersection
> table' - The expression is normally used to mean "the
> table representing the many-to-many relation between
> A and C". Table B is not an intersection table in the
> other two cases.
>
> In the case where you have an intersection table for B, it
> is quite likely that the 'interesting information' is in A
> or C - which means that the intersection table is quite
> likely to be the table with no FILTER predicates at all,
> which would make it a very poor choice of driving table.
>
>
> Moving to the "try to use the most limiting table as
> the driving table (or intersection table) ..." The sentence
> starts with a sensible idea - in fact, this is part of the
> Cost Based Optimiser algorithm. But what it the bit
> in brackets for ? If my most limiting table is table A,
> it is not possible to "use A as the intersection table" -
> the comment is meaningless.

That particular paragraph was at the end of a discussion of eliminating join records in a multitable join, as a summary. Presumably by then the reader would have picked up enough context to have seen he is saying the same thing as you. If I were picking it apart, I would say the real problem is that he seems to be implying you are designing the tables(ie, for filtering), rather than just working with what is there. And that would be a more general problem, outside the tuning issue. You certainly explained it more clearly!

>
> I hope the paragraph after the one you quote points
> out that having the most limiting table first doesn't
> necessarily produce a small result set after the
> first join - which is why the CBO then works
> starts permuting the join order to see if there are
> any better join orders.

The only thing I've noticed about "most limiting table first" was about NESTED LOOPS and ORDERED, which IIRC is not how the OP stated the book stated it. If I've missed something in the text, someone please correct me.

>
>
> Of course, it is alway easy to find flaws in text that
> is quoted out of context - perhaps there is plenty
> of surrounding text that explains the particular cases
> the author had in mind, the specific meaning he had
> in mind when using certain words, and gives concrete
> examples to clarify and demonstrate the point he was
> trying to make. But as it stands, the quoted paragraph
> does not 'seem reasonable enough' to me.

More than fair enough.

>
>
>
> > Then later: "Using cost-based optimization and NESTED LOOPS joins
> as
> > the means of joining the first table in the FROM clause is the
> driving
> > table (all other conditions being equal), but only the ORDERED hint
> > guarantees this."...
> >
> > He also says several times about indexes and join conditions.
> >

jg

--
@home.com is bogus.
http://www.mischiefmail.com/