Oracleデータベースのビッグデータ・クエリーの最適化の問題--パーティション・テーブルの操作方法

Oracleデータベース内のパーティション・テーブルの操作方法
要旨:大量の業務データ処理のプロジェクトでは、アプリケーションシステムの性能を向上させ、データ管理を容易にするためにパーティションテーブルを使用することを考慮することができ、本稿ではパーティションテーブルの使用について詳しく紹介した.大規模なエンタープライズアプリケーションまたはエンタープライズクラスのデータベースアプリケーションでは、処理するデータ量は通常数十~数百GBに達することができ、TBレベルに達することもできます.ストレージ・メディアとデータ処理テクノロジーの開発も急速ですが、ユーザーのニーズを満たすことはできません.ユーザーの大量のデータを読み書き操作とクエリーでより速くするために、Oracleはテーブルとインデックスをパーティション化するテクノロジーを提供し、大規模なアプリケーション・システムのパフォーマンスを改善します.パーティションの使用の利点:・可用性の向上:テーブルのパーティションに障害が発生した場合、テーブルの他のパーティションでのデータは依然として使用可能である.・メンテナンスの便利さ:テーブルのあるパーティションに故障が発生した場合、データを修復する必要があり、そのパーティションだけを修復すればよい.・等化I/O:異なるパーティションをディスクにマッピングしてI/Oをバランスさせ、システム全体の性能を改善することができる.・クエリーのパフォーマンスの向上:パーティション化されたオブジェクトに対するクエリーは、関心のあるパーティションのみを検索し、検索速度を向上させることができる.Oracleデータベースでテーブルまたはインデックスのパーティションを提供する一般的な方法は、次の5つです.
・範囲区分・Hash区分(ハッシュ区分)・複合区分
 
        .リストパーティション
 
        .索引パーティション
         
 
次に、この3つのパーティション化方法を例としてそれぞれ説明します.テストの便宜上、まず3つの表空間を構築します. 
          

create tablespace dinya_space01 
datafile 'e:/demodata/dinya01.dnf' size 50M 
create tablespace dinya_space02 
datafile 'e:/demodata/dinya02.dnf' size 50M 
create tablespace dinya_space03 
datafile 'e:/demodata/dinya03.dnf' size 50M 

    1.1. パーティション表の作成
    1.1.1. レンジパーティション
範囲パーティションとは、データテーブル内の値の範囲をパーティション化し、値の範囲に基づいて、そのデータをどのパーティションに格納するかを決定します.シーケンス番号に基づいてパーティション化したり、ビジネスレコードの作成日に基づいてパーティション化したりします.需要説明:品目取引表があり、表名:material_transactions.このテーブルには将来、千万級のデータ記録数がある可能性があります.このテーブルを作成するときにパーティションテーブルを使用する必要があります.この場合、シーケンス番号を使用して3つのゾーンをパーティション化することができます.各ゾーンには3千万のデータが格納される予定です.また、5年ごとのデータが1つのゾーンに格納されるように、日付パーティションを使用することもできます. 
       
トランザクション・レコードのシーケンス番号に基づいて表を分割します.
           

create table dinya_test 
 ( 
transaction_id number primary key, 
item_id number(8) not null, 
item_description varchar2(300), 
transaction_date date not null 
 ) 
 partition by range (transaction_id) 
 ( 
partition part_01 values less than(30000000) tablespace dinya_space01, 
partition part_02 values less than(60000000) tablespace dinya_space02, 
partition part_03 values less than(maxvalue) tablespace dinya_space03 
 ); 

        
 
テーブルの作成に成功し、トランザクションのシーケンス番号に基づいて、トランザクションIDが3千万以下のレコードが最初のテーブル空間に格納されますdinya_space 01では、パーティション名:par_01では、3千万～6千万のレコードが2番目の表領域に格納されます.
　　 dinya_space 02では、パーティション名:par_02,取引IDが6千万以上のレコードは3番目のテーブルスペースdinya_に格納されるspace 03では、パーティション名はpar_03. 
 
取引日別に表を作成するには、次の手順に従います.

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

これにより,取引番号と取引日で区分されたパーティションテーブルをそれぞれ構築した.データを挿入するたびに、指定したフィールドの値に基づいて、指定したパーティション(表領域)に記録が自動的に格納されます. 
もちろん、partition by range(transaction_id,transaction_date)のような2つのフィールドの範囲分布を使用してパーティション化することもできます.パーティション条件の値も変更します.読者が自分でテストしてください.
 
    1.1.2. Hashパーティション
ハッシュ・パーティションは、I/Oデバイス上でハッシュ・パーティションを行うことで、パーティションのサイズが一致するため、パーティション番号を指定してデータを均一に分散するパーティション・タイプです.品目取引表のデータを取引IDに基づいて指定された3つの表領域にハッシュして格納する.
      

create table dinya_test 
 ( 
transaction_id number primary key, 
item_id number(8) not null, 
 item_description varchar2(300), 
transaction_date date 
) 
partition by hash(transaction_id) 
( 
 partition part_01 tablespace dinya_space01, 
 partition part_02 tablespace dinya_space02, 
 partition part_03 tablespace dinya_space03 
); 

テーブルの作成に成功しました.データが挿入され、transaction_を押します.idは、3つのパーティション、すなわち3つの異なるテーブル空間にレコードをハッシュ的に挿入する. 
 
     1.1.3. 複合パーティション
 
範囲に基づいてパーティションを分割した後、各パーティション内のデータをいくつかの表領域に分散させる必要がある場合があります.これにより、複合パーティションを使用します.複合パーティションは、まず範囲パーティションを使用し、次に各パーティション内でハッシュパーティションを使用するパーティション化方法です.たとえば、品目取引のレコードを時間パーティション化し、各パーティションのデータを3つのサブパーティションに分割し、3つの指定された表領域にデータをハッシュ的に格納します.
     

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

この例では、まず取引日に基づいて範囲パーティションを行い、その後、取引IDに基づいて記録を3つのテーブル空間にハッシュ的に格納する.
 
1.1.3リストパーティション
行をパーティションにマッピングする方法を明確に制御する必要がある場合は、リストパーティション化メソッドを使用します.各パーティションの説明では、パーティション列の列の離散値を指定できます.これは範囲パーティションとは異なり、その範囲はパーティションに関連しています.これはハッシュパーティションとは異なり、ユーザーは行をパーティションにマッピングする方法を制御できません.リストパーティション化手法は,離散値に従うモジュール化データ分割のためにわざわざ設計されている.範囲パーティションまたはハッシュパーティションは、それほど容易ではありません.さらに、リストパーティションは、無秩序で非相関のデータセットを非常に自然にグループ化し、組織することができる.

create table person(
PERSONNAME varchar2(200),
PERSONAGE number
)
 partition by list (PERSONNAME) 
 ( 
 partition s1 values ('  '), 
 partition s2 values ('  '), 
 partition s3 values (default) 
 ); 
  insert into person values('  ',222222222222222);
  insert into person values('  ',222222222222222);
  insert into person values('  ',222222222222222);

  
 
　　1.2. パーティション表アクション
以上、3つのパーティションテーブルの構築方法について説明したが、実際のデータを使用して、日付別の範囲でパーティション化してパーティションテーブルのデータレコードの操作をテストする. 
         
 
1.2.1. レコードの挿入:

insert into dinya_test values(1,12,’BOOKS’,sysdate); 
insert into dinya_test values(2,12, ’BOOKS’,sysdate+30); 
insert into dinya_test values(3,12, ’BOOKS’,to_date(’2006-05-30’,’yyyy-mm-dd’)); 
insert into dinya_test values(4,12, ’BOOKS’,to_date(’2007-06-23’,’yyyy-mm-dd’));
insert into dinya_test values(5,12, ’BOOKS’,to_date(’2011-02-26’,’yyyy-mm-dd’)); 
insert into dinya_test values(6,12, ’BOOKS’,to_date(’2011-04-30’,’yyyy-mm-dd’)); 

上記の表の結果、2006年までのデータは最初のパーティションpart_に格納されます.01では、2006年から2010年までの取引データが2番目のパーティションpart_に格納されます.02では、2010年以降のレコードは第3パーティションpart_に格納される03上.
 
1.2.2. クエリー・パーティション表レコード:SQL>select*from dinya_test partition(part_01); TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE -------------------------------------------------------------------------------- 1 12 BOOKS 2005-1-14 14:19: 2 12 BOOKS 2005-2-13 14:19: SQL> SQL> select * from dinya_test partition(part_02); TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE -------------------------------------------------------------------------------- 3 12 BOOKS 2006-5-30 4 12 BOOKS 2007-6-23 SQL> SQL> select * from dinya_test partition(part_03); TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE--------------------------------------------------------------------------5 12 BOOKS 2011-2-26 12 BOOKS 2011-4-30 SQL>クエリーの結果、挿入されたデータは取引時間範囲に応じて異なるパーティションに格納されていることがわかります.ここではパーティションを指定したクエリーです.もちろん、パーティションを指定せずにselect*from dinyaを直接実行することもできます.testクエリはすべて記録されます.検索されるデータ量が多い場合、指定されたパーティションは検索速度を大幅に向上させます.  　　1.2. パーティションテーブルのメンテナンス

    ： 
ALTER TABLE sales ADD PARTITION sales2000_q1 
VALUES LESS THAN (TO_DATE(‘2000-04-01’,’YYYY-MM-DD’) 
TABLESPACE ts_sale2000q1; 
    maxvalue  ，      ，               ！
    ： 
ALTER TABLE sales DROP PARTION sales1999_q1; 
    :
alter table sales truncate partiton sales1999_q2;
    ：
alter table sales merge partitons sales1999_q2, sales1999_q3 into sales1999_q23;
alter index ind_t2 rebuild partition p123 parallel 2;
    ： 
ALTER TABLE sales 
SPLIT PARTITON sales1999_q4 
AT TO_DATE (‘1999-11-01’,’YYYY-MM-DD’) 
INTO (partition sales1999_q4_p1, partition sales1999_q4_p2);
alter table t2 split partition p123 values (1,2) into (partition p12,partition p3);
    :
alter table x exchange partition p0 with table bsvcbusrundatald ;
      ：
select * from sales partition(sales1999_q2)
EXPORT    ： 
exp sales/sales_password tables=sales:sales1999_q1 
file=sales1999_q1.dmp 
IMPORT    ： 
imp sales/sales_password FILE =sales1999_q1.dmp 
TABLES = (sales:sales1999_q1) IGNORE=y
      ： 
user_tab_partitions, user_segments