百度360必应搜狗淘宝本站头条
mqtt服务器xftp6mybatiscollectionkeyerrorc#var
当前位置：网站首页 > 热门文章 > 正文

Oracle优化-分区表的详细解释和操作(四)

bigegpt 2025-02-08 10:08 9 浏览

一、什么是表分区

表分区其实就是将一张大数据量表中的数据按照不同的分区策略分配到不同的系统分区、硬盘或是不同的服务器设备上，实现数据的均衡分配，这样做的好处是均衡大数据量数据到不同的存储介子中，这样每个分区均摊了一部分数据，然后可以定位到指定的分区中，对数据表进行需求操作，另外，也方便管理水表，比如要删除某个时间段的数据，就可以按照日期分区，然后直接删除该日期分区即可，并且效率相对于传统的DELETE数据效率高很多。


二、分区的条件 官方建议：

1、单表过大，当表的大小超过 2GB。

2、包含历史数据的表，其中新数据被添加到最新的分区中。一个典型的例子是历史表，其中只有当月的数据是可更新的，其他11个月的数据是只读的。

实际应用中每月或年超过5000w可以考虑分区。oracle 18c中，3000w的表加了并行也不慢。

三、分区表的优势 官方文档

  1. 分区支持数据管理操作，例如数据加载，索引创建和重建，以及分区级别的备份/恢复，而不是整个表。这导致这些操作的时间显着减少。
  2. 分区可提高查询性能。在许多情况下，查询的结果可以通过访问分区的子集而不是整个表来实现。对于某些查询，此技术（称为分区 修剪）可以提供性能的数量级增益。
  3. 分区可以显着减少计划停机对维护操作的影响。
  4. 分区维护操作的分区独立性允许您在同一个表或索引的不同分区上执行并发维护操作。您还可以SELECT对不受维护操作影响的分区运行并发和DML操作。
  5. 如果将关键表和索引划分为多个分区以减少维护窗口，恢复时间和故障影响，则分区可提高任务关键型数据库的可用性。
  6. 无需对应用程序进行任何修改即可实现分区。例如，您可以将非分区表转换为分区表，而无需修改SELECT访问该表的任何语句或DML语句。您无需重写应用程序代码即可利用分区。

四、分区类型及SQL语句

1. 范围分区 range ，在数据类项目中主要放结果表，用来供给应用系统查询。一般按照日期等分区。

范围分区即根据指定的值范围进行分区，分区键常用日期。分区的列是一个或者多个。

例句
create table table_name(列1，列2，……)
partition by range(分区列1，列2)--支持多列
(
partition 分区1 values less than (值1,值2)  tablespace t1, --tablespace可以省略
partition 分区2 values less than (值3,值4) ，-
...
partition 分区n values less than (maxvalue,值5)
)
说明：
values less than 指定分区上限，每个分区除第一个分区外会有下限，即前一个分区的上限。
如上语句所示，分区2的范围为>分区1，<分区2
maxvalue 表示无穷值，一旦出现第一个maxvalue，那么后面字段无论是什么都代表maxvalue。
举例1：多字段
CREATE TABLE sales_demo (
   year          NUMBER, 
   month         NUMBER,
   day           NUMBER) 
PARTITION BY RANGE (year,month) 
  (PARTITION before2001 VALUES LESS THAN (2001,1)  tablespace t1, --tablespace可以省略
   PARTITION q1_2001    VALUES LESS THAN (2001,4),
   PARTITION q2_2001    VALUES LESS THAN (2001,7),
   PARTITION q3_2001    VALUES LESS THAN (2001,10),
   PARTITION q4_2001    VALUES LESS THAN (2002,1),
   PARTITION future     VALUES LESS THAN (MAXVALUE,0));--这行代表无限
举例2：一个字段
create table range_part_tab (seq number,deal_date date)
partition by range (deal_date)
(
partition p1 values less than (TO_DATE('2018-11-01','YYYY-MM-DD')),
partition p2 values less than (TO_DATE('2018-12-02','YYYY-MM-DD')),
partition p3 values less than (TO_DATE('2019-01-01','YYYY-MM-DD')),
partition p4 values less than (TO_DATE('2019-02-01','YYYY-MM-DD')),
partition p5 values less than (TO_DATE('2019-03-01','YYYY-MM-DD')),
partition p6 values less than (TO_DATE('2019-04-01','YYYY-MM-DD')),
);

2. 列表分区 list

列表分区适用于分区列的值为非数字或日期数据类型，并且分区列的取值范围较少时使用。例如，成绩表中的科目列取值较少，就可以应用列表分区，实际中的机构分区等。

create table achievement
(
	id number primary key,
    name varchar2(8),
    subject varchar2(10),
    score number
)
partition by list(subject)--不支持多列
(
  partition Li_ke values('数学','物理','化学') tablespace xxx,--tablespace可以省略
  partition Wen_ke values('语文','英语','历史') tablespace yyy
)

3. 散列分区 hash 官方文档

Hash分区时，数据库根据hash算法映射行到用户指定的分区键中。行的存放目的地由数据库的内部hash函数来决定。hash算法的目的是在设备上均匀分布行，以便每个分区包含相同数量的行。哈希分区可以轻松地对数据进行分区，而不需要进行范围或列表分区。它用简单的语法做到了这一点，并且易于实现。在以下情况下，这是比范围分区更好的选择

  • 您事先不知道有多少数据映射到给定的范围内
  • 范围分区的大小差别很大，或者难以手动平衡
  • 范围划分会导致数据不合需要地聚集
  • 并行DML、分区修剪和分区方式等性能特性
  • 拆分、删除或合并分区的概念不适用于哈希分区。相反，可以添加和合并哈希分区。
create table table_name()
partition by hash(列名，列名2)--支持多个列
(
partition 分区名1,
partition 分区名2,
...
)

4. 组合分区

基于两种分区的组合，分区中的分区被称为子分区。oracle11之后支持(范围范围分区)RANGE-RANGE、 (列表范围分区)LIST-RANGE、(列表散列分区)LIST-HASH、(列表列表分区)LIST-LIST这几种组合，为了避免每个主分区中都写相同的从分区，可以用模板方式(subpartition template)

create table tablename (id varchar2(10),name  varchar2(10),hdate date )
partition by range (hdate)  subpartition by list (id)
subpartition template
(subpartition s1 values (211),
subpartition s2 values (212),
subpartition s0 values (DEFAULT) )
(
partition p1 values less than (TO_DATE('2018-11-01','YYYY-MM-DD')),
partition p2 values less than (TO_DATE('2018-12-02','YYYY-MM-DD')),
);--表示p1和p2分区每个里面都有 s1,s2,s0子分区。--未测过

举两个例子： 范围-列表分区

--范围列表分区

create table 表名()
partition by range(列名1) subpartition by list(列名2)
(
partition 分区名1 values less than(值)
(
subpartition 子分区名 values (列值),
...
),
partition 分区名2 values less than(值)
(
....
)
)

--范围-散列分区

create table table_name()
partition by range(列1) subpartition by hash(列2，列3...)
(
partition 分区名1 values less than (值)
(
subpartition 子分区1,
subpartition 子分区2,
...
),
partition 分区名2 values less than （值）
(
......
)
)

五、间隔分区

  • 间隔分区是范围分区的一种扩展
  • 当插入的数据超过了所有范围分区时，将创建指定间隔的分区
  • 必须至少创建一个范围分区
  • 间隔分区可以自动创建范围分区
  • 间隔分区需要考虑下列限制：
  • ? 只能指定一个分区键列，并且该键列必须是Number或Date类型
  • ? 索引表不支持间隔分区
  • ? 不能为间隔分区表创建域索引

具体查看
https://blog.csdn.net/weixin_30895723/article/details/104257825

 --语法
CREATE TABLE table_name
(
  ...
)
PARTITION BY RANGE(column1)
INTERVAL expr [STORE IN (tablespace1,[tablespace2,...])]
(
  PARTITION partition_name1 VALUES LESS THAN(literal | MAXVALUE) [TABLESPACE tablespace1],
  PARTITION partition_name2 VALUES LESS THAN(literal | MAXVALUE) [TABLESPACE tablespace2]
);
--PARTITION BY RANGE(column1):指定一个分区范围列
--INTERVAL:指定分区间隔
--STORE IN：指定分区存储的表空间
--举例
create table achievement
(
	id number primary key,
    name varchar2(8),
    subject varchar2(10),
    score number
)
partition by range(score) interval(10)
(
	partition part1 values less then(60) tablespace xxx,
    partition part2 values less then(70) tablespace yyy
);
--插入一条80的数据，会发现新建立了一个分区。

六、分区索引

需要区分创建的是全局索引，或本地索引，较复杂，可以查看相关文章

六、分区并行

可以使用并行语句

ALTER INDEX ... MOVE PARTITION parallel 10;

ALTER INDEX ...SPLIT PARTITION parallel 10;

七、表分区的查询和维护

1. 查询: 
   select * from user_tab_partitions t;
2. 添加: 
   alter table  add partition  values less than(to_date('2020-02-02', 'YYYY-MM-DD'));
   alter table  add partition  values less than(1000);
3. 删除: (请注意：无法删除分区表唯一的分区，除非删除表)
   alter table  drop partition ;
   alter table  drop subpartition ;
4. 截断分区（'清空某个分区的数据'）
   alter table  truncate partition ;
   alter table  truncate subpartition ;
5. 拆分分区（'拆分后，原来分区不再存在'）
   alter table  sblit partition  at(to_date('2020-01-01', 'YYYY-MM-DD')) into (partition p1, partition p2);
6. 合并分区
   alter table  merge partitions ,  into partition ;
7. 重命名分
   alter table  rename partition  to

八、分区相关查询

查看文章：
https://blog.csdn.net/u014427391/article/details/90315716

（1）查询数据库所有分区表的信息
select * from DBA_PART_TABLES

（2）查询分区表类型、是否有子分区，分区总数
select pt.partitioning_type, pt.subpartitioning_type, pt.partition_count
from user_part_tables pt

（3）查询分区详细详细：
SELECT tab.* FROM USER_TAB_PARTITIONS tab WHERE TABLE_NAME='LIST_PART_TAB'

（4）查询分区表哪列建分区
select column_name, object_type, column_position
from user_part_key_columns
where name = 'LIST_PART_TAB';

（5）查询分区表大小
select sum(bytes / 1024 / 1024)
from user_segments
where segment_name = 'LIST_PART_TAB';

（6）查询分区表各分区的大小和分区名
select partition_name, segment_type, bytes
from user_segments
where segment_name = 'LIST_PART_TAB';

（7）查询分区表各索引大小
select segment_name, segment_type, sum(bytes) / 1024 / 1024
from user_segments
where segment_name in
(select index_name
from user_indexes
where table_name = 'LIST_PART_TAB')
group by segment_name, segment_type;

（8）查询分区表的统计信息
select table_name,
partition_name,
last_analyzed,
partition_position,
num_rows
from user_tab_statistics
where table_name = 'LIST_PART_TAB';

（9）查询分区表索引情况
select table_name,
index_name,
last_analyzed,
blevel,
num_rows,
leaf_blocks,
distinct_keys,
status
from user_indexes
where table_name = 'LIST_PART_TAB';

（10）查询索引在哪些列上
select index_name, column_name, column_position
from user_ind_columns
where table_name = 'LIST_PART_TAB';

（11）查询普通表失效的索引
select ind.index_name,
ind.table_name,
ind.blevel,
ind.num_rows,
ind.leaf_blocks,
ind.distinct_keys
from user_indexes ind
where status = 'INVALID';

（12）查询分区表失效的索引
select a.blevel,
a.leaf_blocks,
a.index_name,
b.table_name,
a.partition_name,
a.status
from user_ind_partitions a, user_indexes b
where a.index_name = b.index_name
and a.status = 'UNUSABLE';

相关推荐

【Docker 新手入门指南】第十章:Dockerfile

Dockerfile是Docker镜像构建的核心配置文件，通过预定义的指令集实现镜像的自动化构建。以下从核心概念、指令详解、最佳实践三方面展开说明，帮助你系统掌握Dockerfile的使用逻...

Windows下最简单的ESP8266_ROTS_ESP-IDF环境搭建与腾讯云SDK编译

前言其实也没啥可说的,只是我感觉ESP-IDF对新手来说很不友好,很容易踩坑,尤其是对业余DIY爱好者搭建环境非常困难,即使有官方文档,或者网上的其他文档,但是还是很容易踩坑,多研究,记住两点就行了,...

python虚拟环境迁移（python虚拟环境conda）

主机A的虚拟环境向主机B迁移。前提条件：主机A和主机B已经安装了virtualenv1.主机A操作如下虚拟环境目录：venv进入虚拟环境：sourcevenv/bin/active(1)记录虚拟环...

Python爬虫进阶教程(二):线程、协程

简介线程线程也叫轻量级进程，它是一个基本的CPU执行单元，也是程序执行过程中的最小单元，由线程ID、程序计数器、寄存器集合和堆栈共同组成。线程的引入减小了程序并发执行时的开销，提高了操作系统的并发性能...

基于网络安全的Docker逃逸（docker）

如何判断当前机器是否为Docker容器环境Metasploit中的checkcontainer模块、（判断是否为虚拟机，checkvm模块）搭配学习教程1.检查根目录下是否存在.dockerenv文...

Python编程语言被纳入浙江高考，小学生都开始学了

今年9月份开始的新学期，浙江省三到九年级信息技术课将同步替换新教材。其中，新初二将新增Python编程课程内容。新高一信息技术编程语言由VB替换为Python，大数据、人工智能、程序设计与算法按照教材...

CentOS 7下安装Python 3.10的完整过程

1.安装相应的编译工具yum-ygroupinstall"Developmenttools"yum-yinstallzlib-develbzip2-develope...

如何在Ubuntu 20.04上部署Odoo 14

Odoo是世界上最受欢迎的多合一商务软件。它提供了一系列业务应用程序，包括CRM，网站，电子商务，计费，会计，制造，仓库，项目管理，库存等等，所有这些都无缝集成在一起。Odoo可以通过几种不同的方式进...

Ubuntu 系统安装 PyTorch 全流程指南

当前环境：Ubuntu22.04，显卡为GeForceRTX3080Ti1、下载显卡驱动驱动网站：https://www.nvidia.com/en-us/drivers/根据自己的显卡型号和...

spark+python环境搭建（python 环境搭建）

最近项目需要用到spark大数据相关技术，周末有空spark环境搭起来...目标spark，python运行环境部署在linux服务器个人通过vscode开发通过远程python解释器执行代码准备...

centos7.9安装最新python-3.11.1（centos安装python环境）

centos7.9安装最新python-3.11.1centos7.9默认安装的是python-2.7.5版本，安全扫描时会有很多漏洞，比如：Python命令注入漏洞(CVE-2015-2010...

Linux系统下，五大步骤安装Python

一、下载Python包网上教程大多是通过官方地址进行下载Python的，但由于国内网络环境问题，会导致下载很慢，所以这里建议通过国内镜像进行下载例如：淘宝镜像http://npm.taobao.or...

centos7上安装python3（centos7安装python3.7.2一键脚本）

centos7上默认安装的是python2，要使用python3则需要自行下载源码编译安装。1.安装依赖yum-ygroupinstall"Developmenttools"...

利用本地数据通过微调方式训练 本地DeepSeek-R1 蒸馏模型

网络上相应的教程基本都基于LLaMA-Factory进行，本文章主要顺着相应的教程一步步实现大模型的微调和训练。训练环境：可自行定义，mac、linux或者window之类的均可以，本文以ma...

【法器篇】天啦噜，库崩了没备份（天啦噜是什么意思?）

背景数据库没有做备份，一天突然由于断电或其他原因导致无法启动了，且设置了innodb_force_recovery=6都无法启动，里面的数据怎么才能恢复出来？本例采用解析建表语句+表空间传输的方式进行...

一周热门
最近发表
标签列表