[DERBY-4831] Support for auto-calculated (virtual) columns

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.6.1.0
• Component/s: SQL
• Labels: derby_triage10_10
• Urgency: Low
• Bug behavior facts: Performance
• Assignee: Unassigned
• Reporter: Uriah Eisenstein
• Created: 04/Oct/10 08:12
• Updated: 28/Sep/12 18:25

Description

The suggestion is similar to generated columns, but with the column value not actually stored in the database, but rather recalculated whenever the column is accessed.
This saves storage space in return for additional processing.
It is also suggested to support creating indices on the calculated columns; this could be a partial solution to expression-based indexes (Derby-455). For instance, creating an index on an expression such as "upper (column)" requires Derby to recognize the expression in a query involving the column, so that it could take advantage of the index; this could be difficult for more complex expressions. Using a generated column would double the required storage for the original column. An auto-computed column could solve both problems.
(I don't know if there is any standard SQL syntax for defining such columns)

Issue Links

• relates to: DERBY-455 Add support for creating index on expressions
• relates to: DERBY-481 implement SQL generated columns