[oracle 11g 新特性] virtual column虛擬列

標籤：

總結：虛擬列可以使用於一些特殊場合，實質是類似於函數列（即以 表中已有的列 經過函數運算得來），“虛擬列不儲存在資料庫中，是在執行查詢時由oracle後台計算出來返回給使用者”，因此虛擬列不會增加儲存空間，但是由於需要計算，需要消耗額外的CPU Time。

---建立表時使用虛擬列

SQL> create table test4(id number,name varchar2(300),hash_id as(ora_hash(id)));

Table created

---alter 表新增虛擬列

SQL> create table test as select * from dba_objects;

Table created

SQL> alter table test add hash as (ora_hash(object_id));

Table altered

 

---below refer to https://oracleinstall.wordpress.com/2011/06/06/virtual-column-in-oracle-11g/

Virtual columns  allows users to create columns whose data is not supplied by the user, but it is derived by oracle server implicitly from other columns. Most importantly, their disk space consumption is NULL because their data is not stored in the table.

Key points about Virtual columns:

1)Data can not be inserted into virtual column

2) The virtual column and the columns to be used in the derivation of virtual column data must belong to the same table.

3)It can be used as normal columns without any restriction. It can be constrained, indexed, and can be used in DML or DDL statements. Note that it cannot be updated in UPDATE statement

4)We can partition the table based on virtual column

We can use below query to define virtual columns defined in the users schema.

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, HIDDEN_COLUMN FROM USER_TAB_COLS WHERE VIRTUAL_COLUMN = ‘YES’;

5)we can not create virtual columns on Temporary tables, object types, clusters, External tables and Index Organized Tables

I tried to create table using following statment and used sysdate in the virtual column expression and got the following error.

Syntax

COLUMN [DATA TYPE] [GENERATED ALWAYS] AS (EXPRESSION) [VIRTUAL]

create table virtual_column (MEMBER_ID number(10), MEMBER_NAME varchar2(25), BIRTH_DATE date, AGE_IN_MONTHS number(5) AS(SYSDATE-BIRTH_DATE))

ORA-54002: only pure functions can be specified in a virtual column expression

create table virtual_column (MEMBER_ID number(10), MEMBER_NAME varchar2(25), MONTHLY_SALARY number(10,2), ANNUAL_SALARY number(10,2) AS(12*MONTHLY_SALARY))

SQL> insert into virtual_column values(1,’POOJITHA’,5000,10000); insert into ukatru.virtual_column values(1,’POOJITHA’,5000,10000) * ERROR at line 1: ORA-54013: INSERT operation disallowed on virtual columns

insert into virtual_column(MEMBER_ID,MEMBER_NAME,MONTHLY_SALARY) values(1,’POOJITHA’,5000);

SQL> select * from ukatru.virtual_column;

MEMBER_ID MEMBER_NAME               MONTHLY_SALARY ANNUAL_SALARY ———- ————————- ————– ————- 1 POOJITHA                            5000         60000

Create index on virtual columns:

CREATE INDEX IDX_ANUAL_SALARY ON virtual_column (ANNUAL_SALARY);

If you query user_indexes table the index  is created as function based index.

SELECT INDEX_NAME,  INDEX_TYPE, FUNCIDX_STATUS FROM   USER_INDEXES WHERE TABLE_NAME = ‘VIRTUAL_COLUMN’;

INDEX_NAME                     INDEX_TYPE                  FUNCIDX_ —————————— ————————— ——– IDX_ANUAL_SALARY               FUNCTION-BASED NORMAL       ENABLED

we can use alter table command to add virtual column to the table.

alter table virtual_column add QUARTERLY_SALARY AS (MONTHLY_SALARY*3)

Adding constraint on the virtual column.

ALTER TABLE virtual_column ADD CONSTRAINT QUARTERLY_SALARY_CHECK CHECK(QUARTERLY_SALARY != 0);

SQL> insert into virtual_column(MEMBER_ID,MEMBER_NAME,MONTHLY_SALARY) values(2,’POOJITHA’,0); insert into virtual_column(MEMBER_ID,MEMBER_NAME,MONTHLY_SALARY) values(2,’POOJITHA’,0) * ERROR at line 1: ORA-02290: check constraint (QUARTERLY_SALARY_CHECK) violated

[oracle 11g 新特性] virtual column虛擬列