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 <[EMAIL PROTECTED]> 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
