Oracle数据库分区表概述

分区介绍

分区能够将非常大的表和索引分解成更小、更易于管理的多个分区对象。
每个分区是一个独立的对象，具有其自己的名称和（可选的）的存储特征。
从应用程序的角度来看，只有一个模式对象已存在。DML 语句不需要作任何修改就可以访问该分区表。
分区对许多不同类型的应用程序都很有用，特别是那些管理大量数据的应用程序。

分区优点

可用性增强：其中某个分区不可用并不意味着整个对象不可用。当部分分区不可用时，查询优化器自动从查询计划中删除未引用的分区，而查询不会受影响。

更轻松的管理模式对象：已分区对象具有多个分片，可以将其作为一个整体来管理，也可以单独管理各个分片。DDL 语句可以处理分区，而不是整个表或索引。因此，可以分解大量占用资源的任务，如重建索引或表。例如，可以一次只移动一个表分区。如果发生了问题，则只需重新移动该分区，而不是整个表。而且，删除一个分区可以避免执行许多 DELETE 语句。

在 OLTP 系统中减少对共享资源的争用：在一些 OLTP 系统中，分区可以减少对共享资源的争用。例如，DML 被分散到很多段，而不只是一个段。

在数据仓库中，分区可以加快处理即席查询：例如，包含一百万行的销售表可以按季度进行分区。

分区特征

每个表或索引的分区必须具有相同的逻辑属性，如列名称、数据类型、和约束。例如，在一个表中的所有分区都共享相同的列和约束定义，并在索引中的所有分区都共享相同的索引列。但是，每个分区可以有单独的物理属性，如其所属的表空间。

分区键

分区键是一个列或列集，以确定分区表中的每一行应该所在的分区。每个行会被确定地（而不是模棱两可地）分配到某个分区。

在 sales 表中，可以指定 time_id 列作为范围分区的键。基于此列中的日期是否属于某一特定范围，数据库将行分配到所属分区。通过使用分区键，Oracle 数据库将插入、更新、和删除操作自动地指向适当的分区。

分区策略

Oracle 分区提供了几个分区策略，来控制数据库如何将数据放置到分区。基本策略有范围分区、列表分区、和哈希分区等。

单一分区策略只使用一种数据分布方法，例如，仅使用列表分区，或仅使用范围分区。
复合分区中，表先按一种数据分布方法分区，然后每个分区使用第二种数据分布方法进一步分成子分区。例如，您可以使用 channel_id划分列表分区，并使用 time_id 划分范围子分区。

范围分区

在范围分区中，数据库基于分区键的值范围将行映射到各个分区。范围分区是最常见的分区类型，通常与日期一起使用。

CREATE TABLE time_range_sales
 ( prod_id NUMBER(6)
 , cust_id NUMBER
 , time_id DATE
 , channel_id CHAR(1)
 , promo_id NUMBER(6)
 , quantity_sold NUMBER(3)
 , amount_sold NUMBER(10,2)
 )
PARTITION BY RANGE (time_id)
(PARTITION SALES_1998 VALUES LESS THAN 
(TO_DATE('01-JAN-1999','DD-MON-YYYY')),
 PARTITION SALES_1999 VALUES LESS THAN 
(TO_DATE('01-JAN-2000','DD-MON-YYYY')),
 PARTITION SALES_2000 VALUES LESS THAN 
(TO_DATE('01-JAN-2001','DD-MON-YYYY')),
 PARTITION SALES_2001 VALUES LESS THAN (MAXVALUE)
);
//PARTITION BY RANGE表示表按照范围分区，其中分区键为time_id列

列表分区

在列表分区中，数据库使用一些具体值的列表作为每个分区的分区键。
可以使用列表分区来控制单个行如何映射到特定的分区。
当用来区分数据集的键不方便排序时，可以通过使用列表来分组和组织相关的数据集。

CREATE TABLE list_sales
 ( prod_id NUMBER(6)
 , cust_id NUMBER
 , time_id DATE
 , channel_id CHAR(1)
 , promo_id NUMBER(6)
 , quantity_sold NUMBER(3)
 , amount_sold NUMBER(10,2)
 )
PARTITION BY LIST (channel_id)
(PARTITION even_channels VALUES (2,4),
 PARTITION odd_channels VALUES (3,9)
);
//PARTITION BY LIST表示表按照列表方式分区，分区键为channel_id列

哈希分区

在哈希分区中，基于用户所指定的将在分区键上应用的哈希算法，数据库将行映射到各个分区。
行的目标分区是由数据库应用于行的内部哈希函数所决定的。
哈希算法被设计为可以跨设备均匀分布行，以使每个分区包含大致相同的行数。
当插入行时，数据库会尝试随机、均匀地将它们分布在各个分区之间。
不能指定某行被放置在哪一个分区。数据库应用哈希函数，其结果确定由哪个分区包含此行。
如果重新更改了分区数目，那么数据库会在所有分区上重新分布数据。
哈希分区可用于划分大表，以提高可管理性。只需管理几个较小的表片断，而不是管理一个大型表。一个哈希分区的缺失并不影响其余分区，并且可以独立地恢复。在更新争用较高的 OLTP 系统中，哈希分区也是非常有用的。例如，一个段被分为几个片断，每个片断都可以被更新，而不像单个段那样会遭受争用。

CREATE TABLE hash_sales
 ( prod_id NUMBER(6)
 , cust_id NUMBER
 , time_id DATE
 , channel_id CHAR(1)
 , promo_id NUMBER(6)
 , quantity_sold NUMBER(3)
 , amount_sold NUMBER(10,2)
 )
PARTITION BY HASH (prod_id)
PARTITIONS 4;
//PARTITION BY HASH表示表按照hash方式分区，分区键为prod_id列。
//PARTITIONS 4表示分区个数为4

间隔分区（interval分区）

间隔分区是Oracle 11.1引入的新功能，通过该功能，可以在输入相应分区的数据时自动创建相应的分区。在没有间隔分区技术之前，DBA通常会创建一个maxvalue分区以避免ORA-14400：插入的分区键值不能映射到任何分区（"inserted partition key does not map to any partition") 错误。

间隔分区只能指定一个分区键列，并且数据类型必须为NUMBER或者DATE类型。
在创建自动间隔分区的时候，最为核心的就是”INTERVAL“关键字了。对于按照时间进行自动分区，INTERVAL后面可以跟随NUMTOYMINTERVAL和NUMTODSINTERVAL

NUMTOYMINTERVAL(x,c)
用法：x是一个数据，c是一个字符串，该函数是将x转为interval year to month类型。常用单位有：”year“、”month“。
NUMTODSINTERVAL(x,c)
用法：x是一个数据，c是一个字符串，该函数是将x转为interval day to second类型。常用单位有：”day“、”hour“、”minute“、”second“。

作为范围分区（range partition）的扩展，间隔分区命令数据库在插入表中的数据超过所有现有范围分区时自动创建指定间隔的分区。DBA必须至少指定一个范围分区的较高值，称为过渡点，数据库会自动的为超出该过渡点的数据创建间隔分区，每个间隔分区的下边界是先前范围或间隔分区的上边界。

数据库创建的间隔分区的名称是系统自动生成的，可以通过dba_tab_partition视图查看。目前无法为分区指定创建模板，但是可以重命名分区。

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：指定分区存储的表空间

案例1、创建按年分区表
CREATE TABLE interval_year_table01
(
  employee_id         NUMBER,
  employee_name       VARCHAR2(20),
  birthday            DATE    
)
PARTITION BY RANGE(birthday)
INTERVAL (NUMTOYMINTERVAL(1,'year')) STORE IN (tbs01,tbs02,tbs03)
(
  PARTITION partition2014 VALUES LESS THAN(to_date('2015-01-01:00:00:00','yyyy-mm-dd hh24:mi:ss')),
  PARTITION partition2015 VALUES LESS THAN(to_date('2016-01-01:00:00:00','yyyy-mm-dd hh24:mi:ss'))
);

案例2、创建按月分区表
CREATE TABLE interval_month_table01
(
  employee_id         NUMBER,
  employee_name       VARCHAR2(20),
  birthday            DATE    
)
PARTITION BY RANGE(birthday)
INTERVAL (NUMTOYMINTERVAL(1,'month')) STORE IN (tbs01,tbs02,tbs03)
(
  PARTITION partition201401 VALUES LESS THAN(to_date('2014-02-01:00:00:00','yyyy-mm-dd hh24:mi:ss'))
);

案例3、按天（日）创建分区
CREATE TABLE interval_day_table01
(
  employee_id         NUMBER,
  employee_name       VARCHAR2(20),
  birthday            DATE    
)
PARTITION BY RANGE(birthday)
INTERVAL (NUMTODSINTERVAL(1,'day')) STORE IN (tbs01,tbs02,tbs03)
(
  PARTITION partition20140101 VALUES LESS THAN(to_date('2014-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss'))
);

可以继续按周，按小时来间隔分区
INTERVAL (NUMTODSINTERVAL(7,'day')) 
INTERVAL (NUMTODSINTERVAL(1,'hour'))

案例4、按数字进行分区相对简单，这里相近的10个数字进入同一个分区
CREATE TABLE interval_number_table01
(
  employee_id         NUMBER,
  employee_name       VARCHAR2(20),
  birthday            DATE    
)
PARTITION BY RANGE(employee_id)
INTERVAL (10) STORE IN (tbs01,tbs02,tbs03)
(
  PARTITION partition10 VALUES LESS THAN(10)
);

备注：
DBMS_METADATA.GET_DDL查询表的定义元数据时，只提供用户手段创建的分区，而不提供系统自动生成的分区。
如果要输出系统自动创建的分区的脚本，需将DBMS_METDATA的EXPORT参数设置为true
exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'EXPORT',true);

分区表

分区表包含一个或多个分区，它们可以单独进行管理，并且可以独立于其他分区进行操作。表要么是分区表，要么是未分区表。
即使分区表只包含一个分区，此表也是与一个未分区表不同的，不能将分区加入到未分区表中。

分区表由一个或多个表分区段组成。如果创建了一个名为 hash_products 的分区表，不会单独为表分配一个段，而是将每个表分区的数据存储在其自己的分区段中。每个表分区段包含表数据的一部分。

堆组织表中的某些分区或所有分区可以存储为压缩格式。压缩可以节省空间，并可以加快执行查询的速度。因此，压缩可用于在插入和更新操作很少的数据仓库环境中，也可以用于 OLTP 环境中。
可以为表空间、表、或表分区声明表压缩属性。如果在表空间级别声明，则默认情况下在该表空间中创建的表是被压缩的。可以更改一个表的压缩属性，在这种情况下，此更改仅适用于插入到该表的新数据。因此，一个单表或分区可能包含压缩和未压缩的块，它保证数据的大小不会因为压缩反而增大。如果压缩会增加一个数据块的大小，那么数据库就不压缩该块。

分区索引

与分区表类似，分区索引被分解成更小、更易于管理的多个索引分区对象。
全局索引独立于它们依赖的表进行分区。
局部索引则依据不同的表分区方法，自动链接到相应的表分区。
与分区表类似，分区索引提高了可管理性、可用性、性能、和可扩展性。
下图显示索引的分区选项：

局部分区索引

在局部分区索引中，索引基于表上相同的列来分区，与表分区具有相同分区数目和相同的分区边界。每个索引分区仅与底层表的一个分区相关联，所以一个索引分区中的所有键都只引用存储在某个单一表分区中的行。通过这种方式，数据库会自动同步索引分区及其关联的表分区，使每个表---索引对相持独立。

局部分区索引在数据仓库环境中很常见。局部索引提供了以下优点：

1、因为使分区中的数据无效或不可用的操作只会影响当前分区，这有助于提高可用性。
2、简化了分区维护。当移动一个表分区，或某个分区的数据老化时，只须重建或维持相关联的局部索引分区。而在全局索引中所有索引分区必须被全部重建或维护。
3、如果分区发生时间点恢复，则可以将局部索引恢复到恢复时间。而不需要重建整个索引。

示例：
CREATE INDEX hash_sales_idx ON hash_sales(time_id) LOCAL;
//LOCAL表示局部分区索引

hash_products 表中有两个分区，因此 hash_sales_idx 也有两个分区。每个索引分区与一个不同的表分区相关联。索引分区 SYS_P38对表分区 SYS_P33 中的行建立索引，而索引分区 SYS_P39 对表分区SYS_P34 中的行建立索引。如图：

不能明确将一个分区添加到一个局部索引中。相反，仅当将一个表分区添加到基础表时，新的索引分区会被自动添加到局部索引。
同样，不能明确从局部索引删除一个分区。相反，仅当你从基础表中删除一个表分区时，相应的局部索引分区会被自动删除。

与其他索引一样，可以在分区表上创建位图索引。唯一的限制是位图索引必须是局部分区索引 — — 而不能是全局分区索引。全局位图索引只支持非分区表。

局部前缀索引和局部非前缀索引

局部分区索引可分为以下类别：
局部前缀索引
在这种情况下，分区键处于索引定义的前导部分。

局部非前缀索引
在这种情况下，分区键不是索引列列表的前导部分，甚至根本不必在该列表中。

这两种类型的索引都可以充分利用分区消除（也称为分区剪除），此时，优化程序将不考虑无关分区，以加快数据访问速度。
查询是否可以消除分区取决于查询谓词。使用局部前缀索引的查询始终允许索引分区消除，而使用一个局部非前缀索引的查询可能不会。

局部分区索引存储

与表分区类似，局部索引分区被存储在其自己的段中。每个段包含整个索引数据的一部分。因此，由四个分区组成的局部索引，不是存储在一个单一索引段中，而是在四个单独的段中。

全局分区索引

全局分区索引是一个 B-树索引，其分区独立于所依赖的基础表。
某个索引分区可以指向任意或所有的表分区，而在一个局部分区索引中，索引分区与分区表之间却存在一对一的配对关系。

通常，对于强调快速访问、数据完整性、和可用性的 OLTP 应用程序来说，全局索引很有用。在一个 OLTP 系统中，表可能会基于某个键（如employees.department_id 列）来分区，但应用程序可能需要基于许多不同的键（如 employee_id 或 job_id）来访问数据。全局索引在这种情况下可能很有用。

可以按范围或哈希方式建立全局分区索引。如果按范围分区，则数据库按你指定的表列的值范围对全局索引进行分区。如果按哈希分区，则数据库对分区键列中的值使用哈希函数，并将行分配到相应的分区。

全局分区索引示例：
CREATE INDEX time_channel_sales_idx ON 
time_range_sales (channel_id)
 GLOBAL PARTITION BY RANGE (channel_id)
 (PARTITION p1 VALUES LESS THAN (3),
 PARTITION p2 VALUES LESS THAN (4),
 PARTITION p3 VALUES LESS THAN (MAXVALUE));
//GLOBAL表示全局索引
//PARTITION BY RANGE表示索引列按照范围分区方式

如图，全局索引分区可以包含指向多个表分区的条目。索引分区 p1 指向 channel_id 为 2 的行，索引分区 p2 指向 channel_id 为 3 的行，而索引分区 p3 指向 channel_id 为 4 或 9 的行。

分区索引组织表

可以对一个索引组织表（IOT) 按范围、列表、或哈希值进行分区。分区有助于改进 IOT 的可管理性、可用性、和性能。另外，使用 IOT 的数据模块可以利用此功能，对其存储的数据进行分区。
请注意分区 IOT 的以下特征：

分区列必须是主键列的子集。
可以对辅助索引进行局部或全局分区。
溢出数据段总是和表分区一样具有相应的对等分区。

Oracle 数据库支持在分区和未分区索引组织表上的位图索引。在索引组织表上创建位图索引需要一个映射表。

posted @ 2026-04-24 10:05 数据库路上阅读(13) 评论(0) 收藏举报

刷新页面返回顶部

Oracle数据库分区表概述