Begin oracle 11g, it can declare a new column type which is virtual column. It does not consume data storage space, only store in the table definition. It would be used the defined formula of virtual column as the selective result, when it is selected by app.
Example:
winkey@SQL>create table test1 (name varchar2(20), english number(5), math number(5), chinese number(5),
2 sum generated always as (english+math+chinese) virtual);
Table created.
winkey@SQL>desc test1
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(20)
ENGLISH NUMBER(5)
MATH NUMBER(5)
CHINESE NUMBER(5)
SUM NUMBER
winkey@SQL>set line 150
winkey@SQL>COL DATA_TYPE FOR A30
winkey@SQL>COL DATA_TYPE FOR A30
winkey@SQL>select column_name, data_type, data_default from user_tab_columns where table_name='TEST1';
COLUMN_NAME DATA_TYPE DATA_DEFAULT
------------------------------ ------------------------------ --------------------------------------------------------------------------------
NAME VARCHAR2
ENGLISH NUMBER
MATH NUMBER
CHINESE NUMBER
SUM NUMBER "ENGLISH"+"MATH"+"CHINESE" /*virtual column*/
winkey@SQL>insert into test1 values('winkey', 100, 100, 100, 300);
insert into test1 values('winkey', 100, 100, 100, 300) /*Virtual column can not be insert contents */
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns
winkey@SQL>insert into test1 values('winkey', 100, 100, 100); /* This method can not be insert contents */
insert into test1 values('winkey', 100, 100, 100)
*
ERROR at line 1:
ORA-00947: not enough values
winkey@SQL>insert into test1(name, english, math, chinese) values('winkey', 100, 100, 100); /* That is right */
1 row created.
winkey@SQL>commit;
Commit complete.
winkey@SQL>select * from test1;
NAME ENGLISH MATH CHINESE SUM
-------------------- ---------- ---------- ---------- ----------
winkey 100 100 100 300
winkey@SQL>
For virtual column, it can be managed as general column except for inserting. Such as creating index, primary key on the virtual column and so on.
winkey@SQL>select * from test1 where sum = 300;
NAME ENGLISH MATH CHINESE SUM
-------------------- ---------- ---------- ---------- ----------
winkey 100 100 100 300
Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 64 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST1 | 1 | 64 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SUM"=300)
winkey@SQL>create index idx_sum_t1 on test1(sum);
Index created.
winkey@SQL>select * from test1 where sum = 300;
NAME ENGLISH MATH CHINESE SUM
-------------------- ---------- ---------- ---------- ----------
winkey 100 100 100 300
Execution Plan
----------------------------------------------------------
Plan hash value: 3856151525
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 64 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 64 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_SUM_T1 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SUM"=300)