Introduction to Oracle 11g Virtual column Vsan

Oracle 11G Virtual column virtual Columnoracle 11G introduces a dummy column in a table, a virtual column is an expression that is evaluated at run time, not stored in the database, and cannot update the value of a virtual column. Defines the syntax for a virtual column: column_name [datatype] [GENERATED always] as [expression] [virtual]1. Virtual column can be used in select,update, The Where condition of the DELETE statement, but not for DML statement 2. Partition 3 can be made based on a virtual column. You can build indexes on virtual columns, and Oracle's function indexes are similar. 4. You can create a table with a virtual column on a virtual column: Creation table EMPLOYEE (empl_id number,empl_nm VARCHAR2, monthly_sal number (10,2 ), bonus number (10,2), total_sal number (10,2) GENERATED always as (monthly_sal*12 + bonus)); Total_sal is a virtual column view the following table definition content: 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_columnempl_id | number | 22 | null | NO empl_nm | VARCHAR2 | 50 | null | NO Monthly_sal | number | 22 | null | NO BONUS | number | 22 | Null                     | NO Total_sal | number | 22 | "Monthly_sal" *12+ "BONUS" | Yes Virtual_column is yes, it is a virtual column, we can also build a table statement with virtual, display the declaration as a virtual column drop TABLE EMPLOYEE PURGE; CREATE OR REPLACE FUNCTION get_empl_total_sal (p_monthly_sal number,p_bonus number) RETURN Numberdeterministi Cisbegin RETURN p_monthly_sal * + p_bonus; END; CREATE TABLE EMPLOYEE (empl_id number, empl_nm VARCHAR2 (), monthly_sal number (10,2), bonus number (10,2), to Tal_sal number (10,2) as (Get_empl_total_sal (monthly_sal, bonus)) virtual); We can create an index on a dummy Ployee (total_sal); SELECT index_name, Index_type from user_indexes WHERE table_name = ' EMPLOYEE ';  Index_name Index_type idx_total_sal function-based Normal This function is also the table Definition content drop function get_empl_total_sal; SELECT * from Employee;*error in line 0ora-00904: "Schema". " Get_empl_total_sal ": Invalid identifier can add a virtual column drop TABLE EMPLOYEE PURGE; CREATE TABLE EMPLOYEE (empl_id    Number, empl_nm VARCHAR2 (), monthly_sal number (10,2), bonus number (10,2)); ALTER TABLE EMPLOYEE ADD (total_sal as (Monthly_sal * + bonus)); The type of this new column is indeterminate when it is inferred from an expression. INSERT into employee (empl_id, EMPL_NM, Monthly_sal, bonus) with DATA as (SELECT empl_id, ' AAA ' empl_nm, 20000 monthl  Y_sal, bonus from DUAL Union Select, ' BBB ', 12000, and from DUAL Union Select, ' CCC ', 32100, + from DUAL Union Select, ' DDD ', 24300, DUAL Union Select, ' EEE ', 12300, 8000 from DUAL-select * from D ATA; SELECT * from employee; empl_id | empl_nm | Monthly_sal | BONUS | total_sal100 | AAA | 20000 | 3000 | 243000200 | BBB | 12000 | 2000 | 146000300 | CCC | 32100 | 1000 | 386200400 | DDD | 24300 | 5000 | 296600500 | EEE | 12300 | 8000 | 155600 executing an UPDATE statement on a virtual column is not allowed for update employee SET total_sal = 2000; Ora-54017:update operation disallowed on virtual columns ability to collect table statistics from a dummy columnEXEC Dbms_stats. Gather_table_stats (user, ' EMPLOYEE ');  SELECT column_name, Num_distinct, Display_raw (Low_value, data_type) Low_value, Display_raw (High_value, data_type) High_value from dba_tab_cols WHERE table_name = ' EMPLOYEE '; column_name | num_distinct | Low_value | High_valuetotal_sal | 5 | 146000 | 386200BONUS | 5 | 1000 | 8000monthly_sal | 5 | 12000 | 32100empl_nm | 5 | AAA | eeeempl_id | 5 | 100 | 500 partition based on virtual column drop TABLE EMPLOYEE PURGE; CREATE TABLE Employee (empl_id number, empl_nm VARCHAR2 (), monthly_sal number (10,2), bonus number (10,2), to Tal_sal number (10,2) as (monthly_sal*12 + bonus)) PARTITION by RANGE (total_sal) (PARTITION sal_200000 VALUES less THAN (2 00000), PARTITION sal_400000 values less THAN (400000), PARTITION sal_600000 values less THAN (600000), PARTITION sal_8000 Values less THAN (800000), PARTITION sal_default values less THAN (MAXVALUE)); InchSERT into employee (empl_id, EMPL_NM, Monthly_sal, bonus) with DATA as (SELECT empl_id, ' AAA ' empl_nm, 20000 monthly_ Sal, bonus from DUAL Union Select, ' BBB ', 12000, c from DUAL Union Select, ' CCC ', 32100, + from D UAL Union Select, ' DDD ', 24300, DUAL Union Select, ' EEE ', 12300, 8000 from DUAL-select * from DAT A EXEC Dbms_stats. Gather_table_stats (user, ' EMPLOYEE ', granularity = ' PARTITION '); SELECT table_name, partition_name, num_rowsfrom user_tab_partitions WHERE table_name = ' EMPLOYEE ' ORDER by Partition_na Me table_name | Partition_name | Num_rowsemployee | sal_200000 | 2EMPLOYEE | sal_400000 | 3EMPLOYEE | sal_600000 | 0EMPLOYEE | sal_800000 | 0EMPLOYEE | Sal_default | 0 The column referenced by the virtual column cannot be updated in the partitioned Case update employee SET monthly_sal = 30000 WHERE empl_id = 500; Ora-14402:updating partition key column would cause a partition change if you can update in the partition condition, you need to set the Enable ROW Movementalter TABLE EMP Loyee ENABLE ROW Movement; UPDATE employee SET monthly_sal = 80000 WHERE empl_id = 500; 1 row updated. Partitions on virtual columns that cannot be based on function expressions Create TABLE employee_new (empl_id number, empl_nm VARCHAR2 (), Monthly_sal number ( 10,2), bonus number (10,2), total_sal number (10,2) as (Get_empl_total_sal (monthly_sal, bonus))) PARTITION by RANGE ( Total_sal) (PARTITION sal_200000 values less THAN (200000), PARTITION sal_400000 values less THAN (400000), PARTITION sal_6 00000 values less THAN (600000), PARTITION sal_800000 values less THAN (800000), PARTITION sal_default values less THAN (M Axvalue)); ora-54021:cannot use PL/SQL expressions in partitioning or subpartitioning columns

Introduction to Oracle 11g Virtual column Vsan