Computed columns allow you to create a shorthand term for an expression, such as “Pay” for “Salary + Commission,” and to make that column indexable, as long as its datatype is indexable. Nonindexable datatypes include:
text
unitext
image
Java class
bit
Computed columns are intended to improve application development and maintenance efficiency. By centralizing expression logics in the table definition (DDL), and giving expressions meaningful aliases, computed columns make greatly simplified and readable DMLs.You can change expressions by simply modifying the computed column definitions.
Computed columns are particularly useful when you must index a column whose defining expression is either a non-deterministic expression or function, or calls a non-deterministic expression or function. For example, the function getdate() always returns the current date, so it is non-deterministic. To index a column using getdate(), you can build a materialized computed column and then index it:
create table rental (cust_id int, start_date as getdate()materialized, prod_id in)) create index ind_start_date on rental (start_date)
An important feature of computed columns is that they can be used to compose and decompose complex datatypes. You can use computed columns either to make a complex datatype from simpler elements (compose), or to extract one or more elements from a complex datatype (decompose). Complex datatypes are usually composed of individual elements or fragments. You can define automatic decomposing or composing of these complex datatypes when you define the table. For example, suppose you want to store XML “order” documents in a table, along with some relational elements: order_no, part_no, and customer. Using create table, you can define an extraction with computed columns:
CREATE TABLE orders(xml_doc IMAGE,
order_no COMPUTE xml_extract("order_no", xml_doc)MATERIALIZED,
part_no COMPUTE xml_extract ("part_no", xml_doc)MATERIALIZED,
customer COMPUTE xml_extract("customer", xml_doc)MATERIALIZED)
Each time you insert a new XML document into the table, the document’s relational elements are automatically extracted into the computed columns.
Or, to present the relational data in each row as an XML document, specify mapping the relational data to an XML document using a computed column in the table definition. For example, define a table:
CREATE TABLE orders (order_no INT,part_no INT, quantity SMALLINT, customer VARCHAR(50))
Later, to return an XML representation of the relational data in each row, add a computed column using alter table:
ALTER TABLE orders ADD order_xml COMPUTE order_xml(order_no, part_no, quantity, customer)
Then use a select statement to return each row in XML format:
SELECT order_xml FROM orders
Computed columns supports comparison, order by, and group by ordering of complex datatypes, such as XML, text, unitext, image, and Java classes. You can use computed columns to extract relational elements of complex data, which you can use to define ordering.
You can also use computed columns to transform data into different formats, to customize data presentations for data retrieval. This is called user-defined sort order. For example, this query returns results in the order of the server’s default character set and sort order, usually ASCII alphabetical order:
SELECT name, part_no, listPrice FROM parts_table ORDER BY name
You can use computed columns to present your query result in a case-insensitive format, such as ordering based on special-case acronyms, as in the ordering of stock market symbols, or you can use system sort orders other than the default sort order. To transform data into a different format, use either the built-in function sortkey, or a user-defined sort order function.
For example, you can add a computed column called name_in_myorder with the user-defined function Xform_to_myorder():
ALTER TABLE parts_table add name_in_myorder COMPUTE Xform_to_myorder(name)MATERIALIZED
The following query then returns the result in the customized format:
SELECT name, part_no, listPrice FROM parts_table ORDER BY name_in_myorder
This approach allows you to materialize the transformed ordering data and create indexes on it.
If you prefer, you can do the same thing using data manipulation language (DML):
SELECT name, part_no, listPrice FROM parts_table
ORDER BY Xform_to_myorder(name)
However, using the computed column approach allows you to materialize the transformed ordering data and create indexes on it, which improves the performance of the query.
Typical decision support system applications require intensive data manipulation, correlation, and collation in the data analysis process. Such applications frequently use expressions and functions in queries, and special user-defined ordering is often required. Using computed columns and function-based indexes simplifies the tasks necessary in such applications, and improves performance.
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
