iAnywhere.com - Working with computed columns

Working with computed columns

A computed column is a column whose value is an expression that can refer to the values of other columns, termed dependent columns, in the same row. Computed columns are especially useful in situations where you want to index a complex expression that may include the values of one or more dependent columns. During query optimization, the Adaptive Server Anywhere optimizer automatically attempts to transform a predicate involving a complex expression into one that simply refers to the computed column's definition. For example, suppose that you want to query a table containing summary information about product shipments:

CREATE TABLE SHIPMENTS(
   SHIPMENT_ID   INTEGER NOT NULL PRIMARY KEY,
   SHIPMENT_DATE TIMESTAMP,
   PRODUCT_CODE  CHAR(20) NOT NULL,
   QUANTITY      INTEGER NOT NULL,
   TOTAL_PRICE   DECIMAL(10,2) NOT NULL )

In particular, the query is to return those shipments whose average cost is between two and four dollars. The query could be written as

SELECT *
FROM SHIPMENTS
WHERE ( TOTAL_PRICE / QUANTITY ) BETWEEN 2.00 AND 4.00

but the predicate in the WHERE clause is not sargable since it does not refer to a single base column. If the size of the SHIPMENTS table is relatively large, an indexed retrieval might be appropriate rather than a sequential scan. You can do this by creating a computed column AVERAGE_COST for the SHIPMENTS table, as follows:

ALTER TABLE shipments
ADD average_cost DECIMAL(30,22)
COMPUTE (total_price / quantity)

CREATE INDEX idx_avg_cost ON shipments( average_cost ASC )

Choosing the type of the computed column is important; the Adaptive Server Anywhere optimizer replaces only complex expressions by a computed column if the data type of the expression in the query precisely matches the data type of the computed column. To determine what the type of any expression is, you can use the EXPRTYPE() built-in function that returns the expression's type in ready-to-use SQL terms:

SELECT EXPRTYPE( 'SELECT (TOTAL_PRICE / QUANTITY ) AS X FROM
SHIPMENTS', 1 ) 
FROM DUMMY

For the SHIPMENTS table, the above query returns NUMERIC(30,22). During optimization, the Adaptive Server Anywhere optimizer rewrites the query above as

SELECT *
FROM SHIPMENTS
WHERE AVERAGE_COST BETWEEN 2.00 AND 4.00

and the predicate in the WHERE clause is now a sargable one, making it possible for the optimizer to choose an indexed scan, using the new idx_avg_cost index, for the query's access plan. Values of computed columns are automatically maintained by the database server as rows are inserted and updated. Most applications should never need to update or insert computed column values directly; however, since computed columns are base columns like any other, they can be directly referenced in predicates and in expressions when it makes sense to do so.

Although you can use INSERT, UPDATE, or LOAD TABLE statements to insert values in computed columns, this is neither the recommended nor intended application of this feature. The LOAD TABLE statement permits the optional computation of computed columns, which can aid the DBA during complex unload/reload sequences, or when it is vital that the value of a computed column stay constant when the COMPUTE expression refers to non-deterministic values, such as CURRENT TIMESTAMP.

Modifying computed column expressions

You can change the expression used in a computed column with the ALTER TABLE statement. The following statement changes the expression that a computed column is based on.

ALTER TABLE table_name
ALTER column-name SET COMPUTE ( new-expression )

The column is recalculated when this statement is executed. If the new expression is invalid, the ALTER TABLE statement fails.

The following statement stops a column from being a computed column.

ALTER TABLE table_name
ALTER column-name DROP COMPUTE

Existing values in the column are not changed when this statement is executed, but they are no longer updated automatically.

Inserting and updating computed columns
When computed columns are recalculated