Virtual columns in Oracle 11g

In Oracle 11g, virtual columns are supported. Note that virtual columns can be dynamically calculated based on other columns,

Syntax:

Column_name [datatype] [generated always] as [expression] [Virtual]

Example:

Create Table employee (

Empl_id number,

Empl_nm varchar2 (50 ),

Monthly_sal number (10, 2 ),

Bonus number (10, 2 ),

Total_sal number (10, 2) generated always as (monthly_sal * 12 + bonus ));

Let's look at the data dictionary again:

Select column_name, data_type, data_length, data_default, virtual_column from user_tab_cols where table_name = 'Employee '; column_name | data_type | data_length | data_default | virtual_column empl_id | Number | 22 | null | noempl_nm | varchar2 | 50 | null | nomonthly_sal | Number | 22 | null | nobonus | Number | 22 | null | nototal_sal | Number | 22 | "monthly_sal" * 12 + "bonus" | Yes

You can add columns to a table:

Drop table employee purge;

Create Table employee (empl_id number, empl_nm varchar2 (50), monthly_sal number (10, 2), bonus number (10, 2 )); alter table employee add (total_sal as (monthly_sal * 12 + bonus ));

You can add other columns to a table, but cannot add or modify virtual columns:

Insert into employee (empl_id, empl_nm, monthly_sal, bonus)

With data

(Select 100 empl_id, 'aaa' empl_nm, 20000 monthly_sal, 3000 bonus from dual Union Select 200, 'bbb', 12000,200 0 from dual Union select 300, 'ccc ', 32100,100 0 from dual Union select 400, 'ddd ', 24300,500 0 from dual Union select 500, 'Eee', 12300,800 0 from dual) Select * from data;

-- Indexes can be created for virtual columns. The index type is function index.

Create index idx_total_sal on employee (total_sal); select index_name, index_type from user_indexes where table_name = 'employe'; index_name index_type idx_total_sal function-based Normal

The virtual column can also be used as the partition key of the partition table.

For example:

Create Table test_part (I1 int, I2 as (I1 + 100 ))

Partition by range (I2)

(Partition Part1 values less than (100 ),

Partition Part2 values less than (200 ),

Partition Part3 values less than (maxvalue ));