02-06-2012, 12:41 PM
Guide to Using SQL: Computed and Automatic Columns
automatic-columns-132042.pdf (Size: 787.34 KB / Downloads: 198)
Computed columns are nothing new to Oracle Rdb and have been available since its first
release in 1984. A special type of column - know as a computed by column - defines a
calculation instead of a data type. This special column takes no space within the table but
allows the programmer to fetch the value at run-time using the select statement, or via a
cursor.
Oracle Rdb builds on this support in release V7.1 by adding a new type of computed
column; automatic columns. These columns are stored with the row but its value is
calculated at either insert or update time and like computed by columns they are read
only.
This article reviews all types of computed columns available in Rdb.1 The examples in the
article use SQL language from Oracle Rdb V7.1 or later.
AUTOMATIC Columns
Rdb includes another type of read-only column called AUTOMATIC AS columns.
Automatic columns are closely related to computed by columns; however, the computed
values are evaluated at INSERT and UPDATE time and stored in the database.
The database designer can define an AUTOMATIC column to be computed and stored
during INSERT, UPDATE or during both these statements. These columns can also be
used as part of an index key, and referenced by constraints.
Frequently Asked Questions about AUTOMATIC columns
What if the updated or inserted data is wrong, how can I fix it? A privileged user who
has the database privilege DBADM can use SET FLAGS 'AUTO_OVERRIDE' statement
to disable the AUTOMATIC column for new queries in the current session. The columns
are temporarily treated as read-write columns and can be updated, or new rows inserted.
This is a common requirement when reloading data during database restructuring. Use
the RDMS$SET_FLAGS logical or use the SET FLAGS statement with the keyword
AUTO_OVERRIDE prior to running update queries that reference the AUTOMATIC or
IDENTITY columns for update or insert.
The SQL IMPORT statement enables this flag automatically when importing tables with
AUTOMATIC columns so that previously recorded values are not replaced when loading
the new database.
I need to reload the current table for database restructuring. How do I retain the
current values for the automatic and identity columns? If the table is being
reorganized it may be necessary to unload the data and reload it after the storage map
and indexes for the table are re-created, yet the old data must remain the same.
Normally, RMU Unload does not unload columns marked as AUTOMATIC, you must use
the /VIRTUAL_FIELD qualifier with the keyword AUTOMATIC to request this action.