Are generated columns stored in the DB or calculated on the fly?

[Uriah Eisenstein <ur...@gmail.com>]

Hi,
I searched around for the actual implementation of column "generated always
as <expression>" but couldn't find anything, except maybe by digging into
Derby source code which I don't want to... I want to know whether a
generated column is a "real" column existing within the DB storage, and is
updated whenever the columns it depends on are updated - or is it a
"virtual" column which is recalculated whenever it is read. I don't have a
specific need for it right now, but such columns can be useful for indexing
and such, e.g. of case-insensitive strings or of UDTs, so I think it is
useful to know whether the price is mainly in space requirements (if they're
"real") or just in performance when reading.
Thanks,
Uriah

Re: Are generated columns stored in the DB or calculated on the fly?

[Uriah Eisenstein <ur...@gmail.com>]

OK, filed the generated columns documentation issue in JIRA (Derby-4790).
Possibilities like taking advantage of Comparable java UDTs crossed my mind
too, but I didn't dare suggest it :) I'm playing around with Derby and JDBC
at home currently, and have little knowledge about the actual implementation
of a DB back-end - as well as no access to the SQL standard. I don't know
what these would say about casting UDTs into strings, for instance -
something which should be possible for any Java type using its toString()
method.
Similarly, I don't know if virtual columns have any standard syntax; I only
saw that some DBs define them simply by "AS <expression>" following the
common column definition. Because of that I'd rather not file an improvement
suggestion, unless you think I should despite the missing information.
Regards,
Uriah Eisenstein

On Fri, Sep 3, 2010 at 11:49 PM, Knut Anders Hatlen
<kn...@oracle.com>wrote:

> Uriah Eisenstein <ur...@gmail.com> writes:
>
> > Hi Knut,
> > Thanks for your answer. May I suggest that this be added to the
> documentation
> > of generated columns?
>
> Yes, you may. :) Please file a JIRA report so that it's not forgotten.
>
> > I think support for virtual columns, as they're indeed called in some
> other
> > DBs, would really be nice though. For instance, Java Enums fit nicely as
> a
> > UDT, but indexing on them (or even simple comparisons) would require a
> > separate column with the ordinal integer value or the name string; in the
> > latter case, the space requirement is rather superfluous relatively to
> that of
> > the enum value itself. It seems to me this could be used as a partial
> solution
> > at least to expression-based indexes (Derby-455), by requiring indexes on
> > simple columns which may be virtual, or implementing the index by
> defining a
> > hidden virtual column, or something of the kind. Do you know if there's
> any
> > Derby improvement suggestion in that direction? (I havent'found any).
>
> Not that I'm aware of, but they sound like useful suggestions.
>
> For the Enum case, one alternative solution may be to improve the UDT
> support to allow comparisons and ordering if the Java type implements
> java.lang.Comparable. Since Enum implements Comparable, that should
> allow creating indexes directly on an Enum-based UDT column.
>
> --
> Knut Anders
>

Re: Are generated columns stored in the DB or calculated on the fly?

[Knut Anders Hatlen <kn...@oracle.com>]

Uriah Eisenstein <ur...@gmail.com> writes:

> Hi Knut,
> Thanks for your answer. May I suggest that this be added to the documentation
> of generated columns?

Yes, you may. :) Please file a JIRA report so that it's not forgotten.

> I think support for virtual columns, as they're indeed called in some other
> DBs, would really be nice though. For instance, Java Enums fit nicely as a
> UDT, but indexing on them (or even simple comparisons) would require a
> separate column with the ordinal integer value or the name string; in the
> latter case, the space requirement is rather superfluous relatively to that of
> the enum value itself. It seems to me this could be used as a partial solution
> at least to expression-based indexes (Derby-455), by requiring indexes on
> simple columns which may be virtual, or implementing the index by defining a
> hidden virtual column, or something of the kind. Do you know if there's any
> Derby improvement suggestion in that direction? (I havent'found any).

Not that I'm aware of, but they sound like useful suggestions.

For the Enum case, one alternative solution may be to improve the UDT
support to allow comparisons and ordering if the Java type implements
java.lang.Comparable. Since Enum implements Comparable, that should
allow creating indexes directly on an Enum-based UDT column.

-- 
Knut Anders

Re: Are generated columns stored in the DB or calculated on the fly?

[Uriah Eisenstein <ur...@gmail.com>]

Hi Knut,
Thanks for your answer. May I suggest that this be added to the
documentation of generated columns?
I think support for virtual columns, as they're indeed called in some other
DBs, would really be nice though. For instance, Java Enums fit nicely as a
UDT, but indexing on them (or even simple comparisons) would require a
separate column with the ordinal integer value or the name string; in the
latter case, the space requirement is rather superfluous relatively to that
of the enum value itself. It seems to me this could be used as a partial
solution at least to expression-based indexes
(Derby-455<https://issues.apache.org/jira/browse/DERBY-455>),
by requiring indexes on simple columns which may be virtual, or implementing
the index by defining a hidden virtual column, or something of the kind. Do
you know if there's any Derby improvement suggestion in that direction? (I
havent'found any).
Regards,
Uriah Eisenstein

On Thu, Sep 2, 2010 at 10:46 PM, Knut Anders Hatlen
<kn...@oracle.com>wrote:

> Uriah Eisenstein <ur...@gmail.com> writes:
>
> > Hi,
> > I searched around for the actual implementation of column "generated
> > always as " but couldn't find anything, except maybe by
> > digging into Derby source code which I don't want to... I want to
> > know whether a generated column is a "real" column existing within
> > the DB storage, and is updated whenever the columns it depends on are
> > updated - or is it a "virtual" column which is recalculated whenever
> > it is read. I don't have a specific need for it right now, but such
> > columns can be useful for indexing and such, e.g. of case-insensitive
> > strings or of UDTs, so I think it is useful to know whether the price
> > is mainly in space requirements (if they're "real") or just in
> > performance when reading.
>
> Hi Uriah,
>
> The generated columns are physical columns stored in the tables, and
> they are updated whenever a column they reference changes. You can also
> create indexes on the generated columns.
>
> --
> Knut Anders
>

Re: Are generated columns stored in the DB or calculated on the fly?

[Knut Anders Hatlen <kn...@ORACLE.COM>]

Uriah Eisenstein <ur...@gmail.com> writes:

> Hi,
> I searched around for the actual implementation of column "generated
> always as " but couldn't find anything, except maybe by
> digging into Derby source code which I don't want to... I want to
> know whether a generated column is a "real" column existing within
> the DB storage, and is updated whenever the columns it depends on are
> updated - or is it a "virtual" column which is recalculated whenever
> it is read. I don't have a specific need for it right now, but such
> columns can be useful for indexing and such, e.g. of case-insensitive
> strings or of UDTs, so I think it is useful to know whether the price
> is mainly in space requirements (if they're "real") or just in
> performance when reading.

Hi Uriah,

The generated columns are physical columns stored in the tables, and
they are updated whenever a column they reference changes. You can also
create indexes on the generated columns.

-- 
Knut Anders