db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: jira question
Date Mon, 01 Aug 2005 16:48:56 GMT
I like the syntax that satheesh has proposed, and of course it could
be implemented separate from the indexing question.  I still think
it would be nice if the optimizer could tell that an expression in
a where clause matched the result of the expression which generated
the column, otherwise indexes on the generated column aren't as interesting.

Rick Hillegas wrote:
> Thanks, Satheesh. I have logged a new enhancement request on this issue 
> and cross-linked it with bug 455. Generated Columns would be easier to 
> implement since we wouldn't have to teach the optimizer to flag a new 
> class of sargs.
> 
> Cheers,
> -Rick
> 
> Satheesh Bandaram wrote:
> 
>> I think it is possible to avoid extra storage with generated columns 
>> too.  These generated columns need not necessarily be in physical 
>> storage. The language layer could evaluate the expression on the fly 
>> when requested. Again, if a query uses an expression that might match 
>> a generated column, it would be possible to use the index.
>>
>> It is possible to have a first implementation actually create the 
>> column in physical storage and then improve the implementation to 
>> avoid this physical column and instead just evaluate the expression 
>> when needed.
>>
>> Satheesh
>>
>> Manish Khettry wrote:
>>
>>> Adding hidden columns is an easy way to implement the feature but it
>>> does result in storage being wasted. Mike's suggestion (option 3) in
>>> Jira seems to be a better way of doing it. As I understand it, Mike is
>>> suggesting that the store is unaware of the fact that a function value
>>> is used as a key in the btree for theindex. The language layer
>>> maintains the information and uses it to maintain the index (i.e.
>>> passing the right key value in DML and index build after applying the
>>> function) as well as considering the index if the function is present
>>> in the query.
>>>
>>> Manish
>>>
>>> On 7/29/05, Satheesh Bandaram <satheesh@sourcery.org> wrote:
>>>  
>>>
>>>> I don't know why Jira is not forwarding comments for this specific bug.
>>>> Here are some of my comments.
>>>>
>>>> SQL standard provides a way to have generated columns. These generated
>>>> columns can be created using,
>>>>
>>>> GENERATED ALWAYS AS <left paren><value expression><right paren>
>>>>
>>>> Once we have a generated column, it should be possible to create a
>>>> regular B-Tree index on this generated column. I think there are 
>>>> several
>>>> advantages of using this syntax: 1) It is standards based. 2) Since the
>>>> column is visible, it is much cleaner and easier to understand the
>>>> semantics 3) Fits nicely with existing syntax for identity columns.
>>>>
>>>> I think this is a good enhancement to add to Derby.
>>>>
>>>> Satheesh
>>>>
>>>> Mike Matrigali wrote:
>>>>
>>>>   
>>>>
>>>>> don' know what is going on with jira.  I have added comments on 
>>>>> 455, and
>>>>> have not seen those go to the list.
>>>>>
>>>>> Rick Hillegas wrote:
>>>>>
>>>>>     
>>>>>
>>>>>> I added comments to two bugs this week (171 and 455) and was hoping
>>>>>> that Jira would post these comments to the developer's list. In
>>>>>> particular, I was hoping that my comment on 455 (expression indexes)
>>>>>> would invite further discussion by the Store experts. Alas, Jira
did
>>>>>> not post my comments. Would appreciate theories about how I am
>>>>>> mis-using Jira.
>>>>>>
>>>>>> Thanks,
>>>>>> -Rick
>>>>>>
>>>>>>
>>>>>>
>>>>>>       
>>>>>
>>>>>     
>>>>
>>>>   
>>>
>>>
>>>
>>>
>>>  
>>>
> 
> 
> 
> 


Mime
View raw message