How To Use Virtual Column

Oracle Database 11g (11.1) and later versions can store expressions directly in base tables as Virtual columns, also known as Generated columns.When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc.

• They are defined by an expression. The result of evaluation of this expression becomes the value of the column.
• The values of the virtual column are not stored in the database. Rather, it’s computed at run-time when you query the data.
• You can’t update (in SET clause of update statement) the values of virtual column. These are read only values, that are computed dynamically and any attempt to modify them will result into oracle error.

The syntax for defining a virtual column is listed below.

column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

If the datatype is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only.

where the parameters within [] are optional and can be omitted. If you don’t mention the datatype, Oracle will decide it based on the result of the expression.

Excepting the above points, a virtual column, exists just like any other column of a normal table and the following points apply to it:

1.    Virtual columns can be used in the WHERE clause of UPDATE and DELETE statement but they can’t be modified by DML.

2.    Statistics can be collected on them.

3.    They can be used as a partition key in virtual column based partitioning.

4.    Indexes can be created on them. As you might have guessed, oracle

5.    would create function based indexes as we create on normal tables.

6.    Constraints can be created on them.

SQL> CREATE TABLE orders
  2      ( order_id           NUMBER(12),
  3        order_date         TIMESTAMP WITH LOCAL TIME ZONE,
  4        order_mode         VARCHAR2(8),
  5        customer_id        NUMBER(6),
  6        order_status       NUMBER(2),
  7        order_total        NUMBER(8,2),
  8        sales_rep_id       NUMBER(6),
  9        promotion_id       NUMBER(6),
 10        CONSTRAINT orders_pk PRIMARY KEY(order_id)
 11      )
 12    PARTITION BY RANGE(order_date)
 13      ( PARTITION Q1_2005 VALUES LESS THAN (TO_DATE('01-APR-2005','DD-MON-YYYY')),
 14        PARTITION Q2_2005 VALUES LESS THAN (TO_DATE('01-JUL-2005','DD-MON-YYYY')),
 15        PARTITION Q3_2005 VALUES LESS THAN (TO_DATE('01-OCT-2005','DD-MON-YYYY')),
 16        PARTITION Q4_2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY'))
 17      );
    ( PARTITION Q1_2005 VALUES LESS THAN (TO_DATE('01-APR-2005','DD-MON-YYYY')),
                                          *
ERROR at line 13:
ORA-30078: partition bound must be TIME/TIMESTAMP WITH TIME ZONE literals
SQL> CREATE TABLE orders_vcol

  2      ( order_id           NUMBER(12),

  3        order_date         TIMESTAMP(6) WITH LOCAL TIME ZONE,

  4        order_mode         VARCHAR2(8),

  5        customer_id        NUMBER(6),

  6        order_status       NUMBER(2),

  7        order_total        NUMBER(8,2),

  8        sales_rep_id       NUMBER(6),

  9        promotion_id       NUMBER(6),

 10        vcol_gmt TIMESTAMP(6) AS (SYS_EXTRACT_UTC(order_date))

 11        virtual,

 12        CONSTRAINT orders_vpk PRIMARY KEY(order_id)

 13      )

 14  /

 

Table created.

SQL> col DATA_TYPE for a30

SQL> col DATA_DEFAULT for a30

SQL> SELECT column_name, data_type, data_length, data_default, virtual_column

  2  FROM user_tab_cols

  3  WHERE table_name = 'ORDERS_VCOL';

 

COLUMN_NAME                    DATA_TYPE                                                         DATA_LENGTH DATA_DEFAULT                   VIR

------------------------------ ------------------------------                                  ----------- ------------------------------         ---

ORDER_ID                           NUMBER                                                                 22                                NO

ORDER_DATE                     TIMESTAMP(6) WITH LOCAL TIME ZONE                11                                NO

ORDER_MODE                     VARCHAR2                                                              8                                NO

CUSTOMER_ID                    NUMBER                                                                  22                                NO

ORDER_STATUS                   NUMBER                                                                 22                                NO

ORDER_TOTAL                    NUMBER                                                                  22                                NO

SALES_REP_ID                   NUMBER                                                                    22                                NO

PROMOTION_ID                   NUMBER                                                                22                                NO

VCOL_GMT                       TIMESTAMP(6)                            11 SYS_EXTRACT_UTC("ORDER_DATE")  YES

9 rows selected.

Limitations on Virtual Columns

1.    A virtual column can only be of scalar datatype or XMLDATATYE. It can’t be a user defined type, LOB or RAW.

2.    All columns mentioned as part of the virtual column expression should belong to the same table.

3.    No DMLs are allowed on the virtual columns.

4.    The virtual column expression can’t reference any other virtual column.

5.    Virtual columns can only be created on ordinary tables. They can’t

6.    be created on index-organized, external, object, cluster or temporary

7.    tables.

8.    If a deterministic function is used as virtual column expression,

9.    that virtual column can’t be used as a partitioning key for virtual

10. column-based partitioning.

Notes and restrictions on virtual columns include:

·         Indexes defined against virtual columns are equivalent to function-based indexes.

·         Virtual columns can be referenced in the WHERE clause of updates and deletes, but they cannot be manipulated by DML.

·         Tables containing virtual columns can still be eligible for result caching.

·         Functions in expressions must be deterministic at the time of

·         table creation, but can subsequently be recompiled and made

·         non-deterministic without invalidating the virtual column. In such cases

·          the following steps must be taken after the function is recompiled:

o    Constraint on the virtual column must be disabled and re-enabled.

o    Indexes on the virtual column must be rebuilt.

o    Materialized views that access the virtual column must be fully refreshed.

o    The result cache must be flushed if cached queries have accessed the virtual column.

o    Table statistics must be regathered.

·         Virtual columns are not supported for index-organized, external, object, cluster, or temporary tables.

·         The expression used in the virtual column definition has the following restrictions:

o    It cannot refer to another virtual column by name.

o    It can only refer to columns defined in the same table.

o    If it refers to a deterministic user-defined function, it cannot be used as a partitioning key column.

o    The output of the expression must be a scalar value. It

o    cannot return an Oracle supplied datatype, a user-defined type, or LOB

o    or LONG RAW.

References:

1 Oracle Documentation
2 http://viralpatel.net/blogs/oracle-11g-new-feature-virtual-column/

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13750068/viewspace-2144136/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13750068/viewspace-2144136/