Oracle 11g Virtual Column(原创)

Virtual Column
Oracle Database 11g lets you include a virtual column in a table. Unlike normal columns, a virtual column’s values aren’t inserted directly into a table. The virtual column you specify is always based on computing an expression or a function based on one or more other columns in the same table. Once you create a virtual column, you can query it just as you do any other column.
Virtual columns have the following important features:

• You can index a virtual column.
• You can use a virtual column in all types of DDL and DML statements.
• The database doesn’t store the values of the virtual column on disk because these values are only computed on-the-fly when you reference the virtual column.
• The datatype for a virtual column is optional. If you don’t explicitly specify the datatype, the virtual column will inherit the same datatype as the underlying expression.
• You can collect optimizer statistics on a virtual column.
• You can partition a table or an index on a virtual column.

You can create a virtual column either when you create a table, or later on, by using the alter table statement. There are two ways to create a virtual column. The first method, shown here, is to create the virtual column when you create the table:

SQL> create table  admin_emp (
         empno      NUMBER(5) PRIMARY KEY,
         ename      VARCHAR2(15) NOT NULL,
         job        VARCHAR2(10),
         mgr        NUMBER(5),
         hiredate   DATE DEFAULT (sysdate),
         photo      BLOB,
         SAL        number(7,2),
         hrly_rate  NUMBER(7,2) GENERATED ALWAYS AS (sal/2080),
         comm       NUMBER(7,2),
         deptno     NUMBER(3) NOT NULL
     );

The column HRLY_RATE is a virtual column. You must specify the generated always as clause when you create a virtual column. Actually, the generated always part of this clause is optional. The generated always clause tells us that the database doesn’t store the column values on disk, but rather, generates them only when a SQL statement refers to this virtual column. The last part of the clause (as) shows the expression the database uses to compute the values for the virtual column. In this example, the values of the HRLY_RATE column are generated from the SAL column, by computing the expression sal/2080. Because the SAL column provides the annual salary, the expression sal/2080 gives you the hourly salary for an employee.

The following restrictions apply to the creation of a virtual column:

• You create a virtual column only on a heap table, which is the normal Oracle table. You can’t create a virtual column on an index-organized, temporary, external, object, or cluster table.
• A virtual column can’t refer to another virtual column.
• A virtual column can be built only on the columns from the same table as the virtual column is in.
• The output of the virtual column must always be a scalar value.
• The virtual column can’t be an Oracle-supplied datatype or a user-defined type, LOB, or LONG RAW type.

Note that you can’t directly update a virtual column. That is, the following statement would fail if we assume that HRLY_RATE is a virtual column:
SQL> update table employees
     set hrly_rate ...
You can, however, specify a virtual column in the where clause of an update statement. Similarly, you can specify a virtual column in the where clause of a delete statement.
The second way to create a virtual column is to do so after table creation, by using the alter table statement, as shown here:
SQL> alter table employees add (income AS (salary + (salary*commission_pct)));

Virtual Column-Based Partitioning

Once you create a table with one or more virtual columns, you can then employ the new virtual column-based partitioning scheme to partition that table.

SQL> CREATE TABLE users (
       id           NUMBER,
       username     VARCHAR2(20),
       first_letter VARCHAR2(1)
       GENERATED ALWAYS AS
     (
         UPPER(SUBSTR(TRIM(username), 1, 1))
     ) VIRTUAL
       )
     PARTITION BY LIST (first_letter)
     (
       PARTITION part_a_g VALUES ('A','B','C','D','E','F','G'),
       PARTITION part_h_n VALUES ('H','I','J','K','L','M','N'),
       PARTITION part_o_u VALUES ('O','P','Q','R','S','T','U'),
       PARTITION part_v_z VALUES ('V','W','X','Y','Z')
     )

enable row movement;
The following code inserts two rows into each partition defined in the table.
SQL> INSERT INTO users (id, username) VALUES (1, 'Andy Pandy');
SQL> INSERT INTO users (id, username) VALUES (1, 'Burty Basset');
SQL> INSERT INTO users (id, username) VALUES (1, 'Harry Hill');
SQL> INSERT INTO users (id, username) VALUES (1, 'Iggy Pop');
SQL> INSERT INTO users (id, username) VALUES (1, 'Oliver Hardy');
SQL> INSERT INTO users (id, username) VALUES (1, 'Peter Pervis');
SQL> INSERT INTO users (id, username) VALUES (1, 'Veruca Salt');
SQL> INSERT INTO users (id, username) VALUES (1, 'Wiley Cyote');
SQL> COMMIT;
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'USERS');
The following query shows the data was distributed as expected.
SQL> COLUMN table_name FORMAT A25
SQL> COLUMN partition_name FORMAT A20
SQL> COLUMN high_value FORMAT A40
SQL> SELECT table_name, partition_name, high_value, num_rows
       FROM user_tab_partitions
      ORDER BY table_name, partition_name;
     TABLE_NAME                PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
     ------------------------- -------------------- ---------------------------------------- ----------
     USERS                     PART_A_G             'A', 'B', 'C', 'D', 'E', 'F', 'G'                 2
     USERS                     PART_H_N             'H', 'I', 'J', 'K', 'L', 'M', 'N'                 2
     USERS                     PART_O_U             'O', 'P', 'Q', 'R', 'S', 'T', 'U'                 2
     USERS                     PART_V_Z             'V', 'W', 'X', 'Y', 'Z'                           2
     4 rows selected.

The last line of the code in the example shows that you can specify row movement when partitioning on a virtual column. When you enable row movement, if the virtual column’s value belongs to another partition, a row migrates to the appropriate partition from its current partition.

Note:if you don't enable row movement, when you update the partition key column, you'll get ORA-14402 error

 

参考至:《McGraw.Hill.OCP.Oracle.Database.11g.New.Features.for.Administrators.Exam.Guide.Apr.2008》

       http://www.oracle-base.com/articles/11g/partitioning-enhancements-11gr1.php

本文原创，转载请注明出处、作者

如有错误，欢迎指正

邮箱:czmcj@163.com