oracle分区表介绍(含本地分区索引和全局分区索引介绍)

标签: # oracle admin

通过分区,您可以将非常大的表和索引分解为更小的,更易于管理的部分,称为分区。每个分区都是一个独立的对象,具有自己的名称和可选的自身存储特性。

分区表的几个好处:
1.增加可用性

分区的不可用并不意味着整个表不可用。查询优化器会自动从查询计划中删除不可用的分区,因此当分区不可用时,查询不会受到影响

2.简化表管理

分区是既可以集体管理,也可以单独管理的片段。 DDL语句可以直接操纵分区,而不仅仅是整个表或索引。因此,您可以分解资源密集型的任务,例如重建索引或表。例如,可以一次移动一个表分区。如果发生问题,则仅重做分区移动,而不是表移动。同样,删除分区避免了执行大量的DELETE语句。

3.减少OLTP共享资源争用

在某些OLTP系统中,分区可以减少共享资源的争用。例如,DML分布在许多段而不是一个段上。

4.增强数的查询性能
分区键:

分区键是一个或多个列,这些列确定分区表中每一行应进入的分区。每行都明确分配给一个分区。

oracle 提供了几种分区策略,范围分区,列表分区,哈希分区

单级分区是一种单一的数据分区的方法。下面分别介绍一下

范围分区:

比如有如下数据集

PROD_ID CUST_ID TIME_ID CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD


  116      11393 05-JUN-99          2        999             1       12.18
   40     100530 30-NOV-98          9         33             1       44.99
  118        133 06-JUN-01          2        999             1       17.12
  133       9450 01-DEC-00          2        999             1       31.28
   36       4523 27-JAN-99          3        999             1       53.89
  125       9417 04-FEB-98          3        999             1       16.86
   30        170 23-FEB-01          2        999             1         8.8
   24      11899 26-JUN-99          4        999             1       43.04
   35       2606 17-FEB-00          3        999             1       54.94
   45       9491 28-AUG-98          4        350             1       47.45 
根据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)
 ); 

分区后,每个分区的数据如下:
在这里插入图片描述

列表分区:

在列表分区中,数据库使用离散值列表作为每个分区的分区键。您可以使用列表分区来控制各个行如何映射到特定分区。

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)
 ); 

如下:channel_id为2和4的在一个分区,3和9的在另外一个分区

在这里插入图片描述

哈希分区:

数据在插入时,会均匀的分布在每个分区,每个分区的数据量基本相同

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 2; 

在这里插入图片描述

分区表由一个或多个分区组成,这些分区被单独管理并且可以独立于其他分区进行操作。表是分区的或未分区的。即使一个分区表仅由一个分区组成,该表也不同于未分区表,未分区的表无法添加分区。
“分区特征”给出了分区表的示例。

分区索引:
分区索引类似于分区表,已被分解为更小且更易于管理的部分。全局索引和表是相对独立的,而局部索引自动链接到表的分区。与分区表一样,分区索引提高了可管理性,可用性,性能和可伸缩性。

下图展示了分区索引选项:

在这里插入图片描述

本地分区索引
在本地分区索引中,分区索引与表相同的列,相同数量的分区和相同的分区边界。每个索引分区仅与表的一个分区相关联,因此索引分区中的所有键仅引用存储在单个表分区中的行。通过这种方式,数据库自动将索引分区与其关联的表分区同步,从而使每个表-索引对独立。

例子:
CREATE INDEX hash_sales_idx ON hash_sales(time_id) LOCAL;
hash_products表具有两个分区,因此hash_sales_idx也具有两个分区。每个索引分区都与一个不同的表分区关联。
索引分区SYS_P38对表分区SYS_P33中的行有效,而索引分区SYS_P39对表分区SYS_P34中的行有效。

在这里插入图片描述
您不能将分区显式的添加到本地索引。当添加新的分区时,新分区才会添加本地索引。同样,不能从本地索引显式的删除分区。只有从基础表中删除分区时,才会删除本地索引分区。

全局分区索引

全局分区索引是B树索引,该索引独立于表而创建的。单个索引分区可以指向所有表分区,而在本地分区的索引中,索引分区和表分区之间存在一对一的奇偶校验。

创建全局分区索引:
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));

如上创建的全局分区索引,索引分区p1指向channel_id为2的行,索引分区p2指向channel_id为3的行,索引分区p3指向channel_id为4或9的行。
如下图:

在这里插入图片描述

版权声明:本文为dazuiba008原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/dazuiba008/article/details/103472594