DBA Hub

📋Steps in this guide1/2

Materialized Virtual Columns (Expression Columns) in Oracle Database 23ai/26ai

In Oracle Database 23ai/26ai (23.7) we have the option to materialize the virtual column value. This increases disk space usage, but reduces the need to repeatedly regenerate the values, while allowing it to remain correct through DML operations.

oracle 23configurationintermediate
by OracleDba
25 views
1

Creating Materialized Virtual Columns (Expression Columns)

The syntax for defining a virtual column is listed below. The default action for virtual columns is for them to be truly virtual. The keyword is optional. The following example creates a regular virtual column. To create a materialized virtual column, we substitute the keyword. Looking at the view shows us the materialized virtual column is not listed as a virtual column, but we can see the expression is present in the column as we would expect for a virtual column. The materialized virtual column is not listed in the view. Despite the column not being a true virtual column, the value is still maintained through DML changes. We insert into the two tables and display the contents. Notice the column is populated. We update the column in both tables and see the correct values have been generated by the virtual columns. We are not able to manually amend the value in the materialized virtual column.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
column_name [datatype] [generated always] as (expression) [virtual|materialized]

create table t1 (
  id              number,
  product         varchar2(50),
  price           number(10,2),
  price_with_tax  number(10,2) generated always as (round(price*1.2,2)) virtual
);

create table t2 (
  id              number,
  product         varchar2(50),
  price           number(10,2),
  price_with_tax  number(10,2) generated always as (round(price*1.2,2)) materialized
);

column table_name format a20
column column_name format a20
column virtual_column format a14
column data_default format a20

select table_name,
       column_name,
       virtual_column,
       data_default
from   user_tab_cols
where  column_name = 'PRICE_WITH_TAX'
order by 1, 2;

TABLE_NAME           COLUMN_NAME          VIRTUAL_COLUMN DATA_DEFAULT
-------------------- -------------------- -------------- --------------------
T1                   PRICE_WITH_TAX       YES            ROUND("PRICE"*1.2,2)
T2                   PRICE_WITH_TAX       NO             ROUND("PRICE"*1.2,2)

SQL>

column table_name format a14
column virtual_column_name format a19
column virtual_column_expression format a25

select table_name,
       virtual_column_name,
       virtual_column_expression
from   user_table_virtual_columns
where  virtual_column_name = 'PRICE_WITH_TAX'
order by 1, 2;

TABLE_NAME     VIRTUAL_COLUMN_NAME VIRTUAL_COLUMN_EXPRESSION
-------------- ------------------- -------------------------
T1             PRICE_WITH_TAX      ROUND("PRICE"*1.2,2)

SQL>

insert into t1 (id, product, price) values (1, 'computer', 1500);
insert into t1 (id, product, price) values (2, 'bike', 1000);

insert into t2 (id, product, price) values (1, 'computer', 1500);
insert into t2 (id, product, price) values (2, 'bike', 1000);
commit;


column product format a15

select * from t1;

        ID PRODUCT              PRICE PRICE_WITH_TAX
---------- --------------- ---------- --------------
         1 computer              1500           1800
         2 bike                  1000           1200

SQL>


select * from t2;

        ID PRODUCT              PRICE PRICE_WITH_TAX
---------- --------------- ---------- --------------
         1 computer              1500           1800
         2 bike                  1000           1200

SQL>

update t1
set    price = price + 10;

update t2
set    price = price + 10;
commit;


select * from t1;

        ID PRODUCT              PRICE PRICE_WITH_TAX
---------- --------------- ---------- --------------
         1 computer              1510           1812
         2 bike                  1010           1212

SQL>

select * from t2;

        ID PRODUCT              PRICE PRICE_WITH_TAX
---------- --------------- ---------- --------------
         1 computer              1510           1812
         2 bike                  1010           1212

SQL>

update t2
set    price_with_tax = 2000;
       *
ERROR at line 2:
ORA-54017: UPDATE operation disallowed on virtual columns
Help: https://docs.oracle.com/error-help/db/ora-54017/

SQL>
2

Altering Materialized Virtual Columns (Expression Columns)

The command doesn't error if we try to switch a virtual column to a materialized virtual column, but it doesn't actually do anything. In the following example we alter the tax calculation and add the keyword. We can see the tax calculation is altered, but the column remains virtual. If we try to alter the expression of the materialized virtual column we get an error. If we want to change the definition, we need to drop the column and re-add it with the new expression. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
alter table t1 modify (
  price_with_tax number(10,2) as (round(price*1.3,2)) materialized
);


column table_name format a20
column column_name format a20
column virtual_column format a14
column data_default format a20

select table_name,
       column_name,
       virtual_column,
       data_default
from   user_tab_cols
where  column_name = 'PRICE_WITH_TAX'
order by 1, 2;

TABLE_NAME           COLUMN_NAME          VIRTUAL_COLUMN DATA_DEFAULT
-------------------- -------------------- -------------- --------------------
T1                   PRICE_WITH_TAX       YES            ROUND("PRICE"*1.3,2)
T2                   PRICE_WITH_TAX       NO             ROUND("PRICE"*1.2,2)

SQL>

alter table t2 modify (
  price_with_tax number(10,2) as (round(price*1.3,2)) 
);

  *
ERROR at line 2:
ORA-54026: Real column cannot have an expression
Help: https://docs.oracle.com/error-help/db/ora-54026/

SQL>

alter table t2 drop column price_with_tax;

alter table t2 add (
  price_with_tax number(10,2) as (round(price*1.3,2)) materialized
);


column table_name format a20
column column_name format a20
column virtual_column format a14
column data_default format a20

select table_name,
       column_name,
       virtual_column,
       data_default
from   user_tab_cols
where  column_name = 'PRICE_WITH_TAX'
order by 1, 2;

TABLE_NAME           COLUMN_NAME          VIRTUAL_COLUMN DATA_DEFAULT
-------------------- -------------------- -------------- --------------------
T1                   PRICE_WITH_TAX       YES            ROUND("PRICE"*1.3,2)
T2                   PRICE_WITH_TAX       NO             ROUND("PRICE"*1.3,2)

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!