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
