Maîtrise complète du partitionnement Oracle

Cet article vous apporte des connaissances pertinentes sur Oracle, qui présente principalement les problèmes liés au partitionnement. Grâce à la fonction de partitionnement, les tables, les index et les tables organisées en index peuvent être subdivisés en segments, afin qu'ils puissent être gérés et consultés avec plus de précision. ces objets de base de données, j'espère que cela sera utile à tout le monde.

Tutoriel recommandé : "Tutoriel Oracle"

1. Introduction :

La fonction de partitionnement Oracle peut améliorer la gérabilité, les performances et la disponibilité de nombreuses applications. Le partitionnement permet aux tables, index et tables organisées en index d'être subdivisés en segments, permettant une gestion et un accès plus précis à ces objets de base de données. Oracle propose une grande variété de schémas de partitionnement pour répondre à tous les besoins de l'entreprise. Et comme le partitionnement est totalement transparent dans les instructions SQL, cette fonctionnalité peut être appliquée à presque toutes les applications.

Avantages de la fonction de partitionnement : la fonction de partitionnement apporte de grands avantages à diverses applications en améliorant la gérabilité, les performances et la disponibilité. De manière générale, le partitionnement peut grandement améliorer les performances de certaines requêtes et opérations de maintenance. De plus, le partitionnement peut grandement simplifier les tâches de gestion courantes. Grâce au partitionnement, les concepteurs et les administrateurs de bases de données peuvent résoudre certains des défis posés par les applications de pointe. Le partitionnement est un outil clé dans la création de systèmes de données de plusieurs gigaoctets ou de systèmes à ultra haute disponibilité.

2. Connaissance de base de la fonction de partition :

La fonction de partition peut subdiviser davantage les tables, les index ou les tables organisées en index en segments. Les segments de ces objets de base de données sont appelés partitions. Chaque partition possède son propre nom et peut sélectionner ses propres caractéristiques de stockage. Du point de vue d'un administrateur de base de données, un objet partitionné comporte plusieurs segments, qui peuvent être gérés collectivement ou individuellement. Cela donne à l'administrateur de base de données une flexibilité considérable dans la gestion des objets partitionnés. Cependant, du point de vue de l'application, une table partitionnée est identique à une table non partitionnée et aucune modification n'est requise lors de l'accès à une table partitionnée à l'aide de commandes SQL DML. Le partitionnement des tables est réalisé grâce à des « clés de partition », qui font référence aux colonnes qui déterminent la partition dans laquelle se trouve une ligne. Oracle Database 10g propose plusieurs techniques de partitionnement des tables :

1. Partitionnement par plage (plage) : Chaque partition est spécifiée par une plage de valeurs de clé de partition (pour une table avec une colonne de date comme clé de partition, "2005 Le "Janvier 2005" La partition contient des lignes avec des valeurs de clé de partition allant du « 1er janvier 2005 » au « 31 janvier 2005 »).

Lorsque vous utilisez le partitionnement par plage, veuillez tenir compte des règles suivantes :

1) Chaque partition doit avoir une clause VALUES LESS THEN, qui spécifie une valeur limite supérieure qui n'est pas incluse dans la partition. Tous les enregistrements dont la valeur de clé de partition est égale ou supérieure à cette limite supérieure seront ajoutés à la partition immédiatement supérieure.

2) Toutes les partitions, sauf la première, auront une valeur limite inférieure implicite, qui est la valeur limite supérieure de la partition précédente de cette partition.

3) Dans la partition la plus élevée, MAXVALUE est définie. MAXVALUE représente une valeur incertaine. Cette valeur est supérieure à la valeur de n'importe quelle clé de partition dans d'autres partitions et peut également être comprise comme supérieure à la valeur de VALUE LESS THEN spécifiée dans n'importe quelle partition, y compris les valeurs nulles.

Exemple 1 : 

Supposons qu'il existe une table CUSTOMER avec 200 000 lignes de données. Nous partitionnons cette table par CUSTOMER_ID. Chaque partition stocke 100 000 lignes. peut s'étendre sur plusieurs disques physiques. Voici le code pour créer des tables et des partitions, comme suit :

CREATE TABLE  CUSTOMER
(
CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,
FIRST_NAME  VARCHAR2(30) NOT NULL,
LAST_NAME   VARCHAR2(30) NOT NULL,
PHONE        VARCHAR2(15) NOT NULL,
EMAIL        VARCHAR2(80),
STATUS       CHAR(1)
)
PARTITION BY RANGE (CUSTOMER_ID)
(
PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01,
PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02
)

Exemple 2 : Divisé par le temps

CREATE TABLE ORDER_ACTIVITIES
(
ORDER_ID      NUMBER(7) NOT NULL,
ORDER_DATE    DATE,
TOTAL_AMOUNT NUMBER,
CUSTOTMER_ID NUMBER(7),
PAID           CHAR(1)
)
PARTITION BY RANGE (ORDER_DATE)
(
PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('01- MAY -2003','DD-MON-YYYY')) TABLESPACEORD_TS01,
PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) TABLESPACE ORD_TS02,
PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY')) TABLESPACE ORD_TS03
)

Exemple 3 : MAXVALUE

CREATE TABLE RangeTable
(
idd   INT PRIMARY KEY ,
iNAME VARCHAR(10),
grade INT
)
PARTITION  BY  RANGE (grade)
(
PARTITION  part1 VALUES  LESS  THEN (1000) TABLESPACE  Part1_tb,
PARTITION  part2 VALUES  LESS  THEN (MAXVALUE) TABLESPACE  Part2_tb
);

2. list (Pour une table avec une colonne région comme clé de partition, la partition « Amérique du Nord » ​​peut contenir les valeurs « Canada », « États-Unis » et « Mexique »). La caractéristique de cette partition est qu'il n'y a que quelques valeurs dans une certaine colonne. Sur la base de cette caractéristique, nous pouvons utiliser le partitionnement de liste.

Exemple 1

CREATE TABLE PROBLEM_TICKETS
(
PROBLEM_ID   NUMBER(7) NOT NULL PRIMARY KEY,
DESCRIPTION  VARCHAR2(2000),
CUSTOMER_ID  NUMBER(7) NOT NULL,
DATE_ENTERED DATE NOT NULL,
STATUS       VARCHAR2(20)
)
PARTITION BY LIST (STATUS)
(
PARTITION PROB_ACTIVE   VALUES ('ACTIVE') TABLESPACE PROB_TS01,
PARTITION PROB_INACTIVE VALUES ('INACTIVE') TABLESPACE PROB_TS02

Exemple 2

CREATE  TABLE  ListTable
(
id    INT  PRIMARY  KEY ,
name  VARCHAR (20),
area  VARCHAR (10)
)
PARTITION  BY  LIST (area)
(
PARTITION  part1 VALUES ('guangdong','beijing') TABLESPACE  Part1_tb,
PARTITION  part2 VALUES ('shanghai','nanjing')  TABLESPACE  Part2_tb
);
)

3. Partition de hachage (hachage) :

Utilisez l'algorithme de hachage pour la clé de partition pour déterminer la partition où se trouve la ligne spécifiée. Ce type de partitionnement utilise un algorithme de hachage sur les valeurs des colonnes pour déterminer dans quelle partition une ligne doit être placée. Le partitionnement par hachage est recommandé lorsque les valeurs des colonnes ne présentent pas de conditions appropriées. Le partitionnement par hachage est un type de partitionnement qui distribue uniformément les données en spécifiant des numéros de partition, car le partitionnement par hachage est effectué sur le périphérique d'E/S afin que les partitions soient de la même taille.

Exemple 1 :

CREATE TABLE HASH_TABLE
(
COL NUMBER(8),
INF VARCHAR2(100)
)
PARTITION BY HASH (COL)
(
PARTITION PART01 TABLESPACE HASH_TS01,
PARTITION PART02 TABLESPACE HASH_TS02,
PARTITION PART03 TABLESPACE HASH_TS03
)

Abréviation :

CREATE TABLE emp
(
empno NUMBER (4),
ename VARCHAR2 (30),
sal   NUMBER
)
PARTITION BY  HASH (empno) PARTITIONS 8
STORE IN (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);

Le mécanisme principal de la partition de hachage est de calculer dans quelle partition un enregistrement spécifique doit être inséré selon l'algorithme de hachage. La chose la plus importante dans l'algorithme de hachage est la fonction de hachage. Dans Oracle, si vous souhaitez utiliser le partitionnement par hachage, spécifiez simplement le nombre de partitions. Il est recommandé que le nombre de partitions soit de 2 à la puissance n, ce qui peut rendre la répartition des données entre chaque partition plus uniforme.

4. Partition de hachage de plage combinée (range-hash) :

范围和散列分区技术的组合，通过该组合，首先对表进行范围分区，然后针对每个单独的范围分区再使用散列分区技术进一步细分。索引组织表只能进行范围分区。

这种分区是基于范围分区和列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区。

CREATE TABLE SALES
(
PRODUCT_ID VARCHAR2(5),
SALES_DATE DATE,
SALES_COST NUMBER(10),
STATUS VARCHAR2(20)
)
PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)
(
PARTITION P1 VALUES LESS THAN(TO_DATE('2003-01-01','YYYY-MM-DD'))TABLESPACE rptfact2009
(
SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,
SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009
),
PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE rptfact2009
(
SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,
SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009
)
)

5、组合范围列表分区(range-list)：

范围和列表分区技术的组合，通过该组合，首先对表进行范围分区，然后针对每个单独的范围分区再使用列表分区技术进一步细分。索引组织表可以按范围、列表或散列进行分区。

这种分区是基于范围分区和散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区。

create table dinya_test
(
transaction_id number primary key,
item_id number(8) not null,
item_description varchar2(300),
transaction_date date
)
partition by range(transaction_date)subpartition by hash(transaction_id)  subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03)
(
partition part_01 values less than(to_date(‘2006-01-01’,’yyyy-mm-dd’)),
partition part_02 values less than(to_date(‘2010-01-01’,’yyyy-mm-dd’)),
partition part_03 values less than(maxvalue)
);

三、Oracle 数据库 10g 还提供了三种类型的分区索引：

1、本地索引：

本地索引是其分区方式与其所在基础表的分区方式一模一样的索引。本地索引的每个分区仅对应于其所在基础表的一个分区。

2、全局分区索引：

全局分区索引是使用不同于其所在表的分区键进行分区的索引，其所在表可以是分区表或非分区表。全局分区的索引可以使用范围或散列分区进行分区。例如，某个表可以按月份进行范围分区，因此具有十二个分区，而该表上的索引则可以使用不同的分区键进行范围分区，从而具有不同的分区数量。

3、全局非分区索引：

全局非分区索引基本上和非分区表的索引一样。索引结构是不分区的。

四、Oracle 提供了一系列丰富的技术，

可用于对表、索引和索引组织表进行分区，

因此可以针对任何业务环境中的任何应用程序进行最佳的分区 Oracle 还提供一套完整的 SQL 命令，用于管理分区表。其中包括添加新分区、删除分区、分拆分区以及合并分区的命令。

分区的优点：

1、用分区功能提高可管理性

通过 Oracle 分区功能，可将表和索引分成更多、更小的可管理单元，从而使数据库管理员能以“化整为零，个个击破”的方式管理数据。使用分区功能，维护操作可集中于表的特定部分。例如，数据库管理员可以只对表的一部分做备份，而不必对整个表做备份。对整个数据库对象的维护操作，可以在每个分区的基础上进行，从而将维护工作分解成更容易管理的小块。利用分区功能提高可管理性的一个典型用法是支持数据仓库中的‘滚动视窗’加载进程。假设数据库管理员每周向表中加载新数据。可以对该表进行范 围分区，使每个分区包含一周的数据。这样加载进程只是简单地添加新的分区。添加一个分区的操作比修改整个表效率高很多，因为 DBA 不需要修改任何其他分区。

2、用分区功能提高性能

由于限制了所检查或操作的数据数量，同时支持并行任务执行，Oracle 分区功能实现了性能上增益。这些特性包括：

1)分区修整：分区修整是用分区功能提高性能的最简单最有价值的手段。分区修整常常能够将查询性能提高几个数量级。例如，假设某个应用程序包含一个存储订单 历史记录的 Orders 表，并且此表已按周分区。查询一周的订单只需访问该订单表的一个分区。如果该订单表包含两年的历史记录，这个查询只需要访问一个分区而不是一百零四个。该 查询的执行速度因为分区修整而有可能快一百倍。分区修整能与所有其他 Oracle 性能特性协作。Oracle 能将分区修整功能与任何索引技术、联接技术或并行访问方法结合使用。

2)分区智能联接：分区功能可以通过称为分区智能联接的技术提高多表联接的性能。当两个表要联接在一起，而且每个表都用联接键来分区时，就可以使用分区智能联 接。分区智能联接将大型联接分解成较小的发生在各个分区间的联接，从而用较少的时间完成全部联接。这就给串行和并行的执行都能带来显著的性能改善。

3、用分区功能提高可用性

分区的数据库对象具有分区独立性。该分区独立性特点可能是高可用性战略的一个重要部分，例如，如果分区表的一个分区不能用，但该表的所有其他 分区仍然保持在线并可用。那么这个应用可以继续针对该分区表执行查询和事务处理，只要不是访问不可用的分区，数据库操作仍然能够成功运行。数据库管理员可以指定各分区存放在不同的表空间里，从而让管理员隔离其它表分区针对单个分区进行备份与恢复操作。还有，分区功能可以减少计划停机时间。由于分区功能改善了性能，使数据库管理员能用相对较少的时间完成大型数据库对象的维护工作。

五、有关表分区的一些维护性操作：

1、添加分区

以下代码给SALES表添加了一个P3分区

ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));

注意：以上添加的分区界限应该高于最后一个分区界限。

以下代码给SALES表的P3分区添加了一个P3SUB1子分区

ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');

2、删除分区

以下代码删除了P3表分区：

ALTER TABLE SALES DROP PARTITION P3;

在以下代码删除了P4SUB1子分区：

ALTER TABLE SALES DROP SUBPARTITION P4SUB1;

注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。

3、截断分区

截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。当表中即使只有一个分区时，也可以截断该分区。通过以下代码截断分区：

ALTER TABLE SALES TRUNCATE PARTITION P2;

通过以下代码截断子分区：

ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;

4、合并分区

合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。以下代码实现了P1 P2分区的合并：

ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;

5、拆分分区

拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。

ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);

6、接合分区(coalesca)

结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，值得注意的是，接合分区只能用于散列分区中。通过以下代码进行接合分区：

ALTER TABLE SALES COALESCA PARTITION;

7、重命名表分区

以下代码将P21更改为P2

ALTER TABLE SALES RENAME PARTITION P21 TO P2;

8、相关查询

跨分区查询

select sum( *) from
(select count(*) cn from t_table_SS PARTITION (P200709_1)
union all
select count(*) cn from t_table_SS PARTITION (P200709_2)
);

查询表上有多少分区

SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName'

查询索引信息

select object_name,object_type,tablespace_name,sum(value)
from v$segment_statistics
where statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX'
group by object_name,object_type,tablespace_name
order by 4 desc
--显示数据库所有分区表的信息：
select * from DBA_PART_TABLES
--显示当前用户可访问的所有分区表信息:
select * from ALL_PART_TABLES
--显示当前用户所有分区表的信息：
select * from USER_PART_TABLES
--显示表分区信息 显示数据库所有分区表的详细分区信息：
select * from DBA_TAB_PARTITIONS
--显示当前用户可访问的所有分区表的详细分区信息：
select * from ALL_TAB_PARTITIONS
--显示当前用户所有分区表的详细分区信息：
select * from USER_TAB_PARTITIONS
--显示子分区信息 显示数据库所有组合分区表的子分区信息：
select * from DBA_TAB_SUBPARTITIONS
--显示当前用户可访问的所有组合分区表的子分区信息：
select * from ALL_TAB_SUBPARTITIONS
--显示当前用户所有组合分区表的子分区信息：
select * from USER_TAB_SUBPARTITIONS
--显示分区列 显示数据库所有分区表的分区列信息：
select * from DBA_PART_KEY_COLUMNS
--显示当前用户可访问的所有分区表的分区列信息：
select * from ALL_PART_KEY_COLUMNS
--显示当前用户所有分区表的分区列信息：
select * from USER_PART_KEY_COLUMNS
--显示子分区列 显示数据库所有分区表的子分区列信息：
select * from DBA_SUBPART_KEY_COLUMNS
--显示当前用户可访问的所有分区表的子分区列信息：
select * from ALL_SUBPART_KEY_COLUMNS
--显示当前用户所有分区表的子分区列信息：
select * from USER_SUBPART_KEY_COLUMNS
--怎样查询出oracle数据库中所有的的分区表
select * from user_tables a where a.partitioned='YES'
--删除一个表的数据是
truncate table table_name;
--删除分区表一个分区的数据是
alter table table_name truncate partition p5;

在Oracle 11g中，组合分区功能这块有所增强，又增加了range-range,list-range,list-list,list-hash，并且 11g里面还支持Interval分区和虚拟列分

推荐教程：《Oracle学习教程》

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!