Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Working with Database Objects » Working with tables

Managing foreign keys (SQL) Next Page

Working with computed columns

A computed column is a column whose value is an expression that can refer to the values of other columns, called dependent columns, in the same row. Computed columns are especially useful in situations where you want to index a complex expression that can include the values of one or more dependent columns. The database server will use the computed column wherever it see an expression that matches the computed column's COMPUTE expression; this includes the SELECT list as well as predicates. However, if the query expression contains a special value, such as CURRENT TIMESTAMP, this matching does not occur. For a list of special values that prevent matching, see Special values.

During query optimization, the SQL 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:

   ShipmentDate TIMESTAMP,
   ProductCode CHAR(20) NOT NULL,
   TotalPrice 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 follows:

   FROM Shipments
   WHERE ( TotalPrice / Quantity ) BETWEEN 2.00 AND 4.00;

However, in this query the predicate in the WHERE clause is not sargable since it does not refer to a single base column. See Predicate analysis. 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 named AverageCost for the Shipments table, as follows:

   ADD AverageCost DECIMAL(30,22)
   COMPUTE( TotalPrice / Quantity );
 CREATE INDEX IDX_average_cost
   ON Shipments( AverageCost ASC );

Choosing the type of the computed column is important; the SQL 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 ( TotalPrice/Quantity ) AS X FROM Shipments', 1 )

For the Shipments table, the above query returns NUMERIC(30,22). During optimization, the SQL Anywhere optimizer rewrites the query above as

   FROM Shipments
   WHERE AverageCost
   BETWEEN 2.00 AND 4.00;

In this case, 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_average_cost index, for the query's access plan.

Altering 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 column-name

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
Recalculating computed columns