Re: Functions and Relations

NENASHI, Tegiri wrote:
> "Aloha Kakuikanu" <aloha.kakuikanu_at_yahoo.com> wrote in
> > (Of course, the solution is obvious from the "predicates are
> > relations, and functions are predicates" perspective.
>
> It is not obvious because you still do not give the function-like-
> relation realization. When you give it it becomes clear if it is
> interesting and more performant than to filter rows by function.

select x,y from T
where x=1 and y=x+1

is

`x=1` /\ `y=x+1` /\ T

Therefore we have 6 join orders to evaluate: 1. `y=x+1` -> T -> `x=1`
that is:scan the `y=x+1` relation, then ... Stop right there. Scanning the whole `y=x+1` takes the infinite cost. 2. `y=x+1` -> `x=1` -> T
scan the `y=x+1` relation, then ... Same reason. 3. T -> `x=1` -> `y=x+1`
scan the T relation, then find matching tuples from `x=1`, then join with `y=x+1`. Mediocre cost.
4. T -> `y=x+1` -> `x=1`
scan the T relation, then find matching tuples from `y=x+1`, then join with `x=1`. Similar to the previous case. 5. `x=1` -> `y=x+1` -> T
scan the `x=1` relation, then do indexed nested loops join with T, then find matching tuples from `y=x+1`. Not bad. 6. `x=1` -> `y=x+1` -> T
scan the `x=1` relation, then find matching tuples from `y=x+1`, then do indexed nested loops join with T. The winner. Received on Tue Nov 21 2006 - 19:39:59 CST