oracle 表分区含义和使用场合，表分区的方式

1.表分区和使用场景

1.1 作用

  允许用户将表分成多个分区；用户可以执行查询，只访问表中特定的分区；将不同的分区存储在不同的磁盘上，提高访问性能和安全性；可以独立地备份和恢复每个分区；

查看表的分区信息:

select * from user_tab_partitions u where u.table_name='大写表名';

1.2 传统表分区的类型

1.2.1 范围分区

以表的一列或者一组列的值的范围分区

语法：

partition by range (列名)(
partition 分区1 values less than (范围1),
partition 分区2 values less than (范围2),
...
[partition 分区n values less than (maxvalue)]
);

如：

create table sales(
p_id varchar2(10),
s_count number
) partition by range (s_count )(
partition p1 values less than (1000),
partition p2 values less than (2000),
partition pn values less than (3000)
);

查看表的分区信息:

select * from user_tab_partitions u where u.table_name='SALES';

往分区表插入数据：

查询特定分区数据：

select * from sales partition(p1);

增加分区

alter table sales add partition p4 values less than (maxvalue);

注

1.2.2 散列分区

  允许用户对不具有逻辑范围的数据进行分区；通过在分区键上执行hash函数决定存储的分区；将数据平均分布到不同的分区；

如：

create table my_emp(empno number, ename varchar2(10))
partition by hash(empno)(partition p1,partition p2);

1.2.3 列表分区

  允许用户将不相关的数据组织到一起；

create table personCity(id number,city varchar2(10))
partition by list (city)(
partition p1 values('开封','商丘'),
partition p2 values('洛阳'),
partition p3 values('许昌'),
partition p4 values('高安')
);

1.2.4 复合分区

  范围分区与散列分区或列表分区的组合

partition by range (列名)
subpartition by hash(列名)
subpartitions 子分区数量
(
partition 分区1 values less than (范围1),
partition 分区2 values less than (范围2),
...
[partition 分区n values less than (maxvalue)]
);

子分区数量：对于每个分区会有n个子分区

create table student(sno number, sname varchar2(10))
partition by range (sno)
subpartition by hash(sname)
subpartitions 4
(
partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (maxvalue)
);

通过em查看表信息

1.3 11g新增分区

1.3.1 引用分区

  基于由外键引用的父表的分区的方法，它依赖于已有的父表子表关系，子表通过外键关联到父表，进而继承父表的分区方式而不需要自己创建，子表还继承父表的维护操作。

CREATE TABLE orders
    ( order_id NUMBER(12),
      order_date TIMESTAMP,
      order_mode VARCHAR2(8),
      customer_id NUMBER(6),
      order_status NUMBER(2),
      order_total NUMBER(8,2),
      sales_rep_id NUMBER(6),
      promotion_id NUMBER(6),
      CONSTRAINT orders_pk PRIMARY KEY(order_id)
    )
  PARTITION BY RANGE(order_date)
    ( PARTITION Q1 VALUES LESS THAN (TO_DATE('01-04-2022','DD-MM-YYYY')),
      PARTITION Q2 VALUES LESS THAN (TO_DATE('01-07-2022','DD-MM-YYYY')),
      PARTITION Q3 VALUES LESS THAN (TO_DATE('01-10-2022','DD-MM-YYYY')),
      PARTITION Q4 VALUES LESS THAN (TO_DATE('01-01-2023','DD-MM-YYYY'))
    );

CREATE TABLE order_items
    ( order_id NUMBER(12) NOT NULL,
      line_item_id NUMBER(3) NOT NULL,
      product_id NUMBER(6) NOT NULL,
      unit_price NUMBER(8,2),
      quantity NUMBER(8),
      CONSTRAINT order_items_fk
      FOREIGN KEY(order_id) REFERENCES orders(order_id)
    )
    PARTITION BY REFERENCE(order_items_fk);

1.3.2 间隔分区

  可以完全自动根据间隔阈值创建范围分区，是范围分区的扩展。在数据仓库中有广泛的应用。

create table sale_detail(
sale_detail_id number,
product_id number,
quantity number,
sale_date date
)
partition by range(sale_date)
interval(numtoyminterval(1,'MONTH'))
(partition p_202206 values less than(to_date('20220328','yyyymmdd')));

插入数据后自动增加分区

insert into sale_detail values(1,100,20,to_date('20220101','yyyymmdd'));
insert into sale_detail values (2,100,20,to_date('20220401','yyyymmdd'));
insert into sale_detail values(3,100,20,to_date('20220601','yyyymmdd'));
commit;

1.3 .3 基于虚拟列的分区

  把分区建立在某个虚拟列上，即建立在函数或表达式的计算结果上，来完成某种任务

create table sale(
sale_id number primary key,
product_id number,
price number,
quantity number,
sale_date date,
total_price as (price*quantity) virtual
)
partition by range(total_price)(
partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (maxvalue)
);

insert into sale(sale_id ,product_id,price,quantity,sale_date) values(1,100,20,50,to_date('20220101','yyyymmdd'));
commit;

select * from sale partition(p2);

1.3.4 系统分区

  不指定分区列，由oracle来完成分区的控制和管理，没有范围分区或者列表分区的界限

create table person(
id number,
name varchar2(20),
address varchar2(20)
)partition by system
(partition p1,
partition p2,
partition p3
);

2.分区操作

2.1 添加分区

alter table sales add partition p4 values less than (5000);

2.2 删除分区

alter table sales drop partition p4 ;

2.3 截断分区(删除分区中的数据)

alter table sales truncate  partition p4 ;

2.4 合并分区

alter table sales merge partitions p1 ,p2 into partition p2 ;

2.5 拆分分区

alter table sales split partition p2 at(1500) into (partition p1,partition p2) ;