Re: Functions and Relations

"Aloha Kakuikanu" <aloha.kakuikanu_at_yahoo.com> wrote in news:1164144924.033155.307890_at_b28g2000cwb.googlegroups.com:

>

>
> In other words, it doesn't rewrite the query to
>
> select * from T
> where x = 1 and y = 0
>
> ?

It does not rewrite but it is not what you asked is it ? It does what you wanted: access by x = 1 and then filter by y = x - 1.

>BTW, I posted this challenge to the "big 3" forums, and Lennart
> posted qute a surprising fact (at least for me) that DB2 rewrites the
> query even when we have UDF:
>
> create function my_f2 (x int)
> returns int
> language sql
> contains sql
> deterministic
> return case when mod(x,2)=0 then x*x+3 else -1*x end;
>
> select *
> from T
> where x = 1 and y = my_f2(x)
>
> (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.

>Moreover, we can
> leverage the existing machinery of brute join orders evaluation
> without putting any burden on optimizer's query rewrite capabilities).

We can not because it is not clear how to collect statistics specially for the infinite relation.

--
Tegi



> 

>