Ausführliche Erläuterung der Oracle-Tabellenbereichs-Tabellenpartition und der Verwendung der Oracle-Tabellenpartitionsabfrage
- 高洛峰Original
- 2017-01-06 13:15:361346Durchsuche
Dieser Artikel organisiert die Konzepte und Operationen von Partitionstabellen unter folgenden Gesichtspunkten:
Die Konzepte von Tabellenbereichen und Partitionstabellen
Die Funktionen von Vor- und Nachteile von Tabellenpartitionen
Verschiedene Arten von Tabellenpartitionen und Betriebsmethoden
5. Wartungsvorgänge für Tabellenpartitionen.
(1.) Konzepte von Tabellenbereich und Partitionstabelle
Tabellenbereich:
Es handelt sich um eine Sammlung von einer oder mehreren Datendateien. Alle Datenobjekte werden im angegebenen Tabellenbereich gespeichert, sie speichern jedoch hauptsächlich Tabellen und werden daher Tabellenbereiche genannt.
Partitionierte Tabelle:
Wenn die Datenmenge in der Tabelle weiter zunimmt, verlangsamt sich die Geschwindigkeit der Datenabfrage und die Leistung der Anwendung nimmt ab. Zu diesem Zeitpunkt sollten Sie eine Partitionierung in Betracht ziehen Tisch. Nach der Partitionierung der Tabelle ist die logische Tabelle immer noch eine vollständige Tabelle, die Daten in der Tabelle werden jedoch physisch in mehreren Tabellenbereichen (physischen Dateien) gespeichert, sodass beim Abfragen der Daten nicht jedes Mal die gesamte Tabelle gescannt wird. Oberfläche.
(2). Die spezifische Rolle der Tabellenpartitionierung
Die Tabellenpartitionierungsfunktion von Oracle bringt große Vorteile für verschiedene Anwendungen, indem sie die Verwaltbarkeit, Leistung und Verfügbarkeit verbessert. Im Allgemeinen kann die Partitionierung die Leistung bestimmter Abfragen und Wartungsvorgänge erheblich verbessern. Darüber hinaus kann die Partitionierung allgemeine Verwaltungsaufgaben erheblich vereinfachen und ist ein wichtiges Werkzeug für den Aufbau von Gigabyte-Datensystemen oder Systemen mit extrem hoher Verfügbarkeit.
Die Partitionierungsfunktion kann eine Tabelle, einen Index oder eine indexorganisierte Tabelle weiter in Segmente unterteilen. Die Segmente dieser Datenbankobjekte werden Partitionen genannt. Jede Partition hat einen eigenen Namen und kann ihre eigenen Speichereigenschaften auswählen. Aus Sicht eines Datenbankadministrators verfügt ein partitioniertes Objekt über mehrere Segmente, und diese Segmente können gemeinsam oder einzeln verwaltet werden. Dies gibt dem Datenbankadministrator erhebliche Flexibilität bei der Verwaltung partitionierter Objekte. Aus Anwendungssicht ist eine partitionierte Tabelle jedoch identisch mit einer nicht partitionierten Tabelle, und beim Zugriff auf eine partitionierte Tabelle mithilfe von SQL-DML-Befehlen sind keine Änderungen erforderlich.
Wann werden Partitionstabellen verwendet?
1. Die Größe der Tabelle überschreitet 2 GB.
2. Die Tabelle enthält historische Daten und neue Daten werden zu neuen Partitionen hinzugefügt.
(3) Vor- und Nachteile der Tabellenpartitionierung
Die Tabellenpartitionierung hat die folgenden Vorteile:
1. Verbessern Sie die Abfrageleistung: Beim Abfragen von Partitionsobjekten können Sie nur nach den Partitionen suchen, die Sie interessieren , wodurch die Abrufgeschwindigkeit verbessert wird.
2. Erweiterte Verfügbarkeit: Wenn eine bestimmte Partition der Tabelle ausfällt, sind die Daten der Tabelle in anderen Partitionen weiterhin verfügbar.
3 repariert werden, nur diese Partition;
4. Ausgeglichene E/A: Verschiedene Partitionen können Festplatten zugeordnet werden, um E/A auszugleichen und die Gesamtsystemleistung zu verbessern.
Nachteile:
Bezogen auf Partitionstabellen: Es gibt keine Möglichkeit, eine vorhandene Tabelle direkt in eine Partitionstabelle umzuwandeln. Allerdings bietet Oracle die Funktion der Online-Neudefinition von Tabellen.
(4). Verschiedene Arten und Betriebsmethoden von Tabellenpartitionen
Beachten Sie bei der Verwendung der Bereichspartitionierung bitte die folgenden Regeln:
1 Jede Partition muss eine VALUES LESS THEN-Klausel haben, die einen oberen Grenzwert angibt, der nicht in der Partition enthalten ist. Alle Datensätze mit einem Partitionsschlüsselwert, der dieser Obergrenze entspricht oder diesen überschreitet, werden der nächsthöheren Partition hinzugefügt.
2. Alle Partitionen außer der ersten haben einen impliziten unteren Grenzwert. Dieser Wert ist der obere Grenzwert der vorherigen Partition dieser Partition.
3. In der höchsten Partition ist MAXVALUE definiert. MAXVALUE stellt einen unsicheren Wert dar. Dieser Wert ist höher als der Wert eines beliebigen Partitionsschlüssels in anderen Partitionen und kann auch als höher als der in einer beliebigen Partition angegebene Wert von VALUE LESS THEN verstanden werden, einschließlich Nullwerten.
Angenommen, es gibt eine CUSTOMER-Tabelle mit 200.000 Datenzeilen. Jede Partition speichert 100.000 Zeilen, damit Datendateien gespeichert werden können erstrecken sich über mehrere physische Festplatten. Im Folgenden finden Sie den Code zum Erstellen von Tabellen und Partitionen:
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
)Beispiel 2: Division durch ZeitCREATE 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
)Beispiel 3: MAXVALUECREATE 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
);Zweitens. Listenpartitionierung: Das Merkmal dieser Partition ist, dass es in einer bestimmten Spalte nur wenige Werte gibt. Basierend auf dieser Funktion können wir die Listenpartitionierung verwenden. Beispiel 1CREATE 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
)Beispiel 2CREATE 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. Hash-Partitionierung: Diese Art von Partition verwendet Hashing für Spaltenwerte Arithmetik, um zu bestimmen, in welche Partition eine Zeile eingefügt werden soll. Die Hash-Partitionierung wird empfohlen, wenn die Spaltenwerte keine geeigneten Bedingungen aufweisen. Hash-Partitionierung ist eine Art der Partitionierung, die Daten durch Angabe von Partitionsnummern gleichmäßig verteilt, da diese Partitionen durch Hash-Partitionierung auf dem E/A-Gerät in ihrer Größe konsistent gemacht werden.
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 )Abkürzung:
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中文网！