Oracle partitions 简介

全栈程序员站长

发布于 2022-09-13 11:07:37

4980

发布于 2022-09-13 11:07:37

文章被收录于专栏：全栈程序员必看

大家好，又见面了，我是你们的朋友全栈君。

基于Oracle11.2.04

分区表的概念

当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间（物理文件上），这样查询数据时，不至于每次都扫描整张表。

分区表的优点

改善查询性能：对分区对象的查询可以公搜索自己关心的分区，提高检索速度。
增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用。
维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可。
均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

什么时候需要分区

官网的建议：

Tables greater than 2GB should always be considered for partitioning.
tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month’s data is updatable and the other 11 months ar read only.

分区表的种类

范围分区

Range分区就是以列的值的范围来做为分区的划分条件，将记录放到列值所在的range分区中，因此在创建的时候，你需要指定基于的列，以及分区的范围值，如果某些记录暂时无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中，并且支持多列做为依赖列。语法：

column: 分区依赖列（如果是多个，以逗号分隔）
partition: 分区名称
values less than：后跟分区范围值（如果依赖列有多个，范围对应的值也有多个，中间以逗号分隔）
tablespace_clause：分区的存储属性，例如果所在表空间等属性（可为空），默认继承基表所在表空间的属性。

e.g.

create table table_name(
...
)
partition by range(column_name)
(
    partition p1 values less than(values1),
    partition p2 values less than(values2),
    partition other values less than(maxvalue)
);

interval partition

interval partition的限制

intervall partition的限制包括“Restrictions on Parrtition in Gennerall ” 和” Restrictions on Range Parrtitioning”。
只能指定一个partitioning key列，而且这个列只能是NUMBER，DATE, FLOAT和TIMESTAMP数据类型。
必须指定至少一个range分区
当有记录插入时，系统根据需要自动创建新的分区和本地索引
。。。

e.g. 按月自动创建分区。

create table table_name(
 ...
) tablespace tablespace_name
partition by range(partitions_field) interval(numtoyminterval(1, 'month')) 
store in (tablespace_name)
(
    partition partition_name_01 values less than(TIMESTAMP'2018-01-01 00:00:00') tablespace tablespace_name
)

interval分区和range分区的转换

允许分区表的分区键是可更新

指当分区列的值更新后，就会把行数据从指定分区中delete掉，然后插入新的分区表。

alter table table_name enable row movement;

Hash分区

根据字段的hash值进行均匀分区，尽可能的实现各分区所散列的数据相等。通常是那些无法有效划分范围的表，使用hash分区，这样对于提高性能还是会有一定的帮助。列所在分区是依赖分区列的hash值自动分配，因此你并不能控制也不知道哪条记录会被放到哪个分区中，hash分区也可以支持多个依赖列。 Hash分区定义规则：在定义Hash分区时，其分区数量应为2的N次方。对于Hash分区，如果更改分区的数量，将导致所有数据在分区间的重新分布。 e.g.

create table table_name(
...
)
partition by hash(column_name)
(
    partition p1,
    partition p2,
    partition p3,
    ...
);

List分区

List分区与range分区和hash分区都有类似之处，该分区与range分区相似的是也需要你指定列的值，但你必须明确指定值，并不能指定范围。与hash分区不同之处在于你能控制记录存储在哪个分区。但它的分区列只能有一个，不过它的单个分区对应的值可以是多个。你在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入、更新就会失败，因此通过建议使用List分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。

create  table table_name(
    ...
)
partition by list(column)
(
    partition value1 values('1'),
    partition value2 values('2'),
    ...
)

组合分区

注意：对于同一张组合分区表而言，子分区的名字不能相同，即使不在相同的分区里。

范围-散列分区 e.g.

create table table_name(
    ...
)
partition by range(column1)
subpartition by hash(column2)
(
    partition p1 values less than(oneValue)
    (
        subpartition sp1,
        subpartition sp2,
        ...
    ),
    partition p2 values less than(maxvalue)
    (
        subpartition sp3,
        subpartition sp4,
        ...
    )
);

2… 范围-列表分区 e.g.

create table table_name(
   ...
)
partition by  range(column2) subpartition by list(column3)
(
    partition p1 values  less than('value1')
    (
        subpartition one values('01', '11'),
        subpartition two values('02', '22'),
        ...
    ),
    partition p2 values less than('value2')
    (
        subpartition three values('03', '33'),
        ...
    ),
    ...
)

索引

分区索引分为两类，一类叫local，一类叫global。

全局索引

是在全局建立索引，这种方式和建立不建立分区一样一般不使用。还有一种就是自定义数据区间的索引，也叫做前缀索引，不过自定义区域值必须有maxvalue。 global自定义全局（前缀索引）索引方式方法：

    create index index_namme on  table(field) global 
    partition by range(field) (
        partition parrtition_name1 values less than(values),
        partition partition_name2 values less thhan(maxvalue)
    )

global全局索引：

    create index index_name on tablle(fiield) global;

Local索引

在每个分区建立索引。需要注意，在分区上建立的索引必须是分区字段。

    create index index_name on table(field) local;

查看分区索引

    select * from  user_ind_partitions;

分区表操作

查询分区表

修改分区信息状态后需要重新收集分区表的统计信息。

查询分区表

    selecct table_name, partitioning_type, partition_count, status 
    from dba_part_tables
    where table_name = 'table_name'

查询单独分区表

    select * from table_name partition(partition_name)

查询分表各分区的条件以及数据库分布情况

    sellect a.table_name, partitioning_type, partition_name, high_value, num_rows
    from user_part_tables a, user_tab_parrtitions b
    where a.table_name = b.table_name

收集分区表的统计信息

    analyze table test_range_partition coompute statistics;

创建新分区

注意：当分区表存在默认条件分区，如：range分区表的maxvalue分区，list分区表的default分区，此时增加分区操作会报错。可以删除原默认条件分区，待增加分区后，再重新添加默认条件分区. 不过默认条件的分区的删除，其数据不会重分布到其他分区，而是删除数据。因此在生产环境使用需要慎重。

或者使用拆分分区（split）的方式。

删除分区

对于分区的删除操作，需要注意，在删除分区后，分区所记录的数据，不会重新分配到其他分区中，而是被删除。慎用。

    alter table table_name drop partition partition_name;

移动分区

移动分区所在的表空间，不过对于组合分区，无法直接移动分区。需要先修改子分区的所在表空间，然后再修改分区的分区属性。

    alter  table table_name move partition partition tablespace tablespace_name;

截断分区

截断分区维护操作，相对于传统的delete操作，删除数据的效率会更高。而且会降低高水位线。

    alter table table_name truncate partition partition_name;

拆分分区

在目标分区被拆分后，被拆分的分区会按照拆分规则，将数据进行重分布。

    alter table table_name split partition partition_name at (values) into (
    partition partition_name,
    ...);

???

合并分区

合并分区操作，主要是将不同的分区，通过分区的合并，进行整合。需要注意：

对于List分区，合并的分区无限制要求。
对于Range分区，合并的分区必须相临。
对于Hash分区，无法进行合并操作。此外，对于Range分区，下限由边界值较低的分区决定，上限值由边界值较高的分区决定。

    alter table table_name merget partitions partition_name1,  partition_name2 [,...] into partition_name_merge;

交换分区

交换分区技术，主要是将一个非分区表的数据同一个分区表的一个分区进行数据交换。支持双向交换，既可以从分区表的分区中迁移到非分区表，也可以从非分区表迁移到分区表的分区中。原则上，非分区表的结构、数据分布等，要符合分区表的目标分区的定义规则。解决办法是通过without validation子句，可以避免数据校验，而交换成功。但会存在与分区规则相悖的数据，因此在生产环境使用需要慎重。技术方案扩展思路：若打算采用交换分区的方法，以实现非分区表到分区表的转换，可以采用先创建一个只有默认条件的单一分区的分区表，在分区交换数据后，根据实际需要，通过前面提到的“拆分分区”的方法进行分区操作。

    alter table table_name_has_partitions exchange partition partition_name with table table_name_no_partitions;

收缩分区维护操作（colaesce）

收缩分区维护操作，仅仅可以在hash分区以及组合分区的hash子分区上进行使用。通过使用收缩分区技术，可以收缩当前hash分区的分区数量。对于hash分区的数据，在收缩过程中，oracle会自动完成数据在分区间的重分布。

    alter table table_name coalesce partition;

需要注意：当hash分区中只有一个分区时，此时无法进行收缩操作。

分割分区

附录

显示分区表信息

显示数据库所有分区表的信息：DBA_PART_TABLES
显示当前用户可访问的所有分区表信息：ALL_PART_TABLES
显示当前用户所有分区表的信息：USER_PART_TABLES

显示表分区信息

显示数据库所有分区表的详细分区信息：DBA_TAB_PARTITIONS
显示当前用户可访问的所有分区表的详细分区信息：ALL_TAB_PARTITIONS
显示当前用户所有分区表的详细分区信息：USER_TAB_PARTITIONS

显示子分区信息

显示数据库所有组合子分区表的子分区信息：DBA_TAB_SUBPARTITIONS
显示当前用户可访问的所有组合分区表的子分区信息：ALL_TAB_SUBPARTITIONS
显示当前用户所有组合分区表的子分区信息： USER_TAB_SUBPARTITIONS

显示分区列

显示数据库所有分区表的分区列信息：DBA_PART_KEY_SOLUMNS
显示当前用户可访问的所有分区表的分区列信息：ALL_PART_KEY_SOLUMNS
显示当前用户所有分区表的分区列信息：USER_PART_KEY_COLUMNS

显示子分区信息

显示数据库所有组合分区表的子分区信息：DBA_TAB_SUBPARTITIONS
显示当前用户可访问的所有组合分区表的子分区信息：ALL_TAB_SUBPARTITIONS
显示当前用户所有组合分区表的子分区的信息：USER_TAB_SUBPARTITIONS

显示分区列

显示数据库所有分区表的分区列信息：DBA_PART_KEY_COLUMNS
显示当前用户可访问的所有分区表的分区列信息：ALL_PART_KEY_COLUMNS
显示当前用户所有分区表的分区列信息：USER_PART_KEY_COLUMNS

显示子分区列

显示数据库所有分区表的子分区列信息：DBA_SUBPART_KEY_COLUMNS
显示当前用户可访问的所有分区表的子分区列信息：ALL_SUBPART_KEY_COLUMNS
显示当前用户所有分区表的子分区列信息：USER_SUBPART_KEY_COLUMNS

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/153296.html原文链接：https://javaforall.cn

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

sql

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

数据库

sql

登录后参与评论

0 条评论

热度