This chapter describes an enhancement that provides easier data manipulation and faster data access, by allowing you to create computed columns, computed column indexes, and function-based indexes.
Computed columns – defined by an expression, whether from regular columns in the same row, functions, arithmetic operators,or path names.
Indexes on computed columns, or computed column indexes – indexes that contain one or more computed columns as index keys.
Function-based indexes – indexes that contain one or more expressions as index keys.
Deterministic property – a property assuring that an expression always returns the same results from a specified set of inputs.
Computed columns and function-based indexes similarly allow you to use an expression or a function as the basis for a more complex function.
Computed columns and function-based indexes differ in some respects:
A computed column provides both shorthand for an expression and indexability, while a function-based index provides no shorthand; it allows you to index the expression directly.
A computed column can be either deterministic or nondeterministic, but a function-based index must be deterministic. “Deterministic” means that if the input values in an expression are the same, the return values must also be the same.
Computed columns can be materialized or not materialized. Columns that are materialized are preevaluated and stored in the table when base columns are inserted or updated. The values associated with them are stored in both the data row and the index row. Any subsequent access to a materialized column does not require reevaluation; its preevaluated result is accessed. Once a column is materialized, each access to it returns the same value.
Columns that are not materialized are also called virtual columns; virtual columns become materialized when they are accessed. If a column is virtual, or not materialized, its result value must be evaluated each time the column is accessed. This means that if the virtual computed column is expression-based on, or calls a nondeterministic expression, it may return different values each time you access it.You may also encounter run-time exceptions, such as domain errors, when you access virtual computed columns.
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
