Re: Functions and Relations

vc wrote:
> In Oracle 9i:
> The nested loops plan does 15 times more work (30617 c.g. vs. 2216
> c.g.) than the hash join. It's hardly "logically about the same amount
> of work".

This is because hash matching is done in memory and therefore is not reflected in the logical IO. The matching itself is logically very similar, or is it not?

BTW, you omited the actual execution time difference. This is becuase it confiurms my 10 times heuristic, isn't it?-)

> That answers "why you think it'll be more performant" but does not
> answer "How do you suggest to materialize 'f(x)' as a relation ?". So
> how do you suggest to do the trick ?

No, I objected to the method where we materialize the `y=f(x)` relation. I suggested a vague idea that we access this relation via index -- function invokation.

> > Are you aware that selectivity/cardinality estimation related to
> > function invokations in SQL is finicky in today's RDBMS implementations?
>
> I am aware of the cardinality problem with 'select * from t1 where x in
> (select x from table(function_1(a,b,c))' and its variations, but the
> problem can be cured with the cardinality hint. It may not be
> esthetically pleasing, granted, but what is the alternative ?

Not only that. Can you specify card(A) = 10 * card(B)? How do you specify cardinality after some predicates are applied? How do you specify cardinality for a volatile (or temporaty) table that changes cardinality by orders of magnitude?

> One can
> use a temp table too which perhaps essentially the same as your
> suggestion (materializing a function as a temp relation). However,
> there may be unexceptable overhead when using temp relations, or such
> temp relation may be infinite and therefore impossible.

The cost of such materialization should be evaluated apriory. Given that there is unavoidable selectivity/cardiality estimation errors, I'd even suggest a heuristics that explicitly prohibits such a materialization. Received on Tue Nov 21 2006 - 22:57:45 CET