Oracle テーブルスペースのテーブルパーティションと Oracle テーブルパーティションクエリの使用方法の詳細な説明
- 高洛峰オリジナル
- 2017-01-06 13:15:361346ブラウズ
この記事では、次の観点からパーティション テーブルの概念と操作を整理します。
1. テーブル スペースとパーティション テーブルの概念
2. テーブル パーティションの利点と欠点
4.テーブル パーティションの特徴 いくつかの種類と操作方法
5. テーブル パーティションのメンテナンス操作
(1.) テーブル スペースとパーティション テーブルの概念
テーブル スペース:
は 1 つ以上のデータ ファイルの集合であり、すべてのデータ オブジェクトがここにあります。指定された表領域に主に表を格納するため、表領域と呼ばれます。
パーティション化されたテーブル:
テーブル内のデータ量が増加し続けると、データのクエリ速度が低下し、アプリケーションのパフォーマンスが低下します。この時点で、テーブルのパーティション化を検討する必要があります。テーブルがパーティション化された後も、論理テーブルは完全なテーブルのままですが、テーブル内のデータは物理的に複数のテーブル スペース (物理ファイル) に格納されるため、データをクエリするときに毎回テーブル全体がスキャンされるわけではありません。表面。
(2). テーブルパーティショニングの具体的な役割
Oracle のテーブルパーティショニング機能は、管理性、パフォーマンス、可用性を向上させることで、さまざまなアプリケーションに大きなメリットをもたらします。一般に、パーティショニングにより、特定のクエリやメンテナンス操作のパフォーマンスが大幅に向上します。さらに、パーティショニングは一般的な管理タスクを大幅に簡素化することができ、ギガバイト データ システムや超高可用性システムを構築するための重要なツールとなります。
パーティショニング機能は、テーブル、インデックス、またはインデックス構成テーブルをさらにセグメントに分割できます。これらのデータベース オブジェクトのセグメントはパーティションと呼ばれます。各パーティションには独自の名前があり、独自のストレージ特性を選択できます。データベース管理者の観点から見ると、パーティション化されたオブジェクトには複数のセグメントがあり、これらのセグメントを一括または個別に管理できるため、データベース管理者はパーティション化されたオブジェクトを管理する際にかなりの柔軟性が得られます。ただし、アプリケーションの観点から見ると、パーティション テーブルは非パーティション テーブルと同一であり、SQL DML コマンドを使用してパーティション テーブルにアクセスする場合は変更する必要はありません。
パーティションテーブルを使用する場合:
1. テーブルのサイズが 2GB を超えている。
2. テーブルには履歴データが含まれており、新しいデータは新しいパーティションに追加されます。
(3). テーブル パーティショニングのメリットとデメリット
テーブル パーティショニングには次の利点があります:
1. クエリのパフォーマンスの向上: パーティション オブジェクトをクエリするときに、必要なパーティションのみを検索できるため、取得速度が向上します。
2. 可用性の強化: テーブルのパーティションに障害が発生した場合でも、テーブルの他のパーティションのデータは引き続き利用可能です。
3. テーブルのパーティションに障害が発生し、データを修復する必要がある場合は、そのパーティションのみを修復します。修復可能です。
4. バランスのとれた I/O: 異なるパーティションをディスクにマッピングして、I/O のバランスをとり、システム全体のパフォーマンスを向上させることができます。
欠点:
パーティションテーブル関連: 既存のテーブルをパーティションテーブルに直接変換する方法はありません。ただし、Oracle ではオンラインでテーブルを再定義する機能が提供されています。
(4). テーブルパーティションのいくつかの種類と操作方法
範囲パーティション化を使用する場合は、次のルールを考慮してください:
1. 各パーティションには、パーティションに含まれない上限値を指定する VALUES LESS THEN 句が必要です。この上限以上のパーティション キー値を持つレコードは、次に高いパーティションに追加されます。
2. 最初のパーティションを除くすべてのパーティションには、このパーティションの前のパーティションの上限値が暗黙的に設定されます。
3. 最上位のパーティションでは MAXVALUE が定義されます。 MAXVALUE は不確実な値を表します。この値は、他のパーティションのパーティション キーの値よりも大きく、NULL 値を含む、パーティションで指定された VALUE LESS THEN の値よりも大きいと解釈できます。
200,000 行のデータを含む CUSTOMER テーブルがあるとします。このテーブルを CUSTOMER_ID ごとにパーティション化し、データ ファイルが複数の物理データにまたがるようにします。ディスク。以下はテーブルとパーティションを作成するコードです:
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
)例 2: 時間で割る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
)例 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
);2. このパーティションの特徴は、列内のいくつかの値、この機能に基づいて、リスト パーティショニングを使用できます。 例 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
)例 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. ハッシュ パーティション化: このタイプのパーティションでは、列の値にハッシュ アルゴリズムを使用して、行をどのパーティションに配置するかを決定します。カラム値に適切な条件がない場合は、ハッシュ分割を推奨します。 ハッシュ パーティショニングは、I/O デバイス上のハッシュ パーティショニングによってパーティションのサイズが一定になるため、パーティション番号を指定してデータを均等に分散するパーティショニングの一種です。
例 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 )略語:
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中文网！