重庆网站建设子沃科技,wordpress文章摘要缩略图,凡客网站建立,网络服务商基本网络参数目录 一. 前置知识1.1 什么是表分区1.2 表分区的优势1.3 表分区的使用条件 二. 表分区的方法2.1 范围分区#xff08;Range Partitioning#xff09;2.2 列表分区#xff08;List Partitioning#xff09;2.3 哈希分区#xff08;Hash Partitioning#xff09;2.4 复合分… 目录 一. 前置知识1.1 什么是表分区1.2 表分区的优势1.3 表分区的使用条件 二. 表分区的方法2.1 范围分区Range Partitioning2.2 列表分区List Partitioning2.3 哈希分区Hash Partitioning2.4 复合分区Composite Partitioning 三. 表分区的查看3.1 分区信息的查看3.2 分区数据的查看 四. 分区表的维护4.1 分区的添加与删除4.2 分区的合并与拆分4.3 重命名分区4.4 分区交换4.5 其他操作 一. 前置知识
1.1 什么是表分区
表分区是数据库中的一种技术用于将一张大表的数据按一定的规则拆分成多个较小的物理存储单元这些物理存储单元被称为分区Partition。 尽管数据被分布到不同的分区中但从逻辑上分区表仍然作为一个整体表进行访问。 1.2 表分区的优势
⏹易于管理
管理更灵活可以对某个分区单独执行备份、恢复、索引重建等操作。删除数据更高效可以直接删除整个分区而无需删除每一行数据。
⏹数据分割
数据被分配到多个分区中每个分区可以独立存储在不同的表空间或磁盘上。例如按时间、区域、ID 等规则划分数据。
⏹性能优化
查询和操作时可以通过 分区裁剪Partition Pruning减少扫描的数据量从而提高查询效率。分区表支持并行操作Parallelism多个分区可以同时被不同的进程访问或操作。
⏹提高存储利用率
不同分区可以存储在不同的表空间或磁盘上优化存储性能和分布。
1.3 表分区的使用条件
表大于2G对一个表并行进行DML操作为了平衡硬盘I/O需将同一个表分区到不同的表空间必须对表进行分区要将表的一部分设为只读状态另一部分设为读写状态需要对表进行分区要将表一部分设为可用状态另一部分设为不可用状态需要队标进行分区要将表中数据按照一定规则分散到不同的磁盘中去需要对表进行分区 二. 表分区的方法
2.1 范围分区Range Partitioning
⏹范围分区是根据分区列值的范围对表进行分区每条记录根据分区列值的范围分配到不同的分区表中。 常用于按照时间和日期分区的表。
partition by range指定使用范围分区 partition sales_q1_2013指定分区的名称values less than指定分区内数据的存储范围tablespace TBS1指定该分区所对应的表空间若不显示指定的话则使用默认表空间
create table sale_by_range
(prod_id number, cust_id number,time_id date,channel_id char(1),promo_id number(6),quantity_sold number(3),amount_sold number
)
partition by range(time_id)
(-- 若 time_id 2013-01-01 , 则数据分布到 sales_q1_2013 分区partition sales_q1_2013 values less than(TO_DATE(2013-01-01, YYYY-MM-DD)) tablespace TBS1,-- 若 2013-01-01 time_id 2016-04-01 , 则数据分布到 sales_q2_2016 分区partition sales_q2_2016 values less than(TO_DATE(2016-04-01, YYYY-MM-DD)) tablespace TBS2,partition sales_q3_2019 values less than(TO_DATE(2019-04-01, YYYY-MM-DD)) tablespace TBS3,partition sales_q4_2021 values less than(TO_DATE(2021-04-01, YYYY-MM-DD)) tablespace TBS4,-- 2021-04-01 time_id , 则数据分布到 sales_q5_202X 分区partition sales_q5_202X values less than (MAXVALUE) tablespace TBS5
)2.2 列表分区List Partitioning
⏹按离散值列表划分数据适合按地区、类别等分类的数据。
partition otherprovince values(default)如果列值不符合其它分区记录的要求保存在该分区表
create table sales_by_list
(dept number,deptname varchar(20),quarterly_sales number(10,2),province varchar(20)
)
partition by list(province)
(-- 广东、福建的存放在southeast分区表partition southeast values(guangdong,fujian) tablespace TBS1,-- 黑龙江、辽宁、吉林放在northeast分区表 partition northeast values(heilongjiang,liaoning,jilin) tablespace TBS2,-- 四川、云南、贵州放在southwest分区表partition southwest values(sichuan,yunnan,guizhou) tablespace TBS3,-- 其他省的放在otherprivince分区表partition otherprovince values(default) tablespace TBS4
)2.3 哈希分区Hash Partitioning
⏹按哈希函数将数据均匀分布到多个分区中。适合数据分布随机、访问均匀的场景。
在 Oracle 数据库中哈希分区Hash Partitioning 的分区名称通常由系统自动生成例如 SYS_P564、SYS_P565等。用户无法直接在建表语句中指定哈希分区的名称。
create table charge_fixed_work (charge_no number(20,0) not null primary key,atzt_no number(20,0) not null,charge_regst_busins_date varchar2(8) not null,charge_expct_date varchar2(8) not null,charge_regst_date varchar2(8) not null,charge_regst_time varchar2(8) not null,charge_proc_result_cd varchar2(8) not null
)
partition by hash (charge_no)
partitions 4
-- 指定4个分区存储在指定的表空间中
store in (tablespace1, tablespace2, tablespace3, tablespace4);2.4 复合分区Composite Partitioning
⏹Oracle支持的6种复合分区方法
Range-RangeRange-ListRange-HashList-RangeList-ListList-Hash
CREATE TABLE sales_by_range_list (dept NUMBER,deptname VARCHAR(20),time_id DATE,quarterly_sales NUMBER(10, 2),province VARCHAR(20)
)
-- 先使用范围分区
PARTITION BY RANGE (time_id)
-- 再使用列表分区进一步细化
SUBPARTITION BY LIST (province)
(-- 第一季度销售情况的分区并指定表空间PARTITION sales_q1_2024 VALUES LESS THAN (TO_DATE(2024-03-01, YYYY-MM-DD)) TABLESPACE TBS1(-- 广东、福建的存放在 sales_q1_2024_southeast 分区表SUBPARTITION sales_q1_2024_southeast VALUES (guangdong, fujian),SUBPARTITION sales_q1_2024_northeast VALUES (heilongjiang, liaoning, jilin),SUBPARTITION sales_q1_2024_southwest VALUES (sichuan, yunnan, guizhou),SUBPARTITION sales_q1_2024_otherprovince VALUES (DEFAULT)),-- 第二季度销售情况PARTITION sales_q2_2024 VALUES LESS THAN (TO_DATE(2024-06-01, YYYY-MM-DD)) TABLESPACE TBS2(SUBPARTITION sales_q2_2024_southeast VALUES (guangdong, fujian),SUBPARTITION sales_q2_2024_northeast VALUES (heilongjiang, liaoning, jilin),SUBPARTITION sales_q2_2024_southwest VALUES (sichuan, yunnan, guizhou),SUBPARTITION sales_q2_2024_otherprovince VALUES (DEFAULT)),-- 第三季度销售情况PARTITION sales_q3_2024 VALUES LESS THAN (TO_DATE(2024-09-01, YYYY-MM-DD)) TABLESPACE TBS3(SUBPARTITION sales_q3_2024_southeast VALUES (guangdong, fujian),SUBPARTITION sales_q3_2024_northeast VALUES (heilongjiang, liaoning, jilin),SUBPARTITION sales_q3_2024_southwest VALUES (sichuan, yunnan, guizhou),SUBPARTITION sales_q3_2024_otherprovince VALUES (DEFAULT)),-- 第四季度销售情况PARTITION sales_q4_2024 VALUES LESS THAN (MAXVALUE) TABLESPACE TBS4(SUBPARTITION sales_q4_2024_southeast VALUES (guangdong, fujian),SUBPARTITION sales_q4_2024_northeast VALUES (heilongjiang, liaoning, jilin),SUBPARTITION sales_q4_2024_southwest VALUES (sichuan, yunnan, guizhou),SUBPARTITION sales_q4_2024_otherprovince VALUES (DEFAULT))
);三. 表分区的查看
3.1 分区信息的查看
DBA_PART_TABLES查看数据库里面的全部分区表信息需要具有DBA权限ALL_PART_TABLES查看当前用户可见的全部分区表信息USER_PART_TABLES查看当前用户拥有的全部分区表信息
⏹查看当前用户拥有的的全部分区表信息
SELECT * FROM USER_PART_TABLES;⏹查询分区表信息
SELECT * FROM USER_TAB_PARTITIONS;⏹查询子分区表信息
SELECT * FROM USER_TAB_SUBPARTITIONS;3.2 分区数据的查看
⏹在向分区表插入数据时我们最关心的就是数据是否按照我们的规划进入了各个分区表例如向SALE_BY_RANGE表中投入数据
INSERT INTO sale_by_range (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) VALUES (1, 1, TO_DATE(2012-12-12, YYYY-MM-DD), A, 1, 10, 100);
INSERT INTO sale_by_range (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) VALUES (2, 2, TO_DATE(2013-02-15, YYYY-MM-DD), B, 2, 20, 200);
INSERT INTO sale_by_range (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) VALUES (3, 3, TO_DATE(2014-06-05, YYYY-MM-DD), A, 3, 30, 300);
INSERT INTO sale_by_range (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) VALUES (4, 4, TO_DATE(2016-05-01, YYYY-MM-DD), B, 4, 40, 400);
INSERT INTO sale_by_range (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) VALUES (5, 5, TO_DATE(2019-06-20, YYYY-MM-DD), A, 5, 50, 500);
INSERT INTO sale_by_range (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) VALUES (6, 6, TO_DATE(2021-05-15, YYYY-MM-DD), B, 6, 60, 600);
INSERT INTO sale_by_range (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) VALUES (7, 7, TO_DATE(2016-02-19, YYYY-MM-DD), A, 7, 70, 700);
INSERT INTO sale_by_range (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) VALUES (8, 8, TO_DATE(2021-07-10, YYYY-MM-DD), B, 8, 80, 800);
INSERT INTO sale_by_range (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) VALUES (9, 9, TO_DATE(2019-09-09, YYYY-MM-DD), A, 9, 90, 900);
INSERT INTO sale_by_range (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) VALUES (10, 10, TO_DATE(2021-01-01, YYYY-MM-DD), B, 10, 100, 1000);
INSERT INTO sale_by_range (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) VALUES (11, 11, TO_DATE(2012-12-25, YYYY-MM-DD), A, 11, 110, 1100);
INSERT INTO sale_by_range (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) VALUES (12, 12, TO_DATE(2013-03-10, YYYY-MM-DD), B, 12, 120, 1200);
INSERT INTO sale_by_range (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) VALUES (13, 13, TO_DATE(2015-07-20, YYYY-MM-DD), A, 13, 130, 1300);
INSERT INTO sale_by_range (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) VALUES (14, 14, TO_DATE(2016-11-22, YYYY-MM-DD), B, 14, 140, 1400);
INSERT INTO sale_by_range (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) VALUES (15, 15, TO_DATE(2018-03-15, YYYY-MM-DD), A, 15, 150, 1500);
INSERT INTO sale_by_range (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) VALUES (16, 16, TO_DATE(2020-06-17, YYYY-MM-DD), B, 16, 160, 1600);
INSERT INTO sale_by_range (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) VALUES (17, 17, TO_DATE(2019-05-02, YYYY-MM-DD), A, 17, 170, 1700);
INSERT INTO sale_by_range (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) VALUES (18, 18, TO_DATE(2020-12-20, YYYY-MM-DD), B, 18, 180, 1800);
INSERT INTO sale_by_range (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) VALUES (19, 19, TO_DATE(2020-08-10, YYYY-MM-DD), A, 19, 190, 1900);
INSERT INTO sale_by_range (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) VALUES (20, 20, TO_DATE(2021-02-14, YYYY-MM-DD), B, 20, 200, 2000);⏹若 2013-01-01 time_id 2016-04-01 , 则数据理论上会分布到 sales_q2_2016 分区
-- SELECT * FROM 表名 PARTITION(分区名);
SELECT * FROM SALE_BY_RANGE PARTITION(SALES_Q2_2016);四. 分区表的维护
4.1 分区的添加与删除
⏹可以通过 ALTER TABLE 添加新的分区。通常是在范围分区表中您会根据需要添加新的分区。
ALTER TABLE sales_by_range
ADD PARTITION sales_q6_2023 VALUES LESS THAN (TO_DATE(2023-07-01, YYYY-MM-DD))
TABLESPACE TBS6;⏹当某些分区数据不再需要时可以删除相应的分区。
ALTER TABLE sales_by_range
DROP PARTITION sales_q1_2013;4.2 分区的合并与拆分
⏹合并分区可以将多个分区的数据合并成一个分区这对减少分区的数量或管理分区非常有用。
ALTER TABLE sales_by_range
MERGE PARTITIONS sales_q1_2013, sales_q2_2013
INTO PARTITION sales_q1_q2_2013;⏹对于复合分区表如果需要管理子分区可以合并多个子分区。
ALTER TABLE sales_by_range
MERGE SUBPARTITIONS sales_q1_2024_southeast, sales_q1_2024_northeast
INTO SUBPARTITION sales_q1_2024_southeast_northeast;⏹拆分分区用于将现有分区分成两个或更多的分区。这对于将过大的分区拆分为更小的分区非常有用。
ALTER TABLE sales_by_range
SPLIT PARTITION sales_q3_2019 AT (TO_DATE(2019-06-01, YYYY-MM-DD))
INTO (PARTITION sales_q3_2019_part1,PARTITION sales_q3_2019_part2
);4.3 重命名分区
⏹您可以重命名分区以便更清楚地反映其数据的含义。
ALTER TABLE sales_by_range
RENAME PARTITION sales_q1_2024 TO sales_q1_2024_new;4.4 分区交换
⏹将分区中的数据交换到其他表或分区。可以用来快速移动数据。
ALTER TABLE sales_by_range
EXCHANGE PARTITION sales_q1_2013
WITH TABLE temp_sales_q1_2013;4.5 其他操作
⏹分区可以禁用以便不再插入新数据或启用以恢复操作。
ALTER TABLE sales_by_range DISABLE PARTITION sales_q4_2021;⏹定期监控分区表的性能和空间使用情况。可以使用查询查看分区表的分区状态。
SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE, TABLESPACE_NAME
FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME SALES_BY_RANGE;⏹使用分区表时定期对数据进行压缩如针对历史数据可以节省空间。压缩可以在分区级别进行。
ALTER TABLE sales_by_range MOVE PARTITION sales_q1_2013 TABLESPACE TBS1 COMPRESS FOR ALL OPERATIONS;⏹重新组织分区表以提高查询性能或释放空间。这包括重新排序和重新分配存储空间。
ALTER TABLE sales_by_range REORGANIZE PARTITION sales_q2_2024;⏹定期检查分区表的健康状态并对损坏的分区进行修复。
SELECT * FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME SALES_BY_RANGE;-- 若有问题可以使用以下语句修复
ALTER TABLE sales_by_range RECOVER PARTITION sales_q3_2019;
