【对象管理】分区表

最新推荐文章于 2024-06-05 00:50:20 发布

flighting_sky

最新推荐文章于 2024-06-05 00:50:20 发布

阅读量2.6k

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle Management 文章标签：分区表

本文链接：https://blog.youkuaiyun.com/flighting_sky/article/details/9984841

Oracle Management 专栏收录该内容

22 篇文章

订阅专栏

本文介绍了数据库分区的益处，包括提升查询性能、增强可用性、简化维护和均衡I/O。详细阐述了如何创建范围分区、列表分区及散列分区，并探讨了分区的删除、截断、合并、拆分和交换操作。此外，还讨论了分区索引的种类及其创建，以及如何查看分区相关信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

第一，改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。

第二，增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；

第三，维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；

第四，均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。

一、创建分区表

1、创建范围分区表

create table range_part 
(time_id date,
 QUANTITY_SOLD NUMBER(10,2) ,
 AMOUNT_SOLD NUMBER(10,2)
)partition by range(time_id)
(
   partition part_1998 values less than (to_timestamp('1999-01-01 00:00:00','YYYY-MM-DD HH24:Mi:SS')),
   partition part_1999 values less than (to_timestamp('2000-01-01 00:00:00','YYYY-MM-DD HH24:Mi:SS')),
   partition part_2000 values less than (to_timestamp('2001-01-01 00:00:00','YYYY-MM-DD HH24:Mi:SS')),
   partition part_next values less than (maxvalue)
);----超过界限的处理：maxvalue

2、创建列表分区

create table list_part
( prod_id number,
  QUANTITY_SOLD  NUMBER(10,2),                                                                                                                                                                                  
  AMOUNT_SOLD    NUMBER(10,2)
)partition by list(prod_id)
(
   partition list_01 values(13,14,15,16,17),
   partition list_02 values(18,19,20,21,22),
   partition list_next values (default)
);---超过界限的处理：default

3、创建散列分区

这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。

create table hash_part
( prod_id number,
  QUANTITY_SOLD  NUMBER(10,2),                                                                                                                                                                                  
  AMOUNT_SOLD    NUMBER(10,2)
)partition by hash(prod_id)
(
    partition hash_01, 
    partition hash_02, 
    partition hash_03,
    partition hash_04
);

4、创建组合分区

分区的三种类型（range,list,hash）可以根据需求两两组合，建立组合分区，基本语法差不多.

create table range_hash_part
( prod_id number,
  time_id date,
  QUANTITY_SOLD  NUMBER(10,2),                                                                                                                                                                                  
  AMOUNT_SOLD    NUMBER(10,2)
)partition by range(time_id) subpartition by list(prod_id)
(
    partition range_01 values less than (to_timestamp('1999-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))
    (
       subpartition range_01_list_01 values(13,14,15,16,17),
       subpartition range_01_list_02 values(18,19,20,21,22),
       subpartition range_01_list_03 values(default)
    ),
    partition range_02 values less than(maxvalue)
    (
       subpartition range_02_list_01 values(13,14,15,16,17),
       subpartition range_02_list_02 values(18,19,20,21,22),
       subpartition range_02_list_03 values(default)
    )
);

二、管理分区
第一：添加分区

需要注意的是，所添加的分区分区界限必须要比最后一个分区界限要高。如下，由于最后一个分区的分区界限为maxvalue也就是最大值，所以不能再直接添加分区。

alter table range_hash_part
add
    partition range_03 values less than (to_timestamp('2000-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))
    (
       subpartition range_03_list_01 values(13,14,15,16,17),
       subpartition range_03_list_02 values(18,19,20,21,22),
       subpartition range_03_list_03 values(default)
    )
错误报告:
SQL 错误: ORA-14074: 分区界限必须调整为高于最后一个分区界限
14074. 00000 -  "partition bound must collate higher than that of the last partition"
*Cause:    Partition bound specified in ALTER TABLE ADD PARTITION
           statement did not collate higher than that of the table's
           last partition, which is illegal.
*Action:   Ensure that the partition bound of the partition to be added
           collates higher than that of the table's last partition.

如下，我往散列分区中添加了一个新的分区，则没有上面的约束，因为散列分区没有最大值可言。

alter table hash_part
add partition hash_05;

第二，删除分区

SQL>alter table list_part drop partition list_next;

如果表中只有一个分区的话，则不能删除，只能删除表。否则会报错。如下：

alter table list_part drop partition list_01
错误报告:
SQL 错误: ORA-14083: 无法删除分区表的唯一分区
14083. 00000 -  "cannot drop the only partition of a partitioned table"
*Cause:    A drop partition command is being executed when there
           is only one partition in the table
*Action:   Ensure that there is at least one partition.
           Drop table to remove all partitions

第三、截断分区

截断子分区：

SQL>alter table range_hash_part truncate subpartition range_01_list_03;

截断分区：

SQL>alter table list_part truncate partition list_01;

第四、合并分区

合并分区的时候，合并的分区必须是相邻的，并且结果分区将采用较高的分区，不能合并到界限较低的分区。如下面：

alter table range_part 
merge partitions part_1998,part_next into partition part_next;
错误报告:
SQL 错误: ORA-14274: 要合并的分区不相邻
14274. 00000 -  "partitions being merged are not adjacent"

alter table range_part 
merge partitions part_1998,part_1999 into partition part_1998
错误报告:
SQL 错误: ORA-14275: 不能将下界分区作为结果分区重用
14275. 00000 -  "cannot reuse lower-bound partition as resulting partition"

不过在合并子分区的时候，不相邻的也能合并。

alter table range_hash_part  
merge subpartitions range_01_list_01 ,range_01_list_03  into subpartition range_01_list_03 ;

另外，不能对散列分区表进行合并操作。

SQL> ALTER TABLE hash_part
  2  merge partitions hash_01,hash_02 into partition hash_02;
ALTER TABLE hash_part
            *
ERROR at line 1:
ORA-14255: table is not partitioned by Range, List, Composite Range or
Composite List method

第五、拆分分区

拆分分区是跟合并分区相反的操作，因此也不适合对HASH分区使用，对于HASH 分区添加一个分区即可。通常我们会用来拆分MAXVALUE/DEFAULT分区。下面例子中，符合要求的分区数据会被分割到第一个分区，其他的被分在另外的一个分区。

SQL> alter table range_hash_part
  2  split subpartition range_02_list_02 values (18,19) into
  3  (subpartition range_02_list_05 ,
  4   subpartition range_02_list_04  );

Table altered.

第六、移动分区，可以移动分区的数据到别的表空间中

SQL>Alter table tab move partition p_tab tablespace tbl update indexes;
-----加上update indexes可以避免数据的移动使得索引无效

第七、修改分区

修改LIST 分区：

SQL> alter table list_part
  2  modify partition list_01  add values(18,19);---添加分区值

Table altered.

SQL> alter table list_part
  2  modify partition list_01 drop values(18,19);---删除分区值

Table altered.

第八、交换分区

SQL>alter table range_hash_part exchange subpartition range_01_list_03 with table tab;

交换分区的时候，需要注意的是交换的表必须不是分区表、聚簇表。否则会报错。下面的SALES是分区表，然后就报错了。

SQL> alter table range_hash_part exchange subpartition range_01_list_03 with table sales;
alter table range_hash_part exchange subpartition range_01_list_03 with table sales
                                                                              *
ERROR at line 1:
ORA-14276: EXCHANGE SUBPARTITION requires a non-partitioned, non-clustered
table

另外，交换分区的两个表的结构要一样，否则会报错。

SQL> alter table range_hash_part exchange subpartition range_01_list_03 with table tab;
alter table range_hash_part exchange subpartition range_01_list_03 with table tab
                                                                              *
ERROR at line 1:
ORA-14277: tables in EXCHANGE SUBPARTITION must have the same number of columns

还有就是，交换分区的表的数据要符合分区表的约束要求，否则也会报错。

SQL> alter table range_hash_part exchange subpartition range_01_list_03 with table tab;
alter table range_hash_part exchange subpartition range_01_list_03 with table tab
                                                                              *
ERROR at line 1:
ORA-14280: all rows in table do not qualify for specified subpartition

第九、接合分区，也就是散列分区的合并

当散列分区表中中某个分区的数据量比较大的时候，可以多添加几个散列分区，然后采用下面的结合语法，对所有的散列分区数据进行平衡。

SQL> ALTER TABLE hash_part coalesce PARTITION;

Table altered.

三、创建分区索引

分区索引有三种，一种是普通的索引，索引的范围是所有分区；一种是本地分区索引，索引的范围是单独的分区；还有一种是全局分区索引，索引的范围是指定的分区。具体的区别可以查看下面的图片。

1、创建普通索引

SQL> create index part_ind on range_hash_part (prod_id);

Index created.

2、创建本地分区索引

SQL> create index part_ind on range_hash_part (prod_id) local ;

Index created.

创建本地分区索引的时候，针对的是所有的分区，不能单独指定分区，否则会报语法错误。如下：

SQL> create index part_ind on range_part(time_id)
  2  local
  3  (
  4     partition part_1998 ,
  5     partition part_2000
  6  );
create index part_ind on range_part(time_id)
                         *
ERROR at line 1:
ORA-14024: number of partitions of LOCAL index must equal that of the
underlying table

3、创建全局分区索引

SQL> create index part_ind on range_part(time_id)
  2  global partition by range(time_id)
  3  (
  4     partition part_1998 values less than (to_timestamp('1999-01-01 00:00:00','YYYY-MM-DD HH24:Mi:SS')) ,
  5     partition part_2000 values less than (to_timestamp('2001-01-01 00:00:00','YYYY-MM-DD HH24:Mi:SS')),
  6     partition part_next values less than (maxvalue)
  7  );

Index created.

需要注意的是，对于范围分区索引来说，创建全局分区索引必须要带有maxvalue的分区，否则会报下面的错误：

SQL> create index part_ind on range_part(time_id)
  2  global partition by range(time_id)
  3  (
  4     partition part_1998 values less than (to_timestamp('1999-01-01 00:00:00','YYYY-MM-DD HH24:Mi:SS')) ,
  5     partition part_2000 values less than (to_timestamp('2001-01-01 00:00:00','YYYY-MM-DD HH24:Mi:SS'))
  6  );
)
*
ERROR at line 6:
ORA-14021: MAXVALUE must be specified for all columns

还有就是，全局分区索引只能是prefix index。所谓prefix index也就是第一个index key必须是partition key为开头的第一列。假设索引列为（prod_id,time_id）,而分区是依据time_id分区的，则创建（prod_id,time_id）的索引列是错误的，必须是time_id开头，也就是（time_id，prod_id）或者直接创建索引(time_id),其他的如（prod_id）都会报错。如下：

SQL> create index part_ind on range_part(QUANTITY_SOLD)
  2  global partition by range(time_id)
  3  (
  4     partition part_1998 values less than (to_timestamp('1999-01-01 00:00:00','YYYY-MM-DD HH24:Mi:SS')),
  5     partition part_2000 values less than (to_timestamp('2001-01-01 00:00:00','YYYY-MM-DD HH24:Mi:SS'))
  6  );
global partition by range(time_id)
                                 *
ERROR at line 2:
ORA-14038: GLOBAL partitioned index must be prefixed

不过，本地分区索引则无所谓，prefixed index和non-prefixed index都支持。

四、查看分区信息

1、查看一个用户有多少分区表，以及判断一个表是不是分区表：

SQL>select * from user_tables  where partitioned='YES'

2、查看一个用户拥有的分区表SUMMARY信息：

SQL>select * from USER_PART_TABLES WHERE table_name ='RANGE_HASH_PART';

3、查看一个表有多少分区，以及显示分区相关的统计信息：

SQL>select * from user_tab_partitions where table_name='RANGE_HASH_PART';

4、查看一个表有多少子分区，以及显示子分区相关的统计信息：

SQL>select * from user_tab_subpartitions where table_name='RANGE_HASH_PART';

5、查看一个表的分区列是什么：

SQL>select * from USER_PART_KEY_COLUMNS WHERE NAME ='RANGE_HASH_PART';

6、查看一个表的子分区列是什么：

SQL>select * from user_SUBPART_KEY_COLUMNS WHERE NAME ='RANGE_HASH_PART';