大家好，太顶了我是两万哪吒，最近项目有一个新的字详需求，​​按月建表，解O技术按天分区​​。区表

不都是太顶了分库分表吗？怎么又来个分区？

让我们一起，一探究竟，两万深入理解一下Oracle分区表技术，字详实现快速入门，解O技术丰富个人简历，区表提高面试level，太顶了给自己增加一点谈资，两万秒变面试小达人，字详BAT不是解O技术梦。

三分钟你将学会：

一、Oracle是如何存储数据的？​

在国企或者一线大厂，一般都会选择使用Oracle数据库，程序通过mybatis等持久层框架访问Oracle数据库，指定表空间，表空间内包含若干张表，表中存有行数据，行数据以行片段的形式存储在数据库块中，IT技术网① 当插入的行太大，无法装入单个块时；② 或因为更新的缘故，导致现有行超出了当前空间时 -> 就会发生整个行不存储在一个位置的情况。

Oracle在逻辑上将数据存储在表空间中，在物理上将数据存储在数据文件中。

表空间包括若干个数据文件，这些表空间使用与运行Oracle软件的操作系统一致的物理结构。数据库的数据存储在构成数据库表空间的数据文件中。

临时文件是一个临时表空间的文件；它是通过TEMPFILE选项创建的。临时表空间不包含表，通常用于排序。

数据库块包含块头、行数据、可用空间。

块头包含段类型（如表或索引）、数据块地址、表目录、行目录和事务处理插槽。

每个插槽的大小为24 字节，修改块中的行时会使用这些插槽。

块中行的实际数据。

可用空间位于块的中部，允许头和行数据空间在必要时进行增长。当插入新行或用更大的值更新现有行的列时，行数据会占用可用空间。

块中的可用空间最初是相邻的。WordPress模板但是，删除和更新操作可能会使块中的可用空间变成碎片，需要时Oracle 服务器会接合块中的空闲空间。

二、Oracle分区表技术​

分区是指表和索引可以被分成若干个部分，它们拥有相同的逻辑属性和数据结构。所有分区的字段和索引都是一样的。

分区表是将表数据分为若干个可以被单独管理的片，每个片就是一个分区，分一个分区都可以拥有自己的物理属性，比如表空间、事务槽、存储参数、最小区段数等，通过建分区语句指定，提升可用性和存储效率。

每个分区可以被单独管理，降低管理成本和备份成本，提高容错率，避免“一荣既荣，一损俱损”的问题。

（1）优点

（2）缺点

普通表和分区表不能直接转换，可以通过数据迁移，再重命名的方式实现，需要重建约束、索引，在创建表时可以添加关键字“parallel compress”并行执行，提高效率，下面会通过SQL实例介绍。

单表的数据量如果过大，会影响SQL的读写性能，我们可以通过分库分表的方式解决表性能的问题，Oracle的分区表是将一张大表在物理上分成几个较小的表，从逻辑上看仍然是一张完整的表。这样，每次DML操作只考虑其中一张分区表即可。

那么，临界点是多少呢？

注意：单个分区的数据可以超过500万，但存储空间不建议超过2GB。

三、分区相关的数据字典​

根据数据字典表的前缀不同，可查询的内容及权限有所差异：

以下是分区表的一些相关字典表，前缀是“DBA_”、“ALL_”、“USER_”；

四、分区表的分类

将数据基于范围映射到每一个分区，这个范围是由创建分区表时指定的分区键决定。

一般选取id或者时间作为范围分区的分区键。

(

,

),

),

)

)

(

))

(

),

))

(

)

);

)

;

)

❝

每个块都有一个块首部。这个块首部中有一个事务表。事务表中会建立一些条目来描述哪些事务将块上的哪些行/元素锁定。这个事务表的初始大小由对象的INITRANS 设置指定。对于表，这个值默认为2（索引的INITRANS 也默认为2）。事务表会根据需要动态扩展，最大达到MAXTRANS 个条目（假设块上有足够的自由空间）。所分配的每个事务条目需要占用块首部中的23～24 字节的存储空间。注意，对于Oracle 10g，MAXTRANS 则会忽略，所有段的MAXTRANS 都是255。

由于oracle块里有一个PCT_free的概念，即oracle会预留块大小的10%作为缓冲，当修改oracle的事务增加时，事务槽向下增长，当更新oracle块的数据时，数据向上增长，PCT_free的空间被压缩。

❞

local索引是针对单个分区表的索引。

在对分区表进行维护操作时需检查索引是否失效，索引失效除了会导致查询慢，还会导致数据写入失败，在ALTER TBALE语法中也可以添加关键字“UPDATE INDEXES”避免维护表时索引失效。

));

));

跨分区查询时，查询每个分区的数据后使用UNION ALL关键字做集合查询，提高查询效率。

,

,

(

,

),

),

)

)

(

),

),

)

);

)

;

)

));

));

(

,

),

),

)

)

(

),

)

范围分区和列表分区都是使用某一个字段进行分区，此字段的分区度大才行，但也会产生诸多问题，比如上述的按技术列表分区，现阶段，Java开发人员明显高于C，此时就会导致分区不均匀的问题。

此时，hash分区闪亮登场，hash分区的好处是让分区更均匀一些。

(

,

),

),

)

)

(

,

,

,

,

此时，插入200条数据，id从1到200，验证一下是否均匀。数据条数分别是51、55、61、33。

刚创建好分区，worker_id_5就有数据了，why？匪夷所思。

添加分区时，所有数据会重新计算hash值，重新分配到不同的分区表中。

(

,

),

),

)

)

(

))

(

),

),

)

),

))

(

),

),

)

)

));

));

));

));

));

));

));

))

(

),

),

)

为WORKER20230529添加子分区technology_go_29：

(

,

),

),

)

)

(

))

(

,

,

,

),

))

(

,

,

,

)

));

));

));

));

));

));

));

));

));

复制select count(1) from worker_202306 PARTITION (WORKER20230628);

select count(1) from worker_202306 PARTITION (WORKER20230629);

select count(1) from worker_202306 SUBPARTITION (worker_id_1);

select count(1) from worker_202306 SUBPARTITION (worker_id_2);

select count(1) from worker_202306 SUBPARTITION (worker_id_5);

由于hash分区的缘故，数据分布不均匀。

(

,

),

),

)

)

(

)

(

,

,

,

),

)

(

,

,

,

)

));

));

));

));

));

));

));

));

));

);

);

);

);

);

五、对已有表进行分区​

(

,

),

),

(

,

),

),

)

)

(

)

;

;

成功转移。

;

AT括号内不能是字段名称，改为数字即可。

)

注意：不能修改分区列的数据类型

通过sql查询验证分区是否成功。

通过sql查询分区数据：

截断分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。

复制ALTER TABLE WORKER_202309

MERGE PARTITIONS WORKER2,WORKER3

六、小结​

使用Oracle这么久，第一次系统的了解Oracle的存储结构，Oracle -> 表空间 -> 段 -> 区 -> 逻辑数据块。

了解了Oracle分区表技术适用于哪些场景、何时分区、分区表的分类，并通过SQL实例进行了实战演练。

本文转载自微信公众号「哪吒编程」，可以通过以下二维码关注。转载本文请联系哪吒编程公众号。