oracle表分区管理partition

环境：oracle12c

1、表分区大的种类

复制代码
表的分区 ：  具有相同的逻辑属性，每个分区可以具有独立的物理属性
分区键 partition key
分区表中的每一行都需要明确分配给某个分区(通过分区键)
分区表 partition table
表中具有long和long raw数据类型的表不允许做分区表

1)Range         #通过值的范围方式建分区
2)Hash          #通过hash值方式建分区
3)List          #通过列举值的方式建分区

分区字典表
SELECT * FROM dba_part_tables            #查看数据库存在分的分区表
SELECT * FROM dba_tab_partitions         #查看表的分区信息
SELECT * FROM dba_part_key_columns       #查看表分区的字段（分区键）
SELECT * FROM dba_part_indexes           #查看表分区的索引
复制代码

2、range分区

复制代码
a、创建range分区
CREATE TABLE hr.orders(
  orders_id number,
  orders_date date,
  orders_comment VARCHAR2(200)
)
PARTITION BY RANGE(orders_date)(
  PARTITION orders_2020_q1 VALUES LESS THAN(to_date('2020-04-01','yyyy-mm-dd')) TABLESPACE tbs03,
  PARTITION orders_2020_q2 VALUES LESS THAN(to_date('2020-07-01','yyyy-mm-dd')) TABLESPACE test01,
  PARTITION orders_2020_q3 VALUES LESS THAN(to_date('2020-10-01','yyyy-mm-dd')) ,
  PARTITION orders_2020_q4 VALUES LESS THAN(to_date('2021-01-01','yyyy-mm-dd'))
);

ALTER TABLE hr.orders TRUNCATE  PARTITION orders_2020_q1;                                                                             #清空分区表数据
ALTER TABLE hr.orders DROP  PARTITION orders_2020_q1;                                                                                 #删除某个分区
ALTER TABLE hr.orders                                                                                                                 #添加分区
ADD PARTITION orders_2021_q1 VALUES LESS THAN(to_date('2021-04-01','yyyy-mm-dd')) TABLESPACE tbs03;                                   
ALTER TABLE hr.orders                                                                                                                 #合并两个分区为一个分区
MERGE PARTITIONS orders_2020_q2,orders_2020_q3 INTO PARTITION orders_2020_Q2_Q3;                                                     
ALTER TABLE hr.orders                                                                                                                 #拆分一个分区为两个分区
SPLIT PARTITION orders_2020_q2_q3 AT(to_date('2020-07-01','yyyy-mm-dd')) INTO (PARTITION orders_2020_q2 TABLESPACE test01,PARTITION orders_2020_q3 TABLESPACE tbs02);
复制代码

3、Hash分区

复制代码
a、创建hash分区（4个分区分别存储在不同表空间，自动生成分区名称）
CREATE TABLE hr.test_hash01(              
  id number,
  name varchar2(20)
)
PARTITION BY HASH(id)
PARTITIONS 4 STORE IN(users,test01,tbs02,tbs03);

--实验插入hash表数据
begin
  for i in 1..1000 loop
    INSERT INTO hr.test_hash01 VALUES(i,i||'a');
  end loop;
    COMMIT;
end;

b、创建hash分区手动指定分区名称
CREATE TABLE hr.test_hash02(
  id number,
  name varchar2(20)
)
PARTITION BY HASH(id)(
  PARTITION thash02_p1 TABLESPACE tbs02,
  PARTITION thash02_p2 TABLESPACE users,
  PARTITION thash02_p3 TABLESPACE tbs03,
  PARTITION thash02_p4 TABLESPACE test01
);
复制代码

4、list分区

复制代码
a、创建list分区，根据不同地点来分区
CREATE TABLE hr.test_list01(
  orders_id NUMBER,
  orders_city VARCHAR2(20)
)
PARTITION BY LIST(orders_city)(
  PARTITION tl_east VALUES('Shanghai','Jiangsu')       TABLESPACE test01,
  PARTITION tl_south VALUES('Guangzhou','Guangxi')  TABLESPACE tbs02,
  PARTITION tl_west VALUES('Sichuang','Guizhou')      TABLESPACE users,
  PARTITION tl_north VALUES('Beijing','Henan')           TABLESPACE tbs03
);

b、需要添加tl_west分区的list值 （比较麻烦分三步：先添加一个分区、合并分区、改名）
ALTER TABLE hr.test_list01                               #添加分区
ADD PARTITION tl_yunan VALUES('Yunan');
ALTER TABLE hr.test_list01                               #合并分区
MERGE PARTITIONS tl_yunan,tl_west INTO PARTITION tl_others TABLESPACE test01;
ALTER TABLE hr.test_list01                                #改成以前的名称
RENAME PARTITION tl_others TO tl_west;

c、拆分list分区（将tl_west拆分成三个独立分区）
ALTER TABLE hr.test_list01
SPLIT PARTITION TL_WEST INTO(
  PARTITION tl_west_01 VALUES('Yunan') TABLESPACE tbs02,
  PARTITION tl_west_02 VALUES('Sichuang') TABLESPACE tbs03,
  PARTITION tl_west_03 TABLESPACE test01
);
复制代码

5、INTERVAL Partition  间隔分区（属于range分区的一种）（该种分区表可以根据插入的数据自己建分区）

复制代码
该分区键只能是当个列 数据类型number或者date类型
numtoyminterval
numtodsinterval
a、创建分区   （创建一个分区表，以三个月一个分区，插入的值大于分区规定会按照要求自动新建分区）
CREATE TABLE hr.test_interval01(
  orders_id number,
  orders_date date
)
PARTITION BY RANGE(orders_date)
INTERVAL(numtoyminterval(3,'MONTH'))( 
  PARTITION ti01_2020_q1 VALUES LESS THAN(to_date('2020-04-01','yyyy-mm-dd')) TABLESPACE tbs02
);

INSERT INTO hr.test_interval01 VALUES(2,sysdate+300);              #插入一个值，分区表自动再建一个分区
COMMIT;
复制代码

6、reference partition  引用分区，子表的分区对应主表的分区（引用父表的分区信息）

复制代码
a、创建主表分区
CREATE TABLE hr.test_orders(
  order_id number primary key,
  order_date date
)
PARTITION BY RANGE(order_date)(
PARTITION torders_2020_q1 VALUES LESS THAN(to_date('2020-04-01','yyyy-mm-dd')) TABLESPACE tbs03,
  PARTITION torders_2020_q2 VALUES LESS THAN(to_date('2020-07-01','yyyy-mm-dd')) TABLESPACE test01,
  PARTITION torders_2020_q3 VALUES LESS THAN(to_date('2020-10-01','yyyy-mm-dd')),
  PARTITION torders_2020_q4 VALUES LESS THAN(to_date('2021-01-01','yyyy-mm-dd'))
);

b、创建子表分区
CREATE TABLE hr.test_order_item(
  id NUMBER PRIMARY KEY,
  order_id NUMBER NOT NULL,
  data VARCHAR2(200),
  CONSTRAINT testoi_fk_orderid FOREIGN KEY(order_id) REFERENCES hr.test_orders(order_id)    --该约束为主表的外键
)
PARTITION BY REFERENCE(testoi_fk_orderid);                                                  --通过外键建立链接
复制代码

7、Virtual Column-Based Partitioning 虚拟列分区（通过虚拟的键创建分区）

复制代码
a、创建分区
CREATE TABLE hr.test_vbp01(
  id number PRIMARY KEY,
  name varchar2(20),
  phone CHAR(11) NOT NULL,
  phone_prefix AS (substr(phone,1,3))                                                      --该键为电话号码前三位
)
PARTITION BY LIST(phone_prefix)(
  PARTITION tvbp_p01 VALUES ('130','131','132'),
  PARTITION tvbp_p02 VALUES ('133','134','135'),
  PARTITION tvbp_p03 VALUES ('136','137','138'),
  PARTITION tvbp_p04 VALUES ('139','189','188'),
  PARTITION tvbp_other VALUES (default)
);
复制代码

8、SYSTEM PARTITION 系统分区（该分区只指定分区名称，不指定键，用户插入表的时候需要指定对应分区插入）

复制代码
a、创建系统分区
CREATE TABLE hr.system_partition01(
  id number PRIMARY KEY,
  name varchar2(20) NOT NULL
)
PARTITION BY SYSTEM(
  PARTITION tsp_01 TABLESPACE tbs02,
  PARTITION tsp_02 TABLESPACE tbs03,
  PARTITION tsp_03 TABLESPACE test01,
  PARTITION tsp_04 TABLESPACE test01
);

b、插入数据
INSERT INTO hr.system_partition01 VALUES(1,'A');                   -error
SQL 错误: ORA-14701: 对于按“系统”方法进行分区的表, 必须对 DML 使用分区扩展名或绑定变量
INSERT INTO hr.system_partition01 PARTITION(tsp_01) VALUES(1,'A');  --OK
复制代码

9、Composite Range-List Partitioning 联合分区，通过多个判断标准创建分区

复制代码
a、创建分区
CREATE TABLE hr.trange_list01(
  orderid NUMBER PRIMARY KEY,
  order_date DATE NOT NULL,
  order_city VARCHAR2(20)
)
PARTITION BY RANGE(order_date)
SUBPARTITION BY LIST(order_city)(
  PARTITION tl_2020_q1 VALUES LESS THAN(to_date('20200401','yyyymmdd')) TABLESPACE USERS(     --第一层通过时间range创建分区
    SUBPARTITION tl_2020_q1_east VALUES('Shanghai','Jiangshu'),                               --第二层通过list创建分区
    SUBPARTITION tl_2020_q1_soutth VALUES('Guangzhou','Guangxi'),
    SUBPARTITION tl_2020_q1_west VALUES('Sichuan','Guizhou'),
    SUBPARTITION tl_2020_q1_north VALUES('Beijing','Henan'),
    SUBPARTITION tl_2020_q1_other VALUES(default)
  ),
  PARTITION tl_2020_q2 VALUES LESS THAN(to_date('20200701','yyyymmdd')) TABLESPACE TEST01(
    SUBPARTITION tl_2020_q2_east VALUES('Shanghai','Jiangshu','Zhejiang'),
    SUBPARTITION tl_2020_q2_soutth VALUES('Guangzhou','Guangxi'),
    SUBPARTITION tl_2020_q2_west VALUES('Sichuan','Guizhou','Yunyan'),
    SUBPARTITION tl_2020_q2_north VALUES('Beijing','Henan','Hebei'),
    SUBPARTITION tl_2020_q2_other VALUES(default)
  ),
  PARTITION tl_2020_q3 VALUES LESS THAN(to_date('20201001','yyyymmdd')) TABLESPACE TBS02(
    SUBPARTITION tl_2020_q3_east VALUES('Shanghai','Jiangshu','Zhejiang'),
    SUBPARTITION tl_2020_q3_soutth VALUES('Guangzhou','Guangxi'),
    SUBPARTITION tl_2020_q3_west VALUES('Sichuan','Guizhou','Yunyan'),
    SUBPARTITION tl_2020_q3_north VALUES('Beijing','Henan','Hebei'),
    SUBPARTITION tl_2020_q3_other VALUES(default)
  ),
  PARTITION tl_2020_q4 VALUES LESS THAN(to_date('20210101','yyyymmdd')) TABLESPACE USERS(
    SUBPARTITION tl_2020_q4_east VALUES('Shanghai','Jiangshu','Zhejiang'),
    SUBPARTITION tl_2020_q4_soutth VALUES('Guangzhou','Guangxi'),
    SUBPARTITION tl_2020_q4_west VALUES('Sichuan','Guizhou','Yunyan'),
    SUBPARTITION tl_2020_q4_north VALUES('Beijing','Henan','Hebei'),
    SUBPARTITION tl_2020_q4_other VALUES(default)
  )
);
复制代码

10、分区表的分区索引 （最好就建local索引，一个分区对应一个分区索引）

复制代码
分区索引分为两种
本地索引 local index  分区索引
全局索引 global index 可以选择是否分区   分区索引使用前缀的分区索引

分区索引
有前缀prefix的分区索引  分区的索引必须包含分区键 并且作为引导列的索引
无前缀prefix的分区索引

字典解析
desc dba_part_indexes
DBA_PART_INDEXES displays the object-level partitioning information for all partitioned indexes in the database.
LOCALITY Indicates whether the partitioned index is local (LOCAL) or global (GLOBAL)

desc dba_ind_partitions
DBA_IND_PARTITIONS describes all index partitions in the database.

--创建全局分区索引
CREATE INDEX hr.orders_id_global ON hr.orders(orders_date) GLOBAL    ---引导列  全局分区索引  前缀
PARTITION BY RANGE(orders_date) --分区键
(
  PARTITION p1 VALUES LESS THAN(to_date('20200401','yyyymmdd')),
  PARTITION p2 VALUES LESS THAN(to_date('20200701','yyyymmdd')),
  PARTITION p3 VALUES LESS THAN(to_date('20201001','yyyymmdd')),
  PARTITION p4 VALUES LESS THAN(to_date('20210101','yyyymmdd')),
  PARTITION p5 VALUES LESS THAN(to_date('20210401','yyyymmdd')),
  PARTITION p6 VALUES LESS THAN(MAXVALUE)
);

--local索引  局部索引一定是分区索引 分区键等于表的分区键
CREATE INDEX hr.orders_idx_local ON hr.orders(orders_date) LOCAL;

--分区索引
ALTER INDEX index_name REBUILD PARTITION partition_name;
Oracle自动维护分区索引，对于全局索引，对分区表进行操作，会导致整个全局索引失效，需要重建。

ALTER TABLE hr.orders
DROP PARTITION xxx UPDATE INDEXES;

ALTER INDEX  REBUILD global_index_name [ONLINE];
复制代码
posted @   苍茫宇宙  阅读(1438)  评论(0)  编辑  收藏  举报
编辑推荐：
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
阅读排行：
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理 了，记录一下
· 【杭电多校比赛记录】2025“钉耙编程”中国大学生算法设计春季联赛（1）
点击右上角即可分享
微信分享提示