oracle 11g分区表类型,ORACLE11G 分区表新特性

1.引用分区表

使子表使用父表的分区条件进行分区，而无需在子表中额外添加用于分区的列，造成数据逆规范化，造成空间浪费等问题。

参考实例:

(1).创建主表

CREATE TABLE orders

(

order#       NUMBER PRIMARY KEY,

order_date   DATE NOT NULL,

data         VARCHAR2 (30)

)

PARTITION BY RANGE

(order_date)

(

PARTITION

part_2016 VALUES LESS THAN (TO_DATE ('01-01-2017', 'dd-mm-yyyy')),

PARTITION

part_2017 VALUES LESS THAN (TO_DATE ('01-01-2018', 'dd-mm-yyyy')))

/

insert into orders values ( 1, to_date( '01-01-2017', 'dd-mm-yyyy' ), 'xxx' );

insert into orders values ( 2, to_date( '01-01-2016', 'dd-mm-yyyy' ), 'yyy' );

commit;

(2).创建子表

create table order_line_items

(

order#      number NOT NULL,

line#      number NOT NULL,

data      varchar2(30),

constraint c1_pk primary key(order#,line#),

constraint c1_fk_p foreign key(order#) references orders

)

enable row movement

partition by reference(c1_fk_p)

/

insert into order_line_items values  ( 1, 1, 'yyy' );

insert into order_line_items values  ( 2, 1, 'yyy' );

SELECT table_name, partition_name

FROM user_tab_partitions

WHERE table_name IN ('ORDERS', 'ORDER_LINE_ITEMS')

ORDER BY table_name, partition_name;

TABLE_NAME           PARTITION_NAME

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

ORDERS               PART_2016

ORDERS               PART_2017

ORDER_LINE_ITEMS     PART_2016

ORDER_LINE_ITEMS     PART_2017

(3).删除父表partition，可以级联删除子表分区

alter table orders drop partition part_2016 update global indexes;

TABLE_NAME           PARTITION_NAME

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

ORDERS               PART_2017

ORDER_LINE_ITEMS     PART_2017

(4).添加父表partition，可以级联添加子表分区

alter table orders add partition part_2018 values less than (to_date( '01-01-2019', 'dd-mm-yyyy' ));

TABLE_NAME           PARTITION_NAME

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

ORDERS               PART_2017

ORDERS               PART_2018

ORDER_LINE_ITEMS     PART_2017

ORDER_LINE_ITEMS     PART_2018

(5).删除子表partition，报错

alter table order_line_items drop partition PART_2017 update global indexes;

ORA-14255: table is not partitioned by Range, List, Composite Range or Composite List method

(6).truncate子表partition，可行。

alter table admin.order_line_items truncate partition PART_2017;

2.间隔分区表

定义分区规则，当有符合条件的数据插入时自动创建分区。

可以使用alter命令将现有的区间分区表修改为间隔分区，也可以使用create创建一个间隔分区。

interval分区的特点

-由range分区派生而来

-以定长宽度创建分区(比如年、月、具体的数字(比如100、500等))

-分区字段必须是number或date类型

-必须至少指定一个range分区(永久分区)

-当有记录插入时，系统根据需要自动创建新的分区和本地索引

-已有的范围分区可被转换成间隔分区(通过ALTER TABLE SET INTERVAL选项完成)

-Interval Partitioning不支持支持索引组织表

-在Interval Partitioning表上不能创建domain index

参考示例:

- 创建间隔分区表

CREATE TABLE admin.orders2

(

order#       NUMBER PRIMARY KEY,

order_date   DATE NOT NULL

)

PARTITION BY RANGE

(order_date)

INTERVAL ( NUMTOYMINTERVAL (1, 'month') )

(

PARTITION

p201612 VALUES LESS THAN (TO_DATE ('01-01-2017', 'dd-mm-yyyy')));

- 插入数据

insert into admin.orders2 values(1,TO_DATE ('01-12-2016', 'dd-mm-yyyy'));

insert into admin.orders2 values(2,TO_DATE ('02-01-2017', 'dd-mm-yyyy'));

insert into admin.orders2 values(3,TO_DATE ('02-02-2017', 'dd-mm-yyyy'));

查看自动生成分区

SELECT table_name, partition_name

FROM user_tab_partitions

WHERE table_name IN ('ORDERS2')

ORDER BY table_name, partition_name;

TABLE_NAME           PARTITION_NAME

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

ORDERS2              P201612

ORDERS2              SYS_P121

ORDERS2              SYS_P122

- 删除数据已生成的分区不变

TABLE_NAME           PARTITION_NAME

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

ORDERS2              P201612

ORDERS2              SYS_P121

ORDERS2              SYS_P122

- 将原有的range分区表变更为interval分区表

ALTER TABLE admin.orders SET INTERVAL(1000000);

ALTER TABLE admin.orders SET INTERVAL (NUMTODSINTERVAL(1,'DAY')); --NUMTODSINTERVAL常用的单位有 ('day','hour','minute','second')

ALTER TABLE admin.orders SET INTERVAL (numtoyminterval(1,'month')); --numtoyminterval常用的单位有'year','month'

- 将interval分区表转为range分区表

ALTER TABLE admin.orders3 SET INTERVAL();

3.虚拟列分区

(1).只能在堆组织表(普通表)上创建虚拟列

(2).虚拟列不能是LOB或者RAW类型；

(3).虚拟列的值并不是真实存在的，只有用到时，才根据表达式计算出虚拟列的值，磁盘上并不存放。

(4).可把虚拟列当做分区关键字建立分区表，这是ORACLE 11g的另一新特性--虚拟列分区

创建带有虚拟列的分区表：

create table test(n1 number, c1 varchar2(80), n2 number generated always as (n1*0.8)) ---创建带有虚拟列的分区

create table test1(n1 number,

c1 varchar2(80),

v1 varchar2(2) generated always as (substr(c1,1,1))

)

partition by list (v1)

(partition v11 values('I'),

partition v12 values('O'),

partition v13 values('E'),

partition v15 values(default)

);

查询：select * from test1 partition(v11);

(5).可在虚拟列上建立索引

create index inx_test on test1(v1);

(6).如果在已经创建的表中增加虚拟列时，若没有指定虚拟列的字段类型；

ORACLE会根据 generated always as 后面的表达式计算的结果自动设置该字段的类型

(7).虚拟列的值由ORACLE根据表达式自动计算得出，不可以做UPDATE和INSERT操作， 可以对虚拟列做 DELETE 操作

(8).表达式中的所有列必须在同一张表

(9).表达式不能使用其他虚拟列

4.系统分区表

- 系统自动生成分区，无分区条件

- 数据和分区没有关系

- 不能建立local index

- 插入数据时要指明分区

CREATE TABLE admin.order4

(

col1   NUMBER,

name   VARCHAR2 (100)

)

PARTITION BY SYSTEM

(PARTITION p1,

PARTITION p2,

PARTITION p3,

PARTITION p4);

insert into admin.order4 partition(p2) select col1,name from admin.order4;

5.完全组合分区

(1).范围分区(range)

(2).哈希分区(hash)

(3).列表分区(list)

(4).范围－哈希复合分区(range-hash)

(5).范围－列表复合分区(range-list)

oracle11g 新增

range-range,list-range,list-list,list-hash