技术头条 - 一个快速在微博传播文章的方式     搜索本站
您现在的位置首页 --> Oracle --> ORACLE 11g新特性-虚拟列

ORACLE 11g新特性-虚拟列

浏览:1270次  出处信息

在上周六的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.
QQ技术交流群:445447336,欢迎加入!
扫一扫订阅我的微信号:IT技术博客大学习
© 2009 - 2024 by blogread.cn 微博:@IT技术博客大学习

京ICP备15002552号-1