Notes in-line
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Iceland__November (tbc)
____Belgium__November (EOUG event)
____UK_______December (UKOUG conference)
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA__October
____UK___November
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.
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.
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.
> 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.
>
Received on Sun Oct 05 2003 - 03:51:09 CDT
