ITPub博客

首页 > 数据库 > Oracle > 【oracle】11g新特性虚拟列

【oracle】11g新特性虚拟列

Oracle 作者:junhong118 时间:2013-12-03 17:41:30 0 删除 编辑

  之前做数据仓库的时候没有用到虚拟列,最近在看其他人设计的数据模型的时候,发现用了很多虚拟列,12g都出了,我还在学习11g特性,呵呵,要补的功课还是很多啊。

  虚拟列(virtual column):并没有存储在数据文件中,而是通过列数据的生成放到了数据字典中,通过真实列的数值计算而来,位置可以随意摆放,可以引用多个实际列的值,但是不能引用其他的虚拟列(但可以使用其他宾变通的方式),也不能只包含虚拟列。

  好处:减少数据的存储,简化了对列进行处理,而且可以对虚拟列进行分区,可以使用自定义函数

  缺点

1、不能使用create table as select ... 来创建包含虚拟列的表,解决办法是先create table as select...创建不含虚拟列的表,在altertable创建含虚拟列的表。

2、虚拟化的列的值被实体化后,会发生虚拟列表达式的变化,会造成实体化和虚拟列的值不同。

  自定义函数注意事项:

1、函数要声明

2、函数中要引用表中的列,不能只包含虚拟列

3、不能替换被定义为索引的虚拟列的计算公式,但是可以修改计算公式用到的自定义函数。但是修改了函数以后,索引要重建,不然查询会出现问题。

 

练习:

1、创建虚拟表有两个虚拟字段,但是虚拟列不能引用虚拟列
create table t_vertual_column(c_v1 number,c_v2 number,v1 as (c_v1+c_v2),v2 as ( to_date(c_v2,'yyyy-mm-dd')))
create table t_vertual_column(c_v1 number,c_v2 number,v1 as (c_v1+c_v2),v2 as ( to_date(c_v2,'yyyy-mm-dd')),v3 as (v1+1)) --ORA-54012: 在列表达式中引用了虚拟列

2、完整的语句如下:
CREATE TABLE yu_test1 ( v_cl_1 VARCHAR2(30),  v_cl_2 CHAR(50) GENERATED ALWAYS AS (LOWER(v_cl_1)) VIRTUAL );
-- CHAR(50):定义了虚拟列的类型,可以不写,在虚拟列as后边的最终结果可以确定虚拟列的最终结果
-- GENERATED ALWAYS 和VIRTUAL:用于描述虚拟列的特性,可以不写


3、虚拟列可以使用oracle自带的函数或者用户定义的函数,用户自定义的函数必须要求声明函数的确定性(deterministic),同时函数必须有实际列当做传入参数。
create or replace function f_vertual_column( v_c1 number ) return number as
begin
  return 1;
  end;
drop table t_vertual_column purge ;
create table t_vertual_column(c_v1 number,c_v2 number,v1 as (f_vertual_column(c_v1)),v2 as ( to_date(c_v2,'yyyy-mm-dd'))) -- ORA-30553: 函数不能确定

create or replace function f_vertual_column( v_c1 number ) return number deterministic as
begin
  return 1;
  end;


create table t_vertual_column(c_v1 number,c_v2 number,v1 as (f_vertual_column(c_v1)),v2 as ( to_date(c_v2,'yyyy-mm-dd'))) -- ok


4、创建表的时候determinister是必须的,但是建立表之后却可以将函数替换为非确定性函数:
drop function f_vertual_column;--带有determiniter的
create or replace function f_vertual_column( v_c1 number ) return number  as
begin
  return 1;
  end;  --不带有determiniter的
insert into t_vertual_column(c_v1,c_v2) values (2,20130107);
select * from t_vertual_column;

 

5、向表中插入数据,直插入实际字段就可以,插入语句必须写column列表:
insert into t_vertual_column(c_v1,c_v2) values (2,20130107) --正确
insert into t_vertual_column(c_v1,c_v2,v1,v2) values (2,20130107,'','' ) --ORA-54013: 不允许对虚拟列执行 INSERT 操作
insert into t_vertual_column(c_v1,c_v2,v1,v2) values (2,20130107 ) --ORA-00947: 没有足够的值
4、可以直接删除表中所有数据
truncate table t_vertual_column
delete from t_vertual_column where v1=20130109


5、可以修改实际列的值,但是不能修改虚拟列的值
update t_vertual_column set c_v1=3; -- ok
update t_vertual_column set v1=5 ; -- ORA-54017: 不允许对虚拟列执行 UPDATE 操作


7、在虚拟列上创建索引,就不允许修改虚拟列的索引表达式,但是可以修改当前函数,也会影响值
create index i_c_v1 on t_vertual_column(v1); --在虚拟列上创建函数
alter table t_vertual_column modify v1 as( decode(c_v1,3,0) ) -- ORA-54022: 无法更改虚拟列表达式, 因为在列上定义了索引
修改函数的返回值,查看结果:
之前function的返回值是:1,插入一条数据
insert into t_vertual_column(c_v1,c_v2) values (3,20130107)

SQL> select * from t_vertual_column;

 
      C_V1       C_V2         V1 V2
---------- ---------- ---------- -----------
          20130107          1 2013/1/7


现在修改函数的返回值为4,插入一条数据:
insert into t_vertual_column(c_v1,c_v2) values (2,20130107)

SQL> select * from t_vertual_column;
 
      C_V1       C_V2         V1 V2
---------- ---------- ---------- -----------
          20130107          4 2013/1/7
          20130107          4 2013/1/7
查询表中的数据
select * from t_vertual_column -- 返回所有,v1字段都=4

SQL> select * from t_vertual_column;
 
      C_V1       C_V2         V1 V2
---------- ---------- ---------- -----------
          20130107          4 2013/1/7
          20130107          4 2013/1/7
select * from t_vertual_column where v1=1 --返回修改函数之前的一条数据v1=1,实际上并无等于1的数据了

SQL> select * from t_vertual_column where v1=1 ;
 
      C_V1       C_V2         V1 V2
---------- ---------- ---------- -----------
          20130107          1 2013/1/7
select * from t_vertual_column where v1=4 -- 返回修改函数之后的一条数据v1=4

SQL> select * from t_vertual_column where v1=4 ;
 
      C_V1       C_V2         V1 V2
---------- ---------- ---------- -----------
          20130107          4 2013/1/7
解决办法:
删掉当前的索引,重新建:
drop index i_c_v1;
create index i_c_v1 on t_vertual_column(v1);
select * from t_vertual_column ;-- 返回所有,所有v1都=4

SQL> select * from t_vertual_column ;
 
      C_V1       C_V2         V1 V2
---------- ---------- ---------- -----------
          20130107          4 2013/1/7
          20130107          4 2013/1/7
select * from t_vertual_column where v1=1; --返回0条数据

 
SQL> select * from t_vertual_column where v1=1;
 
      C_V1       C_V2         V1 V2
---------- ---------- ---------- -----------
select * from t_vertual_column where v1=4 ;-- 返回4条数据

 SQL> select * from t_vertual_column where v1=4 ;
 
      C_V1       C_V2         V1 V2
---------- ---------- ---------- -----------
          20130107          4 2013/1/7

 

<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14056374/viewspace-1116116/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2010-04-05

最新文章