11g虚拟列

========================

11g中建表可以使用虚拟列
虚拟列为其他字段运算的结果
可以直接在建表的时候得到
对于虚拟列，可以对其进行查询，但不能更新
如果虚拟列依赖的字段有值则自动计算出虚拟列的值
在表中单独对虚拟列进行插入和更新是不允许的
ctas形式建表不能引用虚拟列的定义
另外在创建分区的时候也可以使用虚拟列来进行分区

----------------

--建表
SQL> create table a(
   a number,
   b number,
   c as (a*b)
  );
表已创建。

--字段c没有定义类型，定义中指定c为a字段和b字段的乘积

--查看定义

SQL> desc a
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
A                                                  NUMBER
B                                                  NUMBER
C                                                  NUMBER

--对a、b字段进行插入操作，查询时会自动返回c，也就是a和b的乘积

SQL> insert into a (a,b) select 1,2 from dual;
已创建 1 行。

SQL> commit;
提交完成。

SQL> select * from a;
A B C
---------- ---------- ----------
1 2 2

--不能对虚拟列进行插入和更新操作
SQL> insert into a (c) values (1);
insert into a (c) values (1)
*
第 1 行出现错误:
ORA-54013: 不允许对虚拟列执行 INSERT 操作

SQL> update a set c=3;
update a set c=3
*
第 1 行出现错误:
ORA-54017: 不允许对虚拟列执行 UPDATE 操作

--在插入数据的时候因为有虚拟列的干扰，所以插入字段不能省略
SQL> insert into a select 1,2 from dual;
insert into a select 1,2 from dual
*
第 1 行出现错误:
ORA-00947: 没有足够的值

--参照带有虚拟列的表创建的新表中不再存在虚拟列，转换成了普通列
SQL> create table b as select * from a;

表已创建。

SQL> desc b
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
A                                                  NUMBER
B                                                  NUMBER
C                                                  NUMBER

SQL> insert into b (c) select 1 from dual;
已创建 1 行。

SQL> commit;
提交完成。

--可以对虚拟列进行分区操作，但数据不会同样物理的存储

SQL> create table c (a number,b number,c as (a*b))
2 partition by range (c)
3 (partition p1 values less than (10000),
4 partition p2 values less than (maxvalue));

表已创建。

SQL> insert into c(a,b) select 1,2 from dual;
已创建 1 行。

SQL> insert into c(a,b) select 100,200 from dual;
已创建 1 行。

SQL> commit;
提交完成。

SQL> select * from c partition (p2);

A B C
---------- ---------- ----------
100 200 20000

SQL> select * from c partition (p1);

A B C
---------- ---------- ----------
1 2 2

--虽然是虚拟列，但同样是按照分区来分布数据

--插入数据
SQL> begin for i in 1..10000 loop
2 insert into c (a,b) values(i,i+1);
3 end loop;
4 commit;
5 end;
6 /

PL/SQL 过程已成功完成。

--查询同样走分区
SQL> select * from c where c>10000;

已选择9902行。

执行计划
----------------------------------------------------------
Plan hash value: 3678094855

--------------------------------------------------------------------------------
| Id | Operation              | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |   500 | 19500 |     7   (0)| 00:00:01 |
|   1 | PARTITION RANGE SINGLE|      |   500 | 19500 |     7   (0)| 00:00:01 |
|* 2 |   TABLE ACCESS FULL    | C    |   500 | 19500 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("C">10000)

Note
-----
- dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        682 consistent gets
          0 physical reads
          0 redo size
     233439 bytes sent via SQL*Net to client
       7676 bytes received via SQL*Net from client
        662 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
       9902 rows processed

ITPub博客

11g虚拟列

最新文章