oracle如何创建虚拟列,ORACLE 11G 虚拟列 -- Virtual Column

11g以前的创建一个函数索引时, 实际上也是加了一个虚拟列的, 只不过DESC也不显示出来. 在11g中则可以在建表时加上虚拟列, 并可将这个列用于SQL, 分区列中, 还可以在上面建索引

官方new feature介绍:

1.4.2.11 Virtual Columns

Virtual columns are defined by evaluating an expression the results of which become the metadata of the columns for tables. Virtual columns can be defined at table creation or modification time.

Virtual columns enable application developers to define computations and transformations as the column (metadata) definition of tables without space consumption. This makes application development easier and less error-prone, as well as enhances query optimization by providing additional statistics to the optimizer for these virtual columns.

1.4.2.12 Virtual Column-Based Partitioning

In Oracle Database 11g, you can now partition key columns defined on virtual columns of a table.

Frequently, business requirements to logically partition objects does not match existing columns in a one-to-one manner. Oracle partitioning has been enhanced to allow a partitioning strategy being defined on virtual columns, thus enabling a more comprehensive match of the business requirements.

例子:

SQL> CREATE TABLE MAYJ.T3

2  (

3    A  NUMBER(2),

4    B  NUMBER(2),

5    C  AS ((A+B)*5) VIRTUAL

6  );

Table created.

SQL> desc t3;

Name                                      Null?    Type

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

A                                                  NUMBER(2)

B                                                  NUMBER(2)

C                                                  NUMBER

SQL> select dbms_metadata.get_ddl('TABLE','T3') from dual; --用get_ddl显示不全

DBMS_METADATA.GET_DDL('TABLE','T3')

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

CREATE TABLE "MAYJ"."T3"

(    "A" NUMBER(2,0),

"B" NUMBER(2,0),

"C" NUMB

SQL> insert into t3 (a,b) values (1,2);

1 row created.

SQL> select * from t3;

A          B          C

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

1          2         15

SQL> insert into t3 values (1,2,15);  --在虚拟列上insert或update都会失败

insert into t3 values (1,2,15)

*

ERROR at line 1:

ORA-54013: INSERT operation disallowed on virtual columns

SQL> create index idx_1 on t(c);

Index created.  --可以在虚拟列创建索引.