Re: Should Oracle outperform PostgreSQL on a complex

Bruce Momjian wrote:
> How are star joins different from what we do now?
>
> ---------------------------------------------------------------------------
>

Recall that a "star" query with n tables means a query where there are
(n - 1) supposedly small tables (dimensions) and 1 large table (fact) -
which has foreign keys to each dimension.

As I understand it, the classic "tar join" is planned like this:

1) The result of the restriction clauses on each of the (small)
dimension tables is computed.
2) The cartesian product of all the results of 1) is formed.
3) The fact (big) table is joined to the pseudo relation formed by 2).

From what I have seen most vendor implementations do not (always)
perform the full cartesion product of the dimensions, but do some of
them, join to the fact, then join to the remaining dimensions afterwards.

There is another join strategy called the "star transformation" where
some of the dimension joins get rewritten as subqueries, then the above
method is used again! This tends to be useful when the cartesion
products would be stupidly large (e.g. "sparse" facts, or few
restriction clauses)

regards

Mark

P.s : Luke or Simon might have a better definition... but thought I'd
chuck in my 2c... :-)