When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The syntax for defining a virtual column is listed below.
column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]
If the datatype is omitted, it is determined based on the result of the expression. The
GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only.
The script below creates and populates an employees table with two levels of commission. It includes two virtual columns to display the commission-based salary. The first uses the most abbreviated syntax while the second uses the most verbose form.
Querying the table shows the inserted data plus the derived commission-based salaries.CREATE TABLE employees ( id NUMBER, first_name VARCHAR2(10), last_name VARCHAR2(10), salary NUMBER(9,2), comm1 NUMBER(3), comm2 NUMBER(3), salary1 AS (ROUND(salary*(1+comm1/100),2)), salary2 NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL, CONSTRAINT employees_pk PRIMARY KEY (id) ); INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2) VALUES (1, 'JOHN', 'DOE', 100, 5, 10); INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2) VALUES (2, 'JAYNE', 'DOE', 200, 10, 20); COMMIT;
SELECT * FROM employees;
ID FIRST_NAME LAST_NAME SALARY COMM1 COMM2 SALARY1 SALARY2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 JOHN DOE 100 5 10 105 110
2 JAYNE DOE 200 10 20 220 240
2 rows selected.
The expression used to generate the virtual column is listed in the DATA_DEFAULT column of the [DBA|ALL|USER]_TAB_COLUMNS views.
COLUMN data_default FORMAT A50
SELECT column_name, data_default
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES';
COLUMN_NAME DATA_DEFAULT
------------------------------ --------------------------------------------------
ID
FIRST_NAME
LAST_NAME
SALARY
COMM1
COMM2
SALARY1 ROUND("SALARY"*(1+"COMM1"/100),2)
SALARY2 ROUND("SALARY"*(1+"COMM2"/100),2)
8 rows selected.
SQL>
Notes and restrictions on virtual columns include:
- Indexes defined against virtual columns are equivalent to function-based indexes.
- Virtual columns can be referenced in the
WHEREclause of updates and deletes, but they cannot be manipulated by DML. - Tables containing virtual columns can still be eligible for result caching.
- Functions in expressions must be deterministic at the time of table creation, but can subsequently be recompiled and made non-deterministic without invalidating the virtual column. In such cases the following steps must be taken after the function is recompiled:
- Constraint on the virtual column must be disabled and re-enabled.
- Indexes on the virtual column must be rebuilt.
- Materialized views that access the virtual column must be fully refreshed.
- The result cache must be flushed if cached queries have accessed the virtual column.
- Table statistics must be regathered.
- Virtual columns are not supported for index-organized, external, object, cluster, or temporary tables.
- The expression used in the virtual column definition has the following restrictions:
- It cannot refer to another virtual column by name.
- It can only refer to columns defined in the same table.
- If it refers to a deterministic user-defined function, it cannot be used as a partitioning key column.
- The output of the expression must be a scalar value. It cannot return an Oracle supplied datatype, a user-defined type, or LOB or LONG RAW.
Working with Virtual Column:
Creating a Virtual Column:
We will begin by creating a simple table with a single virtual column, as follows.
SQL> CREATE TABLE t 2 ( n1 INT 3 , n2 INT 4 , n3 INT GENERATED ALWAYS AS (n1 + n2) VIRTUAL 5 );
Table created.
We can see that the virtual column is generated from a simple expression involving the other columns in our table. Note that the VIRTUAL keyword is optional and is included for what Oracle calls "syntactic clarity".
Virtual column values are not stored on disk. They are generated at runtime using their associated expression (in our example, N1 + N2). This has some implications for the way we insert data into tables with virtual columns, as we can see below.
SQL> INSERT INTO t VALUES (10, 20, 30);
INSERT INTO t VALUES (10, 20, 30)
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns
We cannot explicitly add data to virtual columns, so we will attempt an insert into the physical columns only, as follows.
SQL> INSERT INTO t VALUES (10, 20);
INSERT INTO t VALUES (10, 20)
*
ERROR at line 1:
ORA-00947: not enough values
Despite the fact that we cannot insert or update virtual columns, they are still considered part of the table's column list. This means, therefore, that we must explicitly reference the physical columns in our insert statements, as follows.
SQL> INSERT INTO t (n1, n2) VALUES (10, 20);
1 row created.
Of course, fully-qualified inserts such as our example above are best practice so this should be a trivial restriction for most developers. Now we have data in our example table, we can query our virtual column, as follows.
SQL> SELECT * FROM t;
N1 N2 N3
---------- ---------- ----------
10 20 30
1 row selected.
Our expression is evaluated at runtime and gives the output we see above.
Indexes and Constraints
Virtual columns are valid for indexes and constraints. Indexes on virtual columns are essentially function-based indexes (this is covered in more detail later in this article). The results of the virtual column's expression are stored in the index. In the following example, we will create a primary key constraint on the N3 virtual column.
SQL> CREATE UNIQUE INDEX t_pk ON t(n3); Index created. SQL> ALTER TABLE t ADD 2 CONSTRAINT t_pk 3 PRIMARY KEY (n3) 4 USING INDEX; Table altered. If we try to insert data that results in a duplicate virtual column value, we should expect a unique constraint violation, as follows. SQL> INSERT INTO t (n1, n2) VALUES (10, 20); INSERT INTO t (n1, n2) VALUES (10, 20) * ERROR at line 1: ORA-00001: unique constraint (SCOTT.T_PK) violated As expected, this generates an ORA-00001 exception. It follows, therefore, that if we can create a primary key on a virtual column, then we can reference it from a foreign key constraint. In the following example, we will create a child table with a foreign key to the T.N3 virtual column. SQL> CREATE TABLE t_child 2 ( n3 INT 3 , CONSTRAINT tc_fk 4 FOREIGN KEY (n3) 5 REFERENCES t(n3) 6 ); Table created. We will now insert some valid and invalid data, as follows. SQL> INSERT INTO t_child VALUES (30); 1 row created. SQL> INSERT INTO t_child VALUES (40); INSERT INTO t_child VALUES (40) * ERROR at line 1: ORA-02291: integrity constraint (SCOTT.TC_FK) violated - parent key not found
Adding Virtual Column
Virtual columns can be added after table creation with an ALTER TABLE statement. In the following example, we will add a new virtual column to our existing table. We will include a check constraint for demonstration purposes.
SQL> ALTER TABLE t ADD 2 n4 GENERATED ALWAYS AS (n1 * n2) 3 CHECK (n4 >= 10);
Table altered.
As stated earlier, an index on a virtual column will store the results of the expression. A check constraint, however, will evaluate the expression at the time of adding or modifying data to the underlying table. This is seemingly obvious, because there is no associated storage structure (i.e. index) with a check constraint.
Our new virtual column, N4, has a check constraint to ensure that the product of the N1 and N2 columns is greater than 10. We will test this, as follows.
SQL> INSERT INTO t (n1, n2) VALUES (1, 2);
INSERT INTO t (n1, n2) VALUES (1, 2) * ERROR at line 1: ORA-02290: check constraint (SCOTT.SYS_C0010001) violated
0 comments:
Post a Comment