jira question

classic Classic list List threaded Threaded
34 messages Options
12
Reply | Threaded
Open this post in threaded view
|

jira question

Rick Hillegas-2
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

Reply | Threaded
Open this post in threaded view
|

Re: jira question

Andrew McIntyre-2

On Jul 28, 2005, at 5:37 PM, 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.

Hi Rick,

That is strange that those messages did not come through. I have gotten other comments from Jira that you have made, including 171, although I did not receive the message from the comment you made to 455. My guess is that it was probably a minor hiccup on Jira's or the mailserver's part. I would suggest just resending the comment for 455 directly to the list.

If it continues to happen, let me know and I'll alert the Jira admins and/or infrastructure as necessary.

andrew
Reply | Threaded
Open this post in threaded view
|

Re: jira question

francois.orsini
Same for me - I see one of your comment about 171 but not 455.

On 7/28/05, Andrew McIntyre <[hidden email]> wrote:

>
>
> On Jul 28, 2005, at 5:37 PM, 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.
> Hi Rick,
>
> That is strange that those messages did not come through. I have gotten
> other comments from Jira that you have made, including 171, although I did
> not receive the message from the comment you made to 455. My guess is that
> it was probably a minor hiccup on Jira's or the mailserver's part. I would
> suggest just resending the comment for 455 directly to the list.
>
> If it continues to happen, let me know and I'll alert the Jira admins and/or
> infrastructure as necessary.
>
> andrew
Reply | Threaded
Open this post in threaded view
|

Re: jira question

mikem_app
In reply to this post by Rick Hillegas-2
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
>
>
>

Reply | Threaded
Open this post in threaded view
|

Re: jira question

Satheesh Bandaram
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
>>
>>
>>
>
>
>

Reply | Threaded
Open this post in threaded view
|

Re: jira question

Mamta Satoor
My comment to Derby-421 "starting an XA transaction resets the isolation level set with SET CURRENT ISOLATION" has not shown up on the list either.
 
Mamta

 
On 7/29/05, Satheesh Bandaram <[hidden email]> 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
>>
>>
>>
>
>
>


Reply | Threaded
Open this post in threaded view
|

Re: jira question

Manish Khettry
In reply to this post by Satheesh Bandaram
It looks like the discussion is partly on this thread and partly in
Jira which isn't sending out email atleast for this issue.

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 <[hidden email]> 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
> >>
> >>
> >>
> >
> >
> >
>
>
Reply | Threaded
Open this post in threaded view
|

Re: jira question

Jean T. Anderson
In reply to this post by mikem_app
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.

reports about Jira not sending email are making it to infrastructure, so
they're aware of the problem.

  -jean
Reply | Threaded
Open this post in threaded view
|

Re: jira question

Rick Hillegas-2
In reply to this post by Andrew McIntyre-2
Thanks, Andrew.

-Rick

Andrew McIntyre wrote:

>
> On Jul 28, 2005, at 5:37 PM, 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.
>
>
> Hi Rick,
>
> That is strange that those messages did not come through. I have
> gotten other comments from Jira that you have made, including 171,
> although I did not receive the message from the comment you made to
> 455. My guess is that it was probably a minor hiccup on Jira's or the
> mailserver's part. I would suggest just resending the comment for 455
> directly to the list.
>
> If it continues to happen, let me know and I'll alert the Jira admins
> and/or infrastructure as necessary.
>
> andrew



Reply | Threaded
Open this post in threaded view
|

Re: jira question

Satheesh Bandaram
In reply to this post by Manish Khettry
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 [hidden email] 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



        

      
    



  
Reply | Threaded
Open this post in threaded view
|

Re: jira question

Rick Hillegas-2
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 <[hidden email]> 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
>>>>>
>>>>>
>>>>>
>>>>>        
>>>>>
>>>>      
>>>>
>>>    
>>>
>>
>>
>>
>>  
>>


Reply | Threaded
Open this post in threaded view
|

Re: jira question

mikem_app
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 <[hidden email]> 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
>>>>>>
>>>>>>
>>>>>>
>>>>>>      
>>>>>
>>>>>    
>>>>
>>>>  
>>>
>>>
>>>
>>>
>>>  
>>>
>
>
>
>

Reply | Threaded
Open this post in threaded view
|

Re: jira question

Manish Khettry
On 8/1/05, Mike Matrigali <[hidden email]> wrote:
> 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.
>

The expression used for the generated column must be stored in the
data dictionary. Does derby serialize expressions? I was looking
around in the classes in impl/sq/compile (ValueNode and such) and
these classes don't seem to implement Formatable.

Manish
Reply | Threaded
Open this post in threaded view
|

Functional indexes WAS Re: jira question

Daniel John Debrunner
Manish Khettry wrote:

> On 8/1/05, Mike Matrigali <[hidden email]> wrote:
>
>>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.
>>
>
>
> The expression used for the generated column must be stored in the
> data dictionary. Does derby serialize expressions? I was looking
> around in the classes in impl/sq/compile (ValueNode and such) and
> these classes don't seem to implement Formatable.

Compile nodes (and classes) are runtime only. They are never serialized
to disk and are not designed for that. Any runtime representation of a
compiled entity uses a specific class, such as RoutineAliasInfo.

An initial approach to this functional indexing may to to make the
scheme work for simple expressions, function calls on single columns,
eg. UPPER(lastname). Just getting that to work would solve most of the
typical uses and would be a good step towards a complete solution.
Handling arbitary expressions up front may be too much to bite off.

Dan.

Reply | Threaded
Open this post in threaded view
|

Re: jira question

Jeffrey Lichtman
In reply to this post by Manish Khettry

>The expression used for the generated column must be stored in the
>data dictionary. Does derby serialize expressions? I was looking
>around in the classes in impl/sq/compile (ValueNode and such) and
>these classes don't seem to implement Formatable.

It would be better to store the expressions as text, and to parse and
bind them when needed. I learned the hard way while working on the
internals of other database systems that storing internal data
structures in the data dictionary causes trouble when the structures
change. Also, it's useful for users to be able to look at the text of
their DDL statements.


                        -        Jeff Lichtman
                                 [hidden email]
                                 Check out Swazoo Koolak's Web Jukebox at
                                 http://swazoo.com/ 

Reply | Threaded
Open this post in threaded view
|

Re: jira question

Rick Hillegas-2
Thanks, Jeff. I agree. Recompiling these expressions can occur when we
compile the triggering SELECT/UPDATE/INSERT statements. Compiling a
SELECT/UPDATE/INSERT is a relatively heavyweight operation and I don't
think anyone will notice a little extra time spent recompiling these
expressions.

Cheers,
-Rick

Jeffrey Lichtman wrote:

>
>> The expression used for the generated column must be stored in the
>> data dictionary. Does derby serialize expressions? I was looking
>> around in the classes in impl/sq/compile (ValueNode and such) and
>> these classes don't seem to implement Formatable.
>
>
> It would be better to store the expressions as text, and to parse and
> bind them when needed. I learned the hard way while working on the
> internals of other database systems that storing internal data
> structures in the data dictionary causes trouble when the structures
> change. Also, it's useful for users to be able to look at the text of
> their DDL statements.
>
>
>                        -        Jeff Lichtman
>                                 [hidden email]
>                                 Check out Swazoo Koolak's Web Jukebox at
>                                 http://swazoo.com/



Reply | Threaded
Open this post in threaded view
|

Re: jira question

Manish Khettry
I think using arbitrarily complex expressions to create indexes may be
overkill. Dan's idea of restricting expression indexes on function
calls on a single base column (actually one _or more_ base columns
shouldn't be that much harder) is probably better.

Isn't storing the expression text for a generated column also
problematic? I can think of a few other things
-- The expression will also have to be compiled and evaluated for DDL
like alter table (drop column)  possibly others.
-- We'll need a good way to check if two expressions are the same.
Obviously a textual comparison may not work. Making sure that two
expr's are the same is not trivial if we want to do it right-- say
(x+y) > 10 is the same as 10 < (y+x).

Manish

On 8/3/05, Rick Hillegas <[hidden email]> wrote:

> Thanks, Jeff. I agree. Recompiling these expressions can occur when we
> compile the triggering SELECT/UPDATE/INSERT statements. Compiling a
> SELECT/UPDATE/INSERT is a relatively heavyweight operation and I don't
> think anyone will notice a little extra time spent recompiling these
> expressions.
>
> Cheers,
> -Rick
>
> Jeffrey Lichtman wrote:
>
> >
> >> The expression used for the generated column must be stored in the
> >> data dictionary. Does derby serialize expressions? I was looking
> >> around in the classes in impl/sq/compile (ValueNode and such) and
> >> these classes don't seem to implement Formatable.
> >
> >
> > It would be better to store the expressions as text, and to parse and
> > bind them when needed. I learned the hard way while working on the
> > internals of other database systems that storing internal data
> > structures in the data dictionary causes trouble when the structures
> > change. Also, it's useful for users to be able to look at the text of
> > their DDL statements.
> >
> >
> >                        -        Jeff Lichtman
> >                                 [hidden email]
> >                                 Check out Swazoo Koolak's Web Jukebox at
> >                                 http://swazoo.com/
>
>
>
>
Reply | Threaded
Open this post in threaded view
|

Re: jira question

Satheesh Bandaram

Manish Khettry wrote:
I think using arbitrarily complex expressions to create indexes may be
overkill. Dan's idea of restricting expression indexes on function
calls on a single base column (actually one _or more_ base columns
shouldn't be that much harder) is probably better.
  
While it may be OK to start with a single base column, I think one or more base column references would be good to support. Useful for functional indexes on area, for example. Or total compensation, which may involve base salary, bonus, commissions etc.
Isn't storing the expression text for a generated column also
problematic? I can think of a few other things
-- The expression will also have to be compiled and evaluated for DDL
like alter table (drop column)  possibly others.
  
Doesn't Derby already support evaluating an expression at DDL time for a default clause? Alter table add column evaluates this default expression at the DDL time.
-- We'll need a good way to check if two expressions are the same.
Obviously a textual comparison may not work. Making sure that two
expr's are the same is not trivial if we want to do it right-- say
(x+y) > 10 is the same as 10 < (y+x).
  
Right... Common expression detection needs to be added. Would also be useful to eliminate multiple evaluations of any common (sub-)expressions.

Satheesh
Manish

On 8/3/05, Rick Hillegas [hidden email] wrote:
  
Thanks, Jeff. I agree. Recompiling these expressions can occur when we
compile the triggering SELECT/UPDATE/INSERT statements. Compiling a
SELECT/UPDATE/INSERT is a relatively heavyweight operation and I don't
think anyone will notice a little extra time spent recompiling these
expressions.

Cheers,
-Rick

Jeffrey Lichtman wrote:

    
The expression used for the generated column must be stored in the
data dictionary. Does derby serialize expressions? I was looking
around in the classes in impl/sq/compile (ValueNode and such) and
these classes don't seem to implement Formatable.
        
It would be better to store the expressions as text, and to parse and
bind them when needed. I learned the hard way while working on the
internals of other database systems that storing internal data
structures in the data dictionary causes trouble when the structures
change. Also, it's useful for users to be able to look at the text of
their DDL statements.


                       -        Jeff Lichtman
                                [hidden email]
                                Check out Swazoo Koolak's Web Jukebox at
                                http://swazoo.com/
      


    



  
Reply | Threaded
Open this post in threaded view
|

Re: jira question

Rick Hillegas-2
I don't anticipate any significant time savings by scaling back the kind
of expressions we support.  In any event, sarg-matching of
indexableExpressions should not involve text comparisons. I imagine it
would involve normalizing the parse trees and comparing the normalized
subgraphs.

If Mike is right and generated columns aren't useful unless the
optimizer can turn indexableExpressions into sargs, then the choice
between generated columns and  expression indexes seems largely
religious to me. I am leaning toward expression indexes because they are
slightly simpler for the user: she only has to declare an index, not
declare a special column and then declare an index.

By the way, this is a very lively, fruitful, and clarifying email
thread. Thanks especially to Dan, Mike, Satheesh, and Manish.

Cheers,
-Rick

Satheesh Bandaram wrote:

>
> Manish Khettry wrote:
>
>>I think using arbitrarily complex expressions to create indexes may be
>>overkill. Dan's idea of restricting expression indexes on function
>>calls on a single base column (actually one _or more_ base columns
>>shouldn't be that much harder) is probably better.
>>  
>>
> While it may be OK to start with a single base column, I think one or
> more base column references would be good to support. Useful for
> functional indexes on *area*, for example. Or total compensation,
> which may involve base salary, bonus, commissions etc.
>
>>Isn't storing the expression text for a generated column also
>>problematic? I can think of a few other things
>>-- The expression will also have to be compiled and evaluated for DDL
>>like alter table (drop column)  possibly others.
>>  
>>
> Doesn't Derby already support evaluating an expression at DDL time for
> a default clause? Alter table add column evaluates this default
> expression at the DDL time.
>
>>-- We'll need a good way to check if two expressions are the same.
>>Obviously a textual comparison may not work. Making sure that two
>>expr's are the same is not trivial if we want to do it right-- say
>>(x+y) > 10 is the same as 10 < (y+x).
>>  
>>
> Right... Common expression detection needs to be added. Would also be
> useful to eliminate multiple evaluations of any common (sub-)expressions.
>
> Satheesh
>
>>Manish
>>
>>On 8/3/05, Rick Hillegas <[hidden email]> wrote:
>>  
>>
>>>Thanks, Jeff. I agree. Recompiling these expressions can occur when we
>>>compile the triggering SELECT/UPDATE/INSERT statements. Compiling a
>>>SELECT/UPDATE/INSERT is a relatively heavyweight operation and I don't
>>>think anyone will notice a little extra time spent recompiling these
>>>expressions.
>>>
>>>Cheers,
>>>-Rick
>>>
>>>Jeffrey Lichtman wrote:
>>>
>>>    
>>>
>>>>>The expression used for the generated column must be stored in the
>>>>>data dictionary. Does derby serialize expressions? I was looking
>>>>>around in the classes in impl/sq/compile (ValueNode and such) and
>>>>>these classes don't seem to implement Formatable.
>>>>>        
>>>>>
>>>>It would be better to store the expressions as text, and to parse and
>>>>bind them when needed. I learned the hard way while working on the
>>>>internals of other database systems that storing internal data
>>>>structures in the data dictionary causes trouble when the structures
>>>>change. Also, it's useful for users to be able to look at the text of
>>>>their DDL statements.
>>>>
>>>>
>>>>                       -        Jeff Lichtman
>>>>                                [hidden email]
>>>>                                Check out Swazoo Koolak's Web Jukebox at
>>>>                                http://swazoo.com/
>>>>      
>>>>
>>>
>>>    
>>>
>>
>>
>>
>>  
>>


Reply | Threaded
Open this post in threaded view
|

Re: jira question

Daniel John Debrunner
Rick Hillegas wrote:

> I don't anticipate any significant time savings by scaling back the kind
> of expressions we support.  In any event, sarg-matching of
> indexableExpressions should not involve text comparisons. I imagine it
> would involve normalizing the parse trees and comparing the normalized
> subgraphs.

I'm just saying that getting some working functionality out for simple
expressions sooner rather than waiting to solve all the edge hard cases
is probably better. Getting a functional index working on something like
UPPER(lastname) will solve most people's problems.

starts chant

'incremental development - release early release often'
'incremental development - release early release often'

Dan.


12