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.虚拟列分区

以下内容来自https://www.cnblogs.com/moonandstar08/p/5100567.html

(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