//
you're reading...
Administration, SQL

Virtual Column

Sometimes you want to store some data in a table based on some expressions which rely on some other columns, functions etc. In simple words, the column generated from other source based on some action. So what’s your option?

One option is to write a trigger using some logic. It could results in changing the existing code. It may or may not be cause some performance issues. But if you are using Oracle Database 11g, the answer is simple – Virtual Column.

Virtual column is another new feature of Oracle Database 11g. The virtual columns derived from the expression or functions performed on the columns of the same table or by computing a function or expression. Unlike regular columns you can’t write or update values of the virtual columns. Also they are not stored on the disk but the database computes their values at the runtime by evaluating a set of expressions or functions.

Let’s take a look how to define a virtual column:

hr@O11R2>SELECT * FROM v$version;

BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

hr@O11R2>CREATE TABLE v_col_tab(
         sid NUMBER,
         student_name VARCHAR2(30),
         marks NUMBER,
         grade VARCHAR2(2) GENERATED ALWAYS AS
         (     	
           CASE
      	       WHEN marks>=90 THEN 'A'
       	       WHEN marks>75 THEN 'B'
       	       WHEN marks>55 THEN 'c'
      	       ELSE 'D'
           END
         )
);
Table created.

The syntax for creating Virtual column as a part of CREATE table or ALTER table statements:

column [datatype] [GENERATED ALWAYS] AS (<column_expression>) [VIRTUAL] [(inline constraint..)]

The keyword GENERATED ALWAYS specifies that the column values not stored on disk but generated as runtime. The AS expression evaluate the content of column.

Let’s perform some insert operation on table:

hr@O11R2>INSERT INTO  v_col_tab (sid, student_name, marks) VALUES (111, ‘D Kumar’, 78);
1 row created.

hr@O11R2>INSERT INTO  v_col_tab (sid, student_name, marks) VALUES (112, ‘P Gupta’, 87);
1 row created.

hr@O11R2>INSERT INTO  v_col_tab (sid, student_name, marks) VALUES (114, ‘S Sharan’, 54);
1 row created.

hr@O11R2>INSERT INTO  v_col_tab (sid, student_name, marks) VALUES (115, ‘K James’, 90);
1 row created.

hr@O11R2>SELECT * FROM v_col_tab;
SID     STUDENT_NAME                            MARKS           GR
-----	---------------------------------------	------------	---
111     D Kumar	                                78              B
112     P Gupta                                 87              B
114     S Sharan                                54              D
115     K James                                 90              A

hr@O11R2>commit;
Commit complete.

As you see here that virtual column values are populated automatically by the expression you specify.

However a virtual column is not stored on disk, they can be used in queries, DML, and DDL statements. They can be indexed, and you can collect statistics on them.

You can also specify the optional keyword VIRTUAL in the column definition as follows:

grade VARCHAR2(2) GENERATED ALWAYS AS
	(
           	CASE
             	WHEN marks >= 90 THEN 'A'
             	WHEN marks > 75 THEN 'B'
              	WHEN marks > 55 THEN 'c'
             	ELSE 'D'
          	END
        )
VIRTUAL

You can also add a virtual column in an existing table.

hr@O11R2>ALTER TABLE SCOTT.emp ADD (inc AS (sal*comm));

Since i didn’t define a datatype, the database itself determines the data type of the column based on the data type of the underlying expressions.

Another thing to remember that we can’t insert or update the value of Virtual column but it can be used in WHERE clause of an UPDATE and DELETE statement.

hr@O11R2>UPDATE v_col_tab
	 SET grade='C'
	 WHERE grade='D';

update v_col_tab
       *
ERROR at line 1:
ORA-54017: UPDATE operation disallowed on virtual columns

hr@O11R2>UPDATE v_col_tab
	 SET sid=113
	 WHERE grade='D';

1 row updated.

hr@O11R2>SELECT * FROM v_col_tab;
SID     STUDENT_NAME                            MARKS           GR
-----	---------------------------------------	------------	---
111     D Kumar	                                78              B
112     P Gupta                                 87              B
113     S Sharan                                54              D
115     K James                                 90              A

Creation of an index on a virtual column is equivalent to a function-based index.

hr@O11R2>CREATE INDEX ind_grade_v_col_tab ON v_col_tab (grade);

Index created.

hr@O11R2>SELECT index_type, column_expression
     	FROM user_indexes JOIN user_ind_expressions
     	USING(index_name)
     	WHERE index_name='IND_GRADE_V_COL_TAB';

INDEX_TYPE               COLUMN_EXPRESSION
---------------------    ------------------------------------------
FUNCTION-BASED NORMAL    CASE  WHEN "MARKS">=90 THEN 'A' WHEN
                         "MARKS">75 THEN 'B' WHEN "MARKS">55
                         THEN 'c' ELSE 'D' END

NOTES AND RESTRICTIONS ON VIRTUAL COLUMNS:

  • You can create virtual columns only on heap-organized tables. You can’t create virtual columns on an index-organized table, an external table, a cluster, an object, or a temporary table.
  • You can’t create a virtual column as a user-defined type, LOB, or RAW.
  • All columns in the column expression must belong to the same table.
  • The column expression must result in a scalar value.
  • The column expression in the GENERATED ALWAYS AS clause can’t refer to another virtual column.

IMPORTANT LINKS:

Virtual Column

Advertisements

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: