1. 为什么要使用虚拟列
(1)可以为虚拟列创建索引(oracle为其创建function index)
(2)可以搜集虚拟列的统计信息statistics,为CBO提供一定的采样分析。
(3)可以在where 后面使用虚拟列作为选择条件
(4)只在一处定义,不存储多余数据,查询是动态生成。
2. 语法
- HR@bear> create table inv(
- 2 inv_id number,
- 3 inv_count number,
- 4 inv_status generated always as
- 5 (case when inv_count <= 100 then 'GETTING LOW'
- 6 when inv_count > 100 then 'OKAY'
- 7 end)
- 8 );
其中 inv_status 为虚拟列
我们插入一条数据,然后再查询,可以看到虚拟列的值会根据定义动态生成。
- HR@bear> insert into inv (inv_id, inv_count) values (1, 100);
-
- 1 row created.
-
- HR@bear> select * from inv;
-
- INV_ID INV_COUNT INV_STATUS
- ---------- ---------- -----------
- 1 100 GETTING LOW
3.添加一个虚拟列
- alter table inv add inv_comm generated always as(inv_count * 0.1) virtual ;
4. 修改现有的一个虚拟列
- alter table inv modify inv_status generated always as(
- case when inv_count <= 50 then 'NEED MORE'
- when inv_count >50 and inv_count <=200 then 'GETTING LOW'
- when inv_count > 200 then 'OKAY'
- end);
5.虚拟列可以在where子句中使用
- SQL> update inv set inv_count=100 where inv_status='OKAY';
注意不能直接插入或修改虚拟列的值。
你可以定义虚拟列的数据类型,如果不指定,oracle会自动指定为定义中依赖的列的数据类型。
注意事项:
(1) 只有堆组织表(heap-organized table)才可以定义虚拟列
(2) 虚拟列不能引用其他的虚拟列
(3) 虚拟列只能引用自己表中的列, 不能引用其他表中的列。
(4) 虚拟列值只能是标量 scalar value (a single value, not a set of values)
Oracle 11G 在表中引入了虚拟列,虚拟列是一个表达式,在运行时计算,不存储在数据库中,不能更新虚拟列的值。
定义一个虚拟列的语法:
column_name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL]
1.虚拟列可以用在select,update,delete语句的where条件中,但是不能用于DML语句
2.可以基于虚拟列来做分区
3. 可以在虚拟列上建索引,oracle的函数索引就类似。
4. 可以在虚拟列上建约束
Oracle11g 增加了虚拟列的新特性, 具体说明如下:
1> 只能在堆组织表(普通表)上创建虚拟列,不能在索引组织表、外部表、临时表上创建虚拟列
2> 虚拟列不能是LOB或者RAW类型
3> 虚拟列的值并不是真实存在的,只有用到时,才根据表达式计算出虚拟列的值,磁盘上并不存放
4> 可把虚拟列当做分区关键字建立分区表,这是ORACLE 11g的另一新特性--虚拟列分区
5> 可在虚拟列上建立索引
6> 如果在已经创建的表中增加虚拟列时,若没有指定虚拟列的字段类型,ORACLE会根据 generated always as 后面的表达式计算的结果自动设置该字段的类型
7> 虚拟列的值由ORACLE根据表达式自动计算得出,不可以做UPDATE和INSERT操作, 可以对虚拟列做 DELETE 操作
8> 表达式中的所有列必须在同一张表
9> 表达式不能使用其他虚拟列
Oracle 11g新特性之--虚拟列(Virtual Column)
Oracle 11G虚拟列Virtual Column介绍
在老的 Oracle 版本,当我们需要使用表达式或者一些计算公式时,我们会创建数据库视图,如果我们需要在这个视图上使用索引,我们会创建基于函数的索引。
我们从Oracle官方文档中,找到下面对于虚拟列技术的描述。
“Tables can also include virtual columns. A virtual column is like any other table column, except that its value is derived by evaluating an expression. The expression can include columns from the same table, constants, SQL functions, and user-defined PL/SQL functions. You cannot explicitly write to a virtual column.”
Oracle 11G 在表中引入了虚拟列,虚拟列是一个表达式,在运行时计算,不存储在数据库中,不能更新虚拟列的值。
定义一个虚拟列的语法:
column_name [datatype] [GENERATED ALWAYS] AS [expression] [VIRTUAL]
1.虚拟列可以用在select,update,delete语句的where条件中,但是不能用于DML语句
2.可以基于虚拟列来做分区
3. 可以在虚拟列上建索引,oracle的函数索引就类似。
4. 可以在虚拟列上建约束
案例:
1、创建一个带虚拟列的表:
14:51:28 SCOTT@ test1 >CREATE TABLE EMP3
14:51:51 2 (
14:51:51 3 EMPNO NUMBER(6),
14:51:51 4 SAL NUMBER(8,2),
14:51:51 5 COMM NUMBER(8,2),
14:51:51 6 SAL_PACK GENERATED ALWAYS AS ( SAL + NVL(COMM,0) ) VIRTUAL
14:51:51 7 )
Table created.
2、查看虚拟列属性
14:56:10 SCOTT@ test1 >COL TABLE_NAME FOR A10
14:56:19 SCOTT@ test1 >COL COLUMN_NAME FOR A20
14:56:27 SCOTT@ test1 >COL DATA_TYPE FOR A20
14:56:34 SCOTT@ test1 >COL DATA_DEFAULT FOR A20
14:56:48 SCOTT@ test1 >R
1 select table_name,COLUMN_NAME,data_type,data_default,VIRTUAL_COLUMN from user_tab_cols
2* where table_name='EMP3'
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_DEFAULT VIR
---------- -------------------- -------------------- -------------------- ---
EMP3 SAL_PACK NUMBER "SAL"+NVL("COMM",0) YES
EMP3 COMM NUMBER NO
EMP3 SAL NUMBER NO
EMP3 EMPNO NUMBER NO
上述建的虚拟列 SAL_PACK 是由一个简单的表达式创建的,使用的关键字有 VIRTUAL(不过这个关键字是可选的),该字段的值是由 COMM 这个字段通过表达式计算而来的。
在Table上添加虚拟列:
15:44:12 SCOTT@ test1 >alter table emp3 add (sal_total as (sal*12+comm) virtual);
Table altered.
15:49:11 SCOTT@ test1 >desc emp3;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
EMPNO NUMBER(6)
SAL NUMBER(8,2)
COMM NUMBER(8,2)
SAL_PACK NOT NULL NUMBER
SAL_TOTAL NUMBER
15:49:16 SCOTT@ test1 >select * from emp3;
EMPNO SAL COMM SAL_PACK SAL_TOTAL
---------- ---------- ---------- ---------- ----------
10 1500 500 2000 18500
20 3000 500 3500 36500
30 4000 500 4500 48500
40 6000 500 6500 72500
15:51:00 SCOTT@ test1 >select table_name,COLUMN_NAME,data_type,data_default,VIRTUAL_COLUMN from user_tab_cols
15:51:27 2 where table_name='EMP3';
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_DEFAULT VIR
---------- -------------------- -------------------- -------------------- ---
EMP3 SAL_TOTAL NUMBER "SAL"*12+"COMM" YES
EMP3 SAL_PACK NUMBER "SAL"+NVL("COMM",0) YES
EMP3 COMM NUMBER NO
EMP3 SAL NUMBER NO
EMP3 EMPNO NUMBER NO
在虚拟列中使用函数:
15:51:37 SCOTT@ test1 >CREATE OR REPLACE FUNCTION sum_sal (in_num1 NUMBER, in_num2 NUMBER)
15:57:17 2 RETURN NUMBER DETERMINISTIC
15:57:17 3 AS
15:57:17 4 BEGIN
15:57:17 5 RETURN in_num1 + in_num2;
15:57:18 6 END;
15:57:19 7 /
Function created.
15:57:21 SCOTT@ test1 >alter table emp3 add ( sal_comm as (sum_sal(sal,comm)) virtual);
Table altered.
16:00:03 SCOTT@ test1 >desc emp3
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
EMPNO NUMBER(6)
SAL NUMBER(8,2)
COMM NUMBER(8,2)
SAL_PACK NOT NULL NUMBER
SAL_TOTAL NUMBER
SAL_COMM NUMBER
16:00:07 SCOTT@ test1 >select * from emp3;
EMPNO SAL COMM SAL_PACK SAL_TOTAL SAL_COMM
---------- ---------- ---------- ---------- ---------- ----------
10 1500 500 2000 18500 2000
20 3000 500 3500 36500 3500
30 4000 500 4500 48500 4500
40 6000 500 6500 72500 6500
虚拟列的值是不存储在磁盘的,它们是在查询时根据定义的表达式临时计算的。
3、对虚拟列的操作
Insert 操作:
我们不能往虚拟列中插入数据:
15:01:52 SCOTT@ test1 >insert into emp3 values (10,1500,500,2000);
insert into emp3 values (10,1500,500,2000)
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns
也不能隐式的添加数据到虚拟列:
15:02:16 SCOTT@ test1 >insert into emp3 values (10,1500,500);
insert into emp3 values (10,1500,500)
*
ERROR at line 1:
ORA-00947: not enough values
虚拟列的数据会自动计算生成
15:07:16 SCOTT@ test1 >insert into emp3(empno,sal,comm) values (10,1500,500);
1 row created.
15:07:29 SCOTT@ test1 >select * from emp3;
EMPNO SAL COMM SAL_PACK
---------- ---------- ---------- ----------
10 1500 500 2000
对虚拟列不能做update操作:
15:18:45 SCOTT@ test1 >update emp3 set sal_pack=3000;
update emp3 set sal_pack=3000
*
ERROR at line 1:
ORA-54017: UPDATE operation disallowed on virtual columns
在虚拟列上创建索引和约束:
15:19:07 SCOTT@ test1 >create index emp3_val_ind on emp3(sal_pack) tablespace indx;
Index created.
15:21:20 SCOTT@ test1 >select table_name,index_name,INDEX_TYPE from user_indexes
15:22:11 2 where table_name='EMP3';
TABLE_NAME INDEX_NAME INDEX_TYPE
---------- ------------------------------ ---------------------------
EMP3 EMP3_VAL_IND FUNCTION-BASED NORMAL
15:22:18 SCOTT@ test1 >drop index EMP3_VAL_IND;
Index dropped.
15:24:37 SCOTT@ test1 >alter table emp3 add constraint pk_emp3 primary key (sal_pack);
Table altered.
15:25:22 SCOTT@ test1 >select table_name,index_name,INDEX_TYPE from user_indexes
15:25:34 2 where table_name='EMP3';
TABLE_NAME INDEX_NAME INDEX_TYPE
---------- ------------------------------ ---------------------------
EMP3 PK_EMP3 FUNCTION-BASED NORMAL
在虚拟列上建立分区表:
15:41:43 SCOTT@ test1 >CREATE TABLE EMP3_part
15:41:46 2 (
15:41:46 3 EMPNO NUMBER(6),
15:41:46 4 SAL NUMBER(8,2),
15:41:46 5 COMM NUMBER(8,2),
15:41:46 6 SAL_PACK GENERATED ALWAYS AS ( SAL + NVL(COMM,0) ) VIRTUAL
15:41:46 7 )
15:41:46 8 PARTITION BY range (sal_pack)
15:41:46 9 (PARTITION sal_2000 VALUES LESS THAN (2000),
15:41:46 10 PARTITION sal_4000 VALUES LESS THAN (4000),
15:41:46 11 PARTITION sal_6000 VALUES LESS THAN (6000),
15:41:46 12 PARTITION sal_8000 VALUES LESS THAN (8000),
15:41:46 13 PARTITION sal_default VALUES LESS THAN (MAXVALUE));
Table created.
15:42:33 SCOTT@ test1 >insert into emp3_part(empno,sal,comm) select empno,sal,comm from emp3;
4 rows created.
15:43:33 SCOTT@ test1 >commit;
Commit complete.
15:43:36 SCOTT@ test1 >select * from emp3_part;
EMPNO SAL COMM SAL_PACK
---------- ---------- ---------- ----------
10 1500 500 2000
20 3000 500 3500
30 4000 500 4500
40 6000 500 6500
15:43:44 SCOTT@ test1 >select * from emp3_part partition(sal_2000);
no rows selected
15:44:01 SCOTT@ test1 >select * from emp3_part partition(sal_4000);
EMPNO SAL COMM SAL_PACK
---------- ---------- ---------- ----------
10 1500 500 2000
20 3000 500 3500
--通过以上对虚拟列的特性可以看出,Oracle采用虚拟列是占用了CPU计算时间,而节约了磁盘的存储空间。
从Oracle11g开始,提供了虚拟列(Virtual Column)功能。和传统的数据列差异在于,虚拟列在数据库中并不存在实际保存的数值,而是通过计算公式,进行计算获取列值。
我们从Oracle官方文档中,找到下面对于虚拟列技术的描述。
“Tables can also include virtual columns. A virtual column is like any other table column, except that its value is derived by evaluating an expression. The expression can include columns from the same table, constants, SQL functions, and user-defined PL/SQL functions. You cannot explicitly write to a virtual column.”
从上面的内容中,我们可以打出关于虚拟列的下面即使要点:
ü 对数据表,我们是可以添加虚拟列的;
ü 虚拟列的使用和一般列在使用上没有过多的区别,只是通过表达式计算出的值;
ü 在虚拟列的表达式中,可以包括同表的其他列、常量、SQL函数,甚至可以包括一些用户自定义的PL/SQL函数;
ü 同一般列的区别,在进行insert操作的时候,我们不能直接进行该列的赋值操作;
下面,我们通过一系列的实验来验证虚拟列特性。
SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
SQL> create table t (tes varchar2(10));
Table created
//定义添加一个虚拟列;
SQL> alter table t add (u_tes varchar2(10) as (upper(tes)));
Table altered
SQL> desc t;
Name Type Nullable Default Comments
----- ------------ -------- ------------ --------
TES VARCHAR2(10) Y
U_TES VARCHAR2(10) Y UPPER("TES")
虚拟列效果
我们通过添加数据,来探究虚拟列的使用。
SQL> insert into t (tes) values ('kew');
1 row inserted
SQL> insert into t (tes) values ('kEfsET3');
1 row inserted
SQL> commit;
Commit complete
//检索全部行
SQL> select * from t;
TES U_TES
---------- ----------
kew KEW
kEfsET3 KEFSET3
当设置上虚拟列之后,虚拟列的取值会按照自动设置的公式计算而成。首先,我们注意虚拟列的定义方式,格式为:
SQL> alter table t add (u_tes varchar2(10) as (upper(tes)));
Table altered
此外,对虚拟列的类型,也可以不进行显示赋值。
//可以不指定类型信息
SQL> alter table t add (u_tes2 as (length(tes)));
Table altered
SQL> desc t;
Name Type Nullable Default Comments
------ ------------ -------- ------------- --------
TES VARCHAR2(10) Y
U_TES VARCHAR2(10) Y UPPER("TES")
U_TES2 NUMBER Y LENGTH("TES")
Oracle会根据生成列值的表达式进行计算,同时估算出可能的类型。
显示赋值不允许
虚拟列的赋值是Oracle自动依据表达式进行的。如果我们强制赋值,会提示错误信息。
SQL> update t set u_tes='k' where u_tes2=3;
update t set u_tes='k' where u_tes2=3
ORA-54017: 不允许对虚拟列执行 UPDATE 操作
SQL> insert into t (u_tes) values ('LI');
insert into t (u_tes) values ('LI')
ORA-54013: 不允许对虚拟列执行 INSERT 操作
虚拟列本质分析
我们检查数据字典的相关信息,分析列情况。
SQL> select object_id from dba_objects where wner='SYS' and object_name='T';
OBJECT_ID
----------
74889
SQL> select col#, segcol#,name, type#, default$ from col$ where obj#=74889;
COL# SEGCOL# NAME TYPE# DEFAULT$
---------- ---------- ---------------------
1 1 TES 1
2 0 U_TES 1 UPPER("TES")
3 0 U_TES2 2 LENGTH("TES")
说明两个列在数据字典底层存在。
索引特性
对虚拟列我们可以加入索引和约束。
//加入控制约束
SQL> alter table T modify U_TES not null;
Table altered
SQL> alter table T modify U_TES2 not null;
Table altered
SQL> desc t;
Name Type Nullable Default Comments
------ ------------ -------- ------------- --------
TES VARCHAR2(10) Y
U_TES VARCHAR2(10) UPPER("TES")
U_TES2 NUMBER LENGTH("TES")
SQL> insert into t (tes) values (null);
insert into t (tes) values (null)
ORA-01400: 无法将 NULL 插入 ("SYS"."T"."U_TES2")
索引情况呢?
//重新构建实验环境
SQL> create table t as select object_id, owner, object_name from dba_objects;
Table created
SQL> alter table t add (name_length as (length(object_name)));
Table altered
SQL> create index idx_t_leng on t(name_length);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
检查索引本质内容:
SQL> select index_type from dba_indexes where index_name='IDX_T_LENG';
INDEX_TYPE
---------------------------
FUNCTION-BASED NORMAL
类型为基于函数的正常索引。
总结:
虚拟列是一种用时间换空间的技术,通过消耗计算时间来换回保存的空间。