Derby optimizer for Table Function

[Reda134 <re...@gmail.com>]

Hi all, 
The Derby devoloppers mentions in the documentation of table functions, that
the optimizer takes 2 Assumtions : 

1 - It Assumes by default that table functions are repeatable and thus place
them in the inner slot of a join.

2 -  It Assumes by default that table functions are expensice and thus place
them in the outer slot of a join.

Is this not paradoxal ?? Or i get it wrong ??

Thank you for your replies

Reda

-- 
View this message in context: http://www.nabble.com/Derby-optimizer-for-Table-Function-tp20456636p20456636.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Derby optimizer for Table Function

[Rick Hillegas <Ri...@Sun.COM>]

Hi Reda,

Thanks for asking these questions. Your comments will help us improve 
the documentation of table functions.

"Repeatable" pretty much means deterministic. That is, a table function 
is repeatable if it returns exactly the same rows when you invoke it 
with a given set of arguments. Repeatable is actually a little weaker 
than deterministic, though. A repeatable table function doesn't have to 
return the rows in the same order each time it is invoked, it just has 
to return the same rows. A deterministic table function would return the 
rows in exactly the same order each time it was invoked.

"Read once" means "not repeatable". A table function is "read once" if 
it can return different rows each time that you invoke it with a given 
set of arguments.

Hope this helps,
-Rick

Reda134 wrote:
> Thank you very much for your reply:)
>
> Can you please explain me what derby developpers mean exactly with
> "repeatable table functions" and "read once Streams" ? 
>
> Many thanks in advance. 
>  
>
> Knut Anders Hatlen wrote:
>   
>> Reda134 <re...@gmail.com> writes:
>>
>>     
>>> Hi all, 
>>> The Derby devoloppers mentions in the documentation of table functions,
>>> that
>>> the optimizer takes 2 Assumtions : 
>>>
>>> 1 - It Assumes by default that table functions are repeatable and thus
>>> place
>>> them in the inner slot of a join.
>>>
>>> 2 -  It Assumes by default that table functions are expensice and thus
>>> place
>>> them in the outer slot of a join.
>>>
>>> Is this not paradoxal ?? Or i get it wrong ??
>>>       
>> I assume you're talking about this section of the developer's guide:
>> http://db.apache.org/derby/docs/10.4/devguide/cdevspecialtfoptimizer.html
>>
>> The clue here is that it doesn't say "thus place them in the
>> [outer/inner] slot", but rather that it is _likely_ that the optimizer
>> will place the table function in an outer slot and that the optimizer
>> _can_ place it in an inner slot. If you for example join two table
>> functions, only one of them can be in the outer slot. The other one will
>> go in the inner slot. If none of them can go in the inner slot because
>> they are not repeatable, one of them will be stored in a temporary table
>> which can go in the inner slot.
>>
>> -- 
>> Knut Anders
>>
>>
>>     
>
>   

Re: Derby optimizer for Table Function

[Reda134 <re...@gmail.com>]

Thank you very much for your reply:)

Can you please explain me what derby developpers mean exactly with
"repeatable table functions" and "read once Streams" ? 

Many thanks in advance. 
 

Knut Anders Hatlen wrote:
> 
> Reda134 <re...@gmail.com> writes:
> 
>> Hi all, 
>> The Derby devoloppers mentions in the documentation of table functions,
>> that
>> the optimizer takes 2 Assumtions : 
>>
>> 1 - It Assumes by default that table functions are repeatable and thus
>> place
>> them in the inner slot of a join.
>>
>> 2 -  It Assumes by default that table functions are expensice and thus
>> place
>> them in the outer slot of a join.
>>
>> Is this not paradoxal ?? Or i get it wrong ??
> 
> I assume you're talking about this section of the developer's guide:
> http://db.apache.org/derby/docs/10.4/devguide/cdevspecialtfoptimizer.html
> 
> The clue here is that it doesn't say "thus place them in the
> [outer/inner] slot", but rather that it is _likely_ that the optimizer
> will place the table function in an outer slot and that the optimizer
> _can_ place it in an inner slot. If you for example join two table
> functions, only one of them can be in the outer slot. The other one will
> go in the inner slot. If none of them can go in the inner slot because
> they are not repeatable, one of them will be stored in a temporary table
> which can go in the inner slot.
> 
> -- 
> Knut Anders
> 
> 

-- 
View this message in context: http://www.nabble.com/Derby-optimizer-for-Table-Function-tp20456636p20458631.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Derby optimizer for Table Function

[Knut Anders Hatlen <Kn...@Sun.COM>]

Reda134 <re...@gmail.com> writes:

> Hi all, 
> The Derby devoloppers mentions in the documentation of table functions, that
> the optimizer takes 2 Assumtions : 
>
> 1 - It Assumes by default that table functions are repeatable and thus place
> them in the inner slot of a join.
>
> 2 -  It Assumes by default that table functions are expensice and thus place
> them in the outer slot of a join.
>
> Is this not paradoxal ?? Or i get it wrong ??

I assume you're talking about this section of the developer's guide:
http://db.apache.org/derby/docs/10.4/devguide/cdevspecialtfoptimizer.html

The clue here is that it doesn't say "thus place them in the
[outer/inner] slot", but rather that it is _likely_ that the optimizer
will place the table function in an outer slot and that the optimizer
_can_ place it in an inner slot. If you for example join two table
functions, only one of them can be in the outer slot. The other one will
go in the inner slot. If none of them can go in the inner slot because
they are not repeatable, one of them will be stored in a temporary table
which can go in the inner slot.

-- 
Knut Anders