Explication détaillée de la partition de table de l'espace table Oracle et comment utiliser la requête de partition de table Oracle

Cet article organise les concepts et les opérations des tables de partition sous les aspects suivants :
1. Les concepts d'espace table et de tables de partition
2. Les fonctions spécifiques des partitions de table
3. partitions de table Avantages et inconvénients
4. Plusieurs types de partitions de table et méthodes de fonctionnement
5. Opérations de maintenance sur les partitions de table.
(1.) Concepts d'espace table et de table de partition
Espace table :
Il s'agit d'une collection d'un ou plusieurs fichiers de données. Tous les objets de données sont stockés dans l'espace table spécifié, mais ils stockent principalement des tables, ils sont donc appelés espaces table.

Table partitionnée :
Lorsque la quantité de données dans la table continue d'augmenter, la vitesse d'interrogation des données ralentira et les performances de l'application diminueront. À ce stade, vous devriez envisager de partitionner les données. tableau. Une fois la table partitionnée, la table logique reste une table complète, mais les données de la table sont physiquement stockées dans plusieurs espaces table (fichiers physiques), de sorte que lors de l'interrogation des données, la table entière ne sera pas analysée à chaque fois. surface.

(2). Le rôle spécifique du partitionnement de table
La fonction de partitionnement de table d'Oracle 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 et constitue un outil clé pour créer des systèmes de données de plusieurs gigaoctets ou des systèmes à ultra haute disponibilité.

La fonction de partitionnement peut subdiviser en outre une table, un index ou une table organisée 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, et ces segments peuvent être gérés collectivement ou individuellement. Cela donne à l'administrateur de base de données une flexibilité considérable lors de 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.

Quand utiliser les tables de partition :
1. La taille de la table dépasse 2 Go.
2. La table contient des données historiques et de nouvelles données sont ajoutées aux nouvelles partitions.

(3). Avantages et inconvénients du partitionnement de table
Le partitionnement de table présente les avantages suivants :
1. Améliorez les performances des requêtes : lorsque vous interrogez des objets de partition, vous ne pouvez rechercher que les partitions qui vous intéressent. , améliorant la vitesse de récupération.
2. Disponibilité améliorée : si une partition de la table échoue, les données des autres partitions de la table sont toujours disponibles ;
3. Maintenance facile : si une partition de la table échoue et que les données doivent être réparées ; , seulement cette partition juste ;
4. E/S équilibrées : différentes partitions peuvent être mappées sur des disques pour équilibrer les E/S et améliorer les performances globales du système.

Inconvénients :
Concerné par la table de partition : il n'existe aucun moyen de convertir directement une table existante en table de partition. Cependant, Oracle propose la fonction de redéfinition en ligne des tables.

(4). Plusieurs types et méthodes de fonctionnement des partitions de table

1. Partitionnement de plage :

Le partitionnement de plage mappe les données sur chaque partition en fonction de la plage. est déterminé par la clé de partition que vous avez spécifiée lors de la création de la partition. Cette méthode de partitionnement est la plus couramment utilisée et la clé de partition utilise souvent une date. Par exemple : vous pouvez partitionner vos données de ventes par mois.
Lors de l'utilisation du 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. Cette valeur 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. s'étendent 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, 
    PHONEVARCHAR2(15) NOT NULL, 
    EMAILVARCHAR2(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 : Diviser 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 
);

Deux. Partitionnement de liste :

La caractéristique de cette partition est qu'il n'y a que quelques valeurs dans une certaine colonne. Sur la base de cette fonctionnalité, 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. Partitionnement par hachage :

Ce type de partition utilise le hachage sur les valeurs des colonnes. arithmétique 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 ces partitions sont rendues cohérentes en taille par le partitionnement par hachage sur le périphérique d'E/S.

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

hash分区最主要的机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中,hash算法中最重要的是hash函数，Oracle中如果你要使用hash分区，只需指定分区的数量即可。建议分区的数量采用2的n次方，这样可以使得各个分区间数据分布更加均匀。

四.组合范围散列分区

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

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

五.复合范围散列分区：

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

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

(5).有关表分区的一些维护性操作：

一、添加分区

以下代码给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');

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

ALTER TABLE SALES DROP PARTITION P3;

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

ALTER TABLE SALES DROP SUBPARTITION P4SUB1;

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

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

ALTER TABLE SALES TRUNCATE PARTITION P2;

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

ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;

四、合并分区

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

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

五、拆分分区

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

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

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

ALTER TABLE SALES COALESCA PARTITION;

七、重命名表分区

以下代码将P21更改为P2

ALTER TABLE SALES RENAME PARTITION P21 TO P2;

八、相关查询

跨分区查询

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表空间表分区详解及oracle表分区查询使用方法相关文章请关注PHP中文网！