Introduction to Oracle 11G Virtual Column

Introduction to Oracle 11G Virtual Column [html] Oracle 11G Virtual Column Oracle 11G introduces Virtual columns in the table, Virtual Column is an expression, which is calculated at runtime, the virtual column value cannot be updated because it is not stored in the database. Syntax for defining a VIRTUAL column: column_name [datatype] [generated always] AS [expression] [VIRTUAL] 1. virtual columns can be used in the where condition of select, update, and delete statements, but cannot be used in DML Statement 2. partitions can be made based on virtual columns. 3. you can create indexes on virtual columns. oracle function indexes are similar. 4. you can CREATE a TABLE with virtual columns with constraints on the virtual column: create table employee (empl_id NUMBER, empl_nm VARCHAR2 (50), monthly_sal NUMBER (), bonus NUMBER ), total_sal NUMBER () generated always as (monthly_sal * 12 + bonus); total_sal is a virtual column to 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_COLUMN EMPL_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 indicates a VIRTUAL column. You can also add the VIRTUAL column in the TABLE creation statement to show the declaration as the VIRTUAL column drop table employee purge; create or replace function get_empl_total_sal (p_monthly_sal NUMBER, p_bonus NUMBER) RETURN NUM Ber deterministic is begin return p_monthly_sal * 12 + p_bonus; END; create table employee (empl_id NUMBER, empl_nm VARCHAR2 (50), monthly_sal NUMBER (10, 2), bonus NUMBER (10, 2 ), total_sal NUMBER () AS (get_empl_total_sal (monthly_sal, bonus) VIRTUAL); we can CREATE an index on the VIRTUAL column create index idx_total_sal ON employee (total_sal); SELECT index_name, index_type FROM user_indexes WHERE table_name = 'Employee'; IN DEX_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 at line 0 ORA-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 (50), monthly_sal NUMBER (10, 2 ), bonus NUMBER (); alter table employee add (total_sal AS (monthly_s Al * 12 + bonus); the type of the new column is uncertain and inferred based on the expression. Insert into employee (empl_id, empl_nm, monthly_sal, bonus) with data as (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; SELECT * FROM employee; EMPL_ID | EMPL_NM | MONTHLY_SAL | BONUS | TOTAL_SAL 100 | AAA | 20000 | 3000 | 243000 200 | BBB | 12000 | 2000 | 146000 300 | CCC | 32100 | 1000 386200 | DDD | 24300 | 5000 | 296600 500 12300 | EEE | 8000 | 155600 | the update Statement on the virtual column is not allowed to be executed. UPDATE employee SET total_sal = 2000; ORA-54017: UPDATE operation disallowed on virtual columns ability to collect table statistics from virtual columns EXEC partition (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_VALUE TOTAL_SAL | 5 | 146000 | 386200 BONUS | 5 | 1000 | 8000 MONTHLY_SAL | 5 | 12000 | 32100 EMPL_NM | 5 | AAA | EEE EMPL_ID | 5 | 100 | 500 drop table employee purge based on the virtual column partition; create table employee (empl_id NUMBER, empl_nm VARCHAR2 (50), monthly_sal NUMBER (10, 2), bonus NUMBER (10, 2), total_sal NUMBER (10, 2) AS (monthly_sal * 12 + bonus )) partition by range (total_sal) (PARTITION sal_200000 values less than (200000), PARTITION sal_400000 values less than (400000), PARTITION sal_600000 values less than (600000 ), PARTITION sal_800000 values less than (800000), PARTITION sal_default values less than (MAXVALUE); insert into employee (empl_id, empl_nm, monthly_sal, bonus) with data as (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; EXEC DBMS_STATS.GATHER_TABLE_STATS (user, 'Employee ', granularity => 'partition'); SELECT table_name, partition_name, num_rows FROM user_tab_partitions WHERE table_name = 'employe' order by partition_name; TABLE_NAME | PARTITION_NAME | NUM_ROWS EMPLOYEE | SAL_200000 | 2 EMPLOYEE | SAL_400000 | 3 EMPLOYEE | SAL_600000 | 0 EMPLOYEE | SAL_800000 | 0 EMPLOYEE | SAL_DEFAULT | 0 in the case of partitions, the columns referenced by the virtual column cannot be updated UPDATE employee SET monthly_sal = 30000 WHERE empl_id = 500; ORA-14402: updating partition key column wocould cause a partition change if the partition can be updated, enable row movement alter table employee enable row movement; UPDATE employee SET monthly_sal = 80000 WHERE empl_id = 500; 1 row updated. create table employee_new (empl_id NUMBER, empl_nm VARCHAR2 (50), monthly_sal NUMBER (), bonus NUMBER (), total_sal NUMBER) 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_600000 values less than (600000), PARTITION sal_800000 values less than (800000), PARTITION sal_default values less than (MAXVALUE); ORA-54021: cannot use PL/SQL expressions in partitioning or subpartitioning columns