I have to write a join which involves 6 tables.
What are the best practices, tips & tricks when joining large number of tables.
I read an article by Tom Kyte who said that no need worry about join order, just write the joins in the logical order of the business rules.
Tom Kyte says that the CBO (as opposed to the RBO) will handle most of the matters for you, including join order, as long as statistics are
not stale.
But then in the below videos John Watson says that JOIN ORDER is the most important thing to consider when writing a 6 table join.
https://www.youtube.com/watch?v=0wwfMbZDRZ0
https://www.youtube.com/watch?v=zkrhCLXY41s
He shows a method called FRP (Filtered Rows % method) which can be used to determine the join order. So, does that mean that
we have to figure out the join order manually everytime?
Suppose there a query like this, which joins 6 tables (A, B, C, D, E, F):
SELECT
a.some_cols, c.some_cols, f.some_cols
FROM
a INNER JOIN e ON (a.c1 = e.c2)
INNER JOIN c ON (c.c3 = f.c4)
INNER JOIN d ON (d.c5 = c.c6)
INNER JOIN e ON (e.c7 = d.c8)
INNER JOIN b ON (b.c9 = e.c9)
WHERE
d.c11 = some_conditioin AND
a.c12 = some_Condition;
Also, if there are 6 tables to be joined there should be minimum of 5 joins to avoid getting a Cartesian join, right?
What are the best practices when writing a big SQL which joins say, 6 tables??