[Oracle 11g new feature] Virtual column

Summary: Virtual columns can be used for special occasions, essentially similar to function columns (that is, the columns already in the table are computed by the function), "The virtual columns are not stored in the database and are returned to the user by the Oracle backend when the query is executed", so the virtual columns do not increase storage space, but because of the need to calculate, Additional CPU time is required.

---Using virtual columns when creating tables

Sql> CREATE TABLE test4 (ID number,name varchar2), hash_id as(Ora_hash (ID)));

Table created

---ALTER TABLE new virtual column

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 are not supplied by the user, but it's derived by Oracle server Implicitly from the other columns. Most importantly, their disk space consumption was NULL because their data is not stored in the table.

Key points about Virtual columns:

1) Data can not is inserted into virtual column

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

3) It can used as normal columns without any restriction. It can be constrained, indexed, and can is used in DML or DDL statements. Note that it cannot is 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 Table S

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

Syntax

COLUMN [DATA TYPE] [GENERATED always] as (EXPRESSION) [VIRTUAL]

CREATE TABLE Virtual_column (member_id number), member_name varchar2 (+), 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), member_name varchar2 (+), 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 @ line 1:ora-54013:insert Operation Disallowe D 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 on line 1:ora-02290: Check constraint (Quarterly_salary_check) violated

[Oracle 11g new feature] Virtual column