ORACLE 11g新特性-虚拟列
在上周六的ACOUG活动中，张乐奕（kamus）老师分享了一个关于ORACLE 11g新特性的主题，本人觉得在10g这个过渡版本已成过去（官网已不提供10g版本的介质下载），12c马上发布，11g已经成熟而有些人还没有开始使用11g的年头，了解11g的新特性还是很有必要的，本文主要和大家一起学习下ORACLE 11g新特性—虚拟列（Virtual Columns）。
虚拟列可以在建表时创建，如果您查看ORACLE 11g的官方文档，您会在SQL部分看到有关虚拟列语法的描述：
建表时指定虚拟列，示例代码如下：
SQL> create table dbdream(id number,identifier varchar2(255), keyword varchar2(20),fond_code varchar2(20) generated always as (substr(identifier,1,2))); Table created.
也可以在已有的表中增加虚拟列：
SQL> drop table dbdream purge; Table dropped. SQL> create table dbdream(id number,identifier varchar2(255),keyword varchar2(20)); Table created. SQL> alter table dbdream add fond_code varchar2(20) generated always as (substr(identifier,1,2)); Table altered.
如果在已有表中增加虚拟列时，没有指定虚拟列的字段类型，ORACLE会根据generated always as后面的表达式计算的结果自动设置该字段的字段类型，示例代码如下：
SQL> alter table dbdream drop column FOND_CODE; Table altered. SQL> alter table dbdream add fond_code generated always as (substr(identifier,1,2)); Table altered. SQL> desc dbdream Name Null? Type ----------------------------------------- -------- -------------- ID NUMBER IDENTIFIER VARCHAR2(255) KEYWORD VARCHAR2(20) FOND_CODE VARCHAR2(8)
虚拟列的值由ORACLE根据表达式自动计算得出，不可以手动去修改和指定虚拟列的值：
SQL> insert into dbdream values(1,'02-01-03-03296-001','奏折','02');
insert into dbdream values(1,'02-01-03-03296-001','奏折','02')
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns
虚拟列的值并不是真实存在的，只有利用到虚拟列，ORACLE才会根据表达式计算出虚拟列的值，磁盘上并不存放虚拟列的值。
SQL> insert into dbdream(id,identifier,keyword) values (1,'02-01-03-03296-001','奏折'); 1 row created. SQL> insert into dbdream(id,identifier,keyword) values (2,'03-01-03-0001-001','手谕'); 1 row created. SQL> insert into dbdream(id,identifier,keyword) values (3,'04-01-02-0075-010','奏折'); 1 row created. SQL> insert into dbdream(id,identifier,keyword) values (4,'05-01-03-0001-001','遗诏'); 1 row created. SQL> commit; Commit complete.
以上虽然没有插入虚拟列的值（也插入不了），但是ORACLE会根据虚拟列的表达式自动计算出虚拟列的值：
SQL> select * from dbdream; ID IDENTIFIER KEYWORD FOND_COD -- ----------------- ------- -------- 1 02-01-03-0326-001 奏折 02 2 03-01-03-0001-001 手谕 03 3 04-01-02-0075-010 奏折 04 4 05-01-03-0001-001 遗诏 05
可以把虚拟列当做分区关键字建立分区表：
SQL> create table stream(ID NUMBER,IDENTIFIER VARCHAR2(255),
2 KEYWORD VARCHAR2(20),FOND_CODE VARCHAR2(8)
3 generated always as (substr(identifier,1,2)))
4 partition by list (FOND_CODE)
5 (partition par01 values('02'),
6 partition par02 values('03'),
7 partition par03 values('04'),
8 partition par04 values('05'),
9 partition par05 values(default));
Table created.
将dbdream表里的数据插入到stream表：
SQL> insert into stream(id,identifier,keyword) select id,identifier,keyword from dbdream; 4 rows created. SQL> commit; Commit complete.
查询数据验证分区建立是否正确：
SQL> select * from dbdream; ID IDENTIFIER KEYWORD FOND_COD -- ----------------- ------- -------- 1 02-01-03-0326-001 奏折 02 2 03-01-03-0001-001 手谕 03 3 04-01-02-0075-010 奏折 04 4 05-01-03-0001-001 遗诏 05 SQL> select * from stream partition(par01); ID IDENTIFIER KEYWORD FOND_COD -- ----------------- ------- -------- 1 02-01-03-0326-001 奏折 02 SQL> select * from stream partition(par02); ID IDENTIFIER KEYWORD FOND_COD -- ----------------- ------- -------- 2 03-01-03-0001-001 手谕 03 SQL> select * from stream partition(par03); ID IDENTIFIER KEYWORD FOND_COD -- ----------------- ------- -------- 3 04-01-02-0075-010 奏折 04 SQL> select * from stream partition(par04); ID IDENTIFIER KEYWORD FOND_COD -- ----------------- ------- -------- 4 05-01-03-0001-001 遗诏 05 SQL> select * from stream partition(par05); no rows selected
可见在虚拟列做分区表的分区键是可以的，这也是ORACLE 11g的新特性-虚拟列分区，下面是虚拟列的特点和限制（可能不全）：
1.虚拟列的值由ORACLE通过表达式计算得出，并不存放在表中。
2.不可以对虚拟列做UPDATE和INSERT操作。
3.可以在虚拟列上建立索引，可以建立虚拟列分区表。
SQL> create index ind_fond on dbdream(fond_code); Index created.
4.只能在堆组织表（普通表）上创建虚拟列，不能在索引组织表、外部表、临时表上创建虚拟列。
5.虚拟列字段不能是LOB或RAW类型。
6.表达式中的所有列必须在同一张表。
7.表达式不能使用其他虚拟列。
有些资料上说不能对虚拟列做DELETE操作，但实验证明是可以的：
SQL> delete from dbdream where FOND_CODE='05'; 1 row deleted. SQL> rollback; Rollback complete.
ORACLE 11g新特性-虚拟列：等您坐沙发呢！
发表评论
