Tested on: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning option CREATE TABLE CMAGNO.TB_VIRTUAL ( NUM NUMBER, TEXT VARCHAR2(200), VL_1 NUMBER, COL_V1 AS (VL_1 * 2), COL_V2 NUMBER GENERATED ALWAYS AS (VL_1 + (VL_1/2)) VIRTUAL, CONSTRAINT PK_TB_VIRTUAL PRIMARY KEY (NUM) ); Table created. CREATE INDEX CMAGNO.IX_TB_VIRTUAL_01 ON CMAGNO.TB_VIRTUAL(COL_V1); Index created. CREATE INDEX CMAGNO.IX_TB_VIRTUAL_02 ON CMAGNO.TB_VIRTUAL(COL_V2); Index created. desc CMAGNO.TB_VIRTUAL Name Null? Type ----------------------------------------- -------- ---------------------------- NUM NOT NULL NUMBER TEXT VARCHAR2(200) VL_1 NUMBER COL_V1 NUMBER COL_V2 NUMBER COLUMN data_default FORMAT A50 SELECT column_name, data_default, DATA_TYPE,HISTOGRAM FROM dba_tab_columns WHERE table_name = 'TB_VIRTUAL'; COLUMN_NAME DATA_DEFAULT DATA_TYPE HISTOGRAM ------------------------------ -------------------------------------------------- ---------- --------------- COL_V2 "VL_1"+"VL_1"/2 NUMBER NONE COL_V1 "VL_1"*2 NUMBER NONE VL_1 NUMBER NONE TEXT VARCHAR2 NONE NUM NUMBER NONE declare i number; begin for i in 1..1000 loop insert into cmagno.tb_virtual (NUM, TEXT, VL_1) values (i,'TEXT:'||TO_CHAR(I),I*10); end loop; commit; end; / COL TEXT FORMAT A30 SELECT * FROM cmagno.tb_virtual WHERE ROWNUM<10; NUM TEXT VL_1 COL_V1 COL_V2 ---------- ------------------------------ ---------- ---------- ---------- 342 TEXT:342 3420 6840 5130 343 TEXT:343 3430 6860 5145 344 TEXT:344 3440 6880 5160 345 TEXT:345 3450 6900 5175 346 TEXT:346 3460 6920 5190 347 TEXT:347 3470 6940 5205 348 TEXT:348 3480 6960 5220 349 TEXT:349 3490 6980 5235 350 TEXT:350 3500 7000 5250 SET AUTOTRACE on explain SELECT * FROM CMAGNO.TB_VIRTUAL WHERE COL_V1=6840; NUM TEXT VL_1 COL_V1 COL_V2 ---------- ------------------------------ ---------- ---------- ---------- 342 TEXT:342 3420 6840 5130 Execution Plan ---------------------------------------------------------- Plan hash value: 3962365088 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 154 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TB_VIRTUAL | 1 | 154 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IX_TB_VIRTUAL_01 | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("COL_V1"=6840) Note ----- - dynamic sampling used for this statement (level=2) SET AUTOTRACE OFF; CREATE VIEW CMAGNO.VW_TB_VIRTUAL AS SELECT NUM, TEXT, COL_V1, COL_V1 * 2 COL_V1_DOUBLE FROM CMAGNO.TB_VIRTUAL; View created. DESC CMAGNO.VW_TB_VIRTUAL Name Null? Type ------------------- -------- ----------------- NUM NOT NULL NUMBER TEXT VARCHAR2(200) COL_V1 NUMBER COL_V1_DOUBLE NUMBER SELECT * FROM CMAGNO.VW_TB_VIRTUAL WHERE ROWNUM < 10; NUM TEXT COL_V1 COL_V1_DOUBLE ---------- ------------------------------ ---------- ------------- 342 TEXT:342 6840 13680 343 TEXT:343 6860 13720 344 TEXT:344 6880 13760 345 TEXT:345 6900 13800 346 TEXT:346 6920 13840 347 TEXT:347 6940 13880 348 TEXT:348 6960 13920 349 TEXT:349 6980 13960 350 TEXT:350 7000 14000 ==> LIMITATIONS UPDATE CMAGNO.TB_VIRTUAL SET COL_V1=1; 2 3 CMAGNO.TB_VIRTUAL * ERROR at line 2: ORA-54017: UPDATE operation disallowed on virtual columns ==> Virtual column can’t be used for: temporary table index-organized tables external objects clusters ==> Virtual column can be used for: heap-organized tables, partitioning, indexing,
Advertisements