本文深入探讨了 Oracle 11G 中新增的虚拟列分区表特性，包括创建方法、虚拟列限制、在分区表中的应用、虚拟列索引等。通过示例展示了如何在表中添加虚拟列并利用其进行分区，以及在虚拟列上进行插入、更新、删除等操作。同时，介绍了虚拟列的类型自动设置、表达式约束以及区间分区的限制。
11G新特性虚拟列分区表
Oracle11g 增加了虚拟列的新特性， 具体说明如下：
1> 只能在堆组织表（普通表）上创建虚拟列，不能在索引组织表、外部表、临时表上创建虚拟列
2> 虚拟列不能是LOB或者RAW类型
3> 虚拟列的值并不是真实存在的，只有用到时，才根据表达式计算出虚拟列的值，磁盘上并不存放
4> 可把虚拟列当做分区关键字建立分区表，这是ORACLE 11g的另一新特性--虚拟列分区
5> 可在虚拟列上建立索引
6> 如果在已经创建的表中增加虚拟列时，若没有指定虚拟列的字段类型，ORACLE会根据 generated always as 后面的表达式计算的结果自动设置该字段的类型
7> 虚拟列的值由ORACLE根据表达式自动计算得出，不可以做UPDATE和INSERT操作， 可以对虚拟列做 DELETE 操作
8> 表达式中的所有列必须在同一张表
9> 表达式不能使用其他虚拟列
区间分区仅限于一个单一的分区键必须是一个数值或日期范围。
在表被创建时，必须要初始化一个分区
区间分区不支持索引组织表
不能在区间分区表上创建domain索引
区间分区能用于按逐渐自动增长的机制分区，但是不能设置分区级别
不能再去见分区表上设置maxvalue
partition 列不能为空
《《《
create table vtest (
day1 date,
day2 char(8),
fqkey date generated always as (to_date(day2,'YYYYMMDD')))
partition by range(fqkey)
interval (numtodsinterval(1,'day'))
(partition p20150115 values less than (to_date('20150115','YYYYMMDD')));
SQL> DROP TABLE VTEST;
Table dropped.
SQL> create table vtest (
day1 date,
day2 char(8),
fqkey date generated always as (to_date(day2,'YYYYMMDD'))
)
partition by range(fqkey)
interval (numtodsinterval(1,'day'))
(
partition p20150115 values less than (to_date('20150115','YYYYMMDD'))
); 2 3 4 5 6 7 8 9 10
Table created.
SQL> insert into vtest(day1,day2) values(sysdate-31,'20150101');
insert into vtest(day1,day2) values(sysdate-30,'20150102');
insert into vtest(day1,day2) values(sysdate-29,'20150103');
insert into vtest(day1,day2) values(sysdate-28,'20150104');
insert into vtest(day1,day2) values(sysdate-27,'20150105');
insert into vtest(day1,day2) values(sysdate-26,'20150106');
insert into vtest(day1,day2) values(sysdate-25,'20150107');
insert into vtest(day1,day2) values(sysdate-24,'20150108');
insert into vtest(day1,day2) values(sysdate-23,'20150109');
insert into vtest(day1,day2) values(sysdate-22,'20150110');
insert into vtest(day1,day2) values(sysdate-21,'20150121');
insert into vtest(day1,day2) values(sysdate-19,'20150111');
insert into vtest(day1,day2) values(sysdate-18,'20150131');
insert into vtest(day1,day2) values(sysdate-17,'20150112');
insert into vtest(day1,day2) values(sysdate-16,'20150113');
insert into vtest(day1,day2) values(sysdate-15,'20150114');
insert into vtest(day1,day2) values(sysdate-14,'20150115');
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL> COMMIT;
Commit complete.
SQL> select segment_name,owner,partition_name,segment_type from dba_segments where segment_name='VTEST';
SEGMENT_NAME OWNER PARTITION_NAME SEGMENT_TYPE
--------------- ---------- ------------------------------ ------------------
VTEST YZY P20150115 TABLE PARTITION
VTEST YZY SYS_P41 TABLE PARTITION
VTEST YZY SYS_P42 TABLE PARTITION
VTEST YZY SYS_P43 TABLE PARTITION
SQL> SELECT * FROM VTEST PARTITION(SYS_P41);
DAY1 DAY2 FQKEY
----------------- -------- -----------------
20141017 19:33:46 20150121 20150121 00:00:00
SQL> insert into vtest(day1,day2) values(sysdate-31,'20150201');
insert into vtest(day1,day2) values(sysdate-30,'20150202');
insert into vtest(day1,day2) values(sysdate-29,'20150203');
insert into vtest(day1,day2) values(sysdate-28,'20150204');
insert into vtest(day1,day2) values(sysdate-27,'20150205');
insert into vtest(day1,day2) values(sysdate-26,'20150206');
insert into vtest(day1,day2) values(sysdate-25,'20150207');
insert into vtest(day1,day2) values(sysdate-24,'20150208');
insert into vtest(day1,day2) values(sysdate-23,'20150209');
insert into vtest(day1,day2) values(sysdate-22,'20150210');
insert into vtest(day1,day2) values(sysdate-21,'20150221');
insert into vtest(day1,day2) values(sysdate-19,'20150211');
insert into vtest(day1,day2) values(sysdate-18,'20150231');
insert into vtest(day1,day2) values(sysdate-17,'20150212');
insert into vtest(day1,day2) values(sysdate-16,'20150213');
insert into vtest(day1,day2) values(sysdate-15,'20150214');
insert into vtest(day1,day2) values(sysdate-14,'20150215');
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL> COMMIT;
Commit complete.
SQL> select segment_name,owner,partition_name,segment_type from dba_segments where segment_name ='VTEST';
SEGMENT_NAME OWNER PARTITION_NAME SEGMENT_TYPE
--------------- ---------- ------------------------------ ------------------
VTEST SYS P20150115 TABLE PARTITION
VTEST YZY P20150115 TABLE PARTITION
VTEST YZY SYS_P41 TABLE PARTITION
VTEST YZY SYS_P42 TABLE PARTITION
VTEST YZY SYS_P43 TABLE PARTITION
VTEST YZY SYS_P44 TABLE PARTITION
VTEST YZY SYS_P45 TABLE PARTITION
VTEST YZY SYS_P46 TABLE PARTITION
VTEST YZY SYS_P47 TABLE PARTITION
VTEST YZY SYS_P48 TABLE PARTITION
VTEST YZY SYS_P49 TABLE PARTITION
VTEST YZY SYS_P50 TABLE PARTITION
VTEST YZY SYS_P51 TABLE PARTITION
VTEST YZY SYS_P52 TABLE PARTITION
VTEST YZY SYS_P53 TABLE PARTITION
VTEST YZY SYS_P54 TABLE PARTITION
VTEST YZY SYS_P55 TABLE PARTITION
VTEST YZY SYS_P56 TABLE PARTITION
VTEST YZY SYS_P57 TABLE PARTITION
VTEST YZY SYS_P58 TABLE PARTITION
VTEST YZY SYS_P59 TABLE PARTITION
21 rows selected.
SQL> SELECT * FROM VTEST PARTITION(SYS_P50);
DAY1 DAY2 FQKEY
----------------- -------- -----------------
20141013 19:35:21 20150207 20150207 00:00:00
SQL> update vtest partition(SYS_P50) set DAY2='20160101' where DAY1='20141013 19:35:21';
update vtest partition(SYS_P50) set DAY2='20160101' where DAY1='20141013 19:35:21'
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change
SQL> select table_name,owner,row_movement from dba_tables where table_name = 'VTEST';
TABLE_NAME OWNER ROW_MOVE
------------------------------ ---------- --------
VTEST YZY DISABLED
VTEST SYS DISABLED
SQL> ALTER TABLE YZY.VTEST ENABLE ROW MOVEMENT;
Table altered.
SQL> update vtest partition(SYS_P50) set DAY2='20160101' where DAY1='20141013 19:35:21';
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> select table_name,owner,row_movement from dba_tables where table_name = 'VTEST';
TABLE_NAME OWNER ROW_MOVE
------------------------------ ---------- --------
VTEST YZY ENABLED
VTEST SYS DISABLED
SQL> ALTER TABLE YZY.VTEST DISABLE ROW MOVEMENT;
Table altered.
SQL> select table_name,owner,row_movement from dba_tables where table_name = 'VTEST';
TABLE_NAME OWNER ROW_MOVE
------------------------------ ---------- --------
VTEST YZY DISABLED
VTEST SYS DISABLED
SQL> SELECT SEGMENT_NAME,SEGMENT_TYPE,OWNER,PARTITION_NAME FROM DBA_SEGMENTS WHERE SEGMENT_NAME ='VTEST';
SEGMENT_NAME SEGMENT_TYPE OWNER PARTITION_NAME
--------------- ------------------ ---------- ------------------------------
VTEST TABLE PARTITION SYS P20150115
VTEST TABLE PARTITION YZY P20150115
VTEST TABLE PARTITION YZY SYS_P41
VTEST TABLE PARTITION YZY SYS_P42
VTEST TABLE PARTITION YZY SYS_P43
VTEST TABLE PARTITION YZY SYS_P44
VTEST TABLE PARTITION YZY SYS_P45
VTEST TABLE PARTITION YZY SYS_P46
VTEST TABLE PARTITION YZY SYS_P47
VTEST TABLE PARTITION YZY SYS_P48
VTEST TABLE PARTITION YZY SYS_P49
VTEST TABLE PARTITION YZY SYS_P50
VTEST TABLE PARTITION YZY SYS_P51
VTEST TABLE PARTITION YZY SYS_P52
VTEST TABLE PARTITION YZY SYS_P53
VTEST TABLE PARTITION YZY SYS_P54
VTEST TABLE PARTITION YZY SYS_P55
VTEST TABLE PARTITION YZY SYS_P56
VTEST TABLE PARTITION YZY SYS_P57
VTEST TABLE PARTITION YZY SYS_P58
VTEST TABLE PARTITION YZY SYS_P59
VTEST TABLE PARTITION YZY SYS_P61
22 rows selected.
SQL> SELECT * FROM vtest partition(SYS_P50);
no rows selected
SQL> SELECT * FROM vtest partition(SYS_P61);
DAY1 DAY2 FQKEY
----------------- -------- -----------------
20141013 19:35:21 20160101 20160101 00:00:00
SQL> DELETE FROM vtest partition(SYS_P61);
1 row deleted.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM vtest partition(SYS_P61);
no rows selected
SQL> SELECT SEGMENT_NAME,SEGMENT_TYPE,OWNER,PARTITION_NAME FROM DBA_SEGMENTS WHERE SEGMENT_NAME ='VTEST';
SEGMENT_NAME SEGMENT_TYPE OWNER PARTITION_NAME
--------------- ------------------ ---------- ------------------------------
VTEST TABLE PARTITION SYS P20150115
VTEST TABLE PARTITION YZY P20150115
VTEST TABLE PARTITION YZY SYS_P41
VTEST TABLE PARTITION YZY SYS_P42
VTEST TABLE PARTITION YZY SYS_P43
VTEST TABLE PARTITION YZY SYS_P44
VTEST TABLE PARTITION YZY SYS_P45
VTEST TABLE PARTITION YZY SYS_P46
VTEST TABLE PARTITION YZY SYS_P47
VTEST TABLE PARTITION YZY SYS_P48
VTEST TABLE PARTITION YZY SYS_P49
VTEST TABLE PARTITION YZY SYS_P50
VTEST TABLE PARTITION YZY SYS_P51
VTEST TABLE PARTITION YZY SYS_P52
VTEST TABLE PARTITION YZY SYS_P53
VTEST TABLE PARTITION YZY SYS_P54
VTEST TABLE PARTITION YZY SYS_P55
VTEST TABLE PARTITION YZY SYS_P56
VTEST TABLE PARTITION YZY SYS_P57
VTEST TABLE PARTITION YZY SYS_P58
VTEST TABLE PARTITION YZY SYS_P59
VTEST TABLE PARTITION YZY SYS_P61
22 rows selected.
SQL> SELECT * FROM VTEST;
DAY1 DAY2 FQKEY
----------------- -------- -----------------
20141007 19:33:45 20150101 20150101 00:00:00
20141008 19:33:46 20150102 20150102 00:00:00
20141009 19:33:46 20150103 20150103 00:00:00
20141010 19:33:46 20150104 20150104 00:00:00
20141011 19:33:46 20150105 20150105 00:00:00
20141012 19:33:46 20150106 20150106 00:00:00
20141013 19:33:46 20150107 20150107 00:00:00
20141014 19:33:46 20150108 20150108 00:00:00
20141015 19:33:46 20150109 20150109 00:00:00
20141016 19:33:46 20150110 20150110 00:00:00
20141019 19:33:46 20150111 20150111 00:00:00
20141021 19:33:46 20150112 20150112 00:00:00
20141022 19:33:46 20150113 20150113 00:00:00
20141023 19:33:46 20150114 20150114 00:00:00
20141024 19:33:47 20150115 20150115 00:00:00
20141017 19:33:46 20150121 20150121 00:00:00
20141020 19:33:46 20150131 20150131 00:00:00
20141007 19:35:18 20150201 20150201 00:00:00
20141008 19:35:18 20150202 20150202 00:00:00
20141009 19:35:19 20150203 20150203 00:00:00
20141010 19:35:19 20150204 20150204 00:00:00
20141011 19:35:20 20150205 20150205 00:00:00
20141012 19:35:20 20150206 20150206 00:00:00
20141014 19:35:21 20150208 20150208 00:00:00
20141015 19:35:21 20150209 20150209 00:00:00
20141016 19:35:22 20150210 20150210 00:00:00
20141019 19:35:22 20150211 20150211 00:00:00
20141021 19:35:23 20150212 20150212 00:00:00
20141022 19:35:23 20150213 20150213 00:00:00
20141023 19:35:23 20150214 20150214 00:00:00
20141024 19:35:23 20150215 20150215 00:00:00
20141017 19:35:22 20150221 20150221 00:00:00
32 rows selected.
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
