[oracle]パーティションについて

■ORACLEパーティション勉強

実践！パーティションの基本から実装例まで
http://www.oracle.com/technetwork/jp/ondemand/db-technique/d-12-disk-1484778-ja.pdf

OracleR Database VLDBおよびパーティショニング・ガイド
11g リリース2 (11.2)
パーティションのメンテナンス
https://docs.oracle.com/cd/E16338_01/server.112/b56316/part_admin002.htm#i1007479


-------------------------
パーティションとは
-------------------------
　大きな表や索引をデータベース内部で複数の領域に分割して管理する
　内部的に表を分割しているため、ユーザやアプリケーションからはひとつの表に見える
　
・表分割の仕組み
　パーティション化キー：表を分割する基準となる列を指定
　※複数列をパーティション化キーとして指定可
　

-------------------------
パーティションのメリット
-------------------------
・パーティション単位の検索が可能
　パーティション・プルーニング
　⇒対象のデータが格納されているパーティションだけにアクセスし、
　　不要なパーティションを読み飛ばす機能
　　※WHERE句の条件にパーティション化キーを含める必要がある
　　※パーティション化キーに対して関数が実行されているSQL文は
　　　パーティション・プルーニングの対象にはなりません
　　※件数が少ない場合、パーティションなしのほうが処理速度が速い
　
・パーティション単位の管理が可能
・パーティション単位で障害の範囲を限定

-------------------------
パーティションの種類
-------------------------
・単一レベル・パーティション
　レンジ・パーティション 8i～
　⇒値の範囲でデータを分割する
　リスト・パーティション 9i～
　⇒特定のデータ（製品名、店舗名 etc）のカテゴリーによって表を分割
　　※指定された値以外のデータの挿入はエラー
　ハッシュ・パーティション 8i～
　⇒パーティションにデータを均等に配分する
　　※内部的に生成されるハッシュ値を使って、データを分割する
　　※ハッシュ値を基にパーティション・プルーニングの利用が可能
　　※パーティションキー列はなるべく一意の列を含めるようにする
　　※レンジパーティションと違い、各パーティションサイズがほぼ均等で
　　　パラレルDMLなどのパフォーマンスを最適化
・コンポジット・パーティション
　⇒単一レベル・パーティションを組み合わせて使う
　　メイン／サブ
　　例）レンジ・パーティションをさらにハッシュ・パーティションに分割することができる
・パーティション化の拡張
　インターバル・パーティション 11g～
　⇒定義したインターバル期間に従い、レコードの挿入時に必要に応じて自動的にパーティションが追加される
　　※レンジ・パーティションから変更することも可能
　リファレンス・パーティション 11g～
　⇒親子関係を利用したパーティショニング手法
　　親表を参照する子表の外部キーを利用
　　①外部キーを設定するカラムに、NOT NULL制約を設定する
　　②親表の主キー又は一意キーを参照する外部キーを作成する
　　③PARTITION BY REFERENCE句に、その外部キーを指定する
　バーチャルカラム・パーティション 11g～
　⇒実データは持たず、他のカラムから関数によって定義される仮想的な列
　　例) acc_no(アカウント番号)の上2桁をacc_branch(支店番号)として用いる
　　※実カラムを追加することなく、目的のパーティションを作成することが可能

【パーティションの種類と例】
　●レンジパーティション
　　日付でデータを管理したい
　　　特定の連続するデータ（売上日、商品ID etc）の範囲によって分割
　●インターバルパーティション
　　・パーティションの管理を簡素化したい
　　　自動でパーティション表を追加
　●ハッシュパーティション
　　・パーティション表を使ってバッチ処理を高速化させたい
　　　一意となるデータ（社員ID、商品ID etc）でデータを分散させて分割
　●コンポジットパーティション
　　・日付でデータを管理しつつ、データを均等に分散させたい
　　　パーティションを組み合わせてより細かい単位で表を分割

-------------------------
パーティション表に行えるメンテナンス作業
-------------------------
●パーティションの追加
　ADD PARTITION
●パーティションの削除
　DROP PARTITION
　※ハッシュ・パーティション以外のパーティションで実行可能
●パーティションの分割
　SPLIT PARTITION
●パーティションのマージ
　MERGE PARTITIONS
●パーティションの結合
　COALESCE PARTITION
　※データを残しパーティション表のみを削除する場合使用する
　※ハッシュ・パーティション表の場合のみ若干違う
●パーティションの移動
　MOVE PARTITION
●パーティションの交換
　EXCHANGE PARTITION
※パーティションの種類によって、実行可能なメンテナンス操作が異なる
-------------------------
パーティション結合
-------------------------
パーティション単位での結合処理
・パーティション・ワイズ・ジョイン
　パーティション表同士をパーティション化キーによって、パーティション単位で結合処理を行うことができる
　⇒フル・パーティション・ワイズ結合
　　両方の表を結合キーで同一レベルでパーティション化
　⇒パーシャル・パーティション・ワイズ結合
　　片方の表のみが結合キーをパーティション・キーとしてパーティション化されている場合に実行される結合
　
　パラレルで結合を実行する場合にジョイン作業の回数が少なくなり、レスポンス時間・結合に必要なメモリー量を減少

-------------------------
索引パーティション
-------------------------
表と同様に索引もパーティション化が可能
パーティション表に対し、通常の索引も作成可能
通常の表に対し、パーティション索引も作成可能
基礎となるパーティション表に変更があった場合、索引パーティションも自動でメンテナンスが行われる

●ローカル索引　
　・ローカル同一キー索引
　　⇒表パーティションと同一のキーでパーティション化する索引
　　　索引のキー列も表パーティションのキー列と同一である索引
　　　各索引パーティションの定義なしに作成が可能
　　　（CREATE INDEX 索引名 ON パーティション表名(列名) LOCAL;）
　・ローカル非同一キー索引
　　⇒表パーティションと同一のキーでパーティション化する索引
　　　索引のキー列が表パーティションのキー列と同一でない索引
●グローバル索引
　⇒表パーティションとは別のキーでパーティション化する索引
　　※基礎となるパーティション表に変更があった場合、手動で索引パーティションのメンテナンスが必要になる
　　※パーティション化された表に関係なく、自由に索引のパーティション化が可能

-------------------------
索引パーティションのメンテナンス
-------------------------
特定の操作（パーティションの結合、削除等）をパーティション表に対して行うと、
対応する索引が使用できなくなる
＝INDEX UNUSABLE

・ローカル索引
　⇒対応するパーティション索引が使用不可に
・グローバル索引
　⇒全てのパーティション索引が使用不可に

※索引パーティションがUNUSABLEかどうかの確認は
USER_IND_PARTITIONSビューのStatus列を確認します

索引が使用不可の状態でその索引を利用するアクセスを行った場合はエラーが発生
「ORA-01502: 索引'HR.ID_INDEX'またはそのパーティションが使用不可の状態です。」

使用不可となった索引は再構築する必要がある
ALTER TABLE sales MODIFY PARTITION p_1 REBUILD UNUSABLE
LOCAL INDEXES;

表定義を変更する際に同時に索引も再構築するにはUPDATE
INDEXES句を利用
ALTER TABLE hr.index_test MERGE PARTITIONS p_2,p_3 INTO
partition p_3 UPDATE INDEXES;

■補足

パーティションのTRUNCATEについて

truncate table <パーティションテーブル名、でも可能だが、
alter table xxxxx truncate partition <パーティション名> のほうがよい？

http://www.oracle.com/technetwork/jp/database/articles/tsushima/tsushima-hakushi-43-2405698-ja.html


パーティションのメンテナンスについて
ご存知ない方のために、ここでパーティションのメンテナンスについて簡単に説明します。 DDLは、第27回で説明したように、共有カーソルをINVALIDにする必要があるので、ライブラリ・キャッシュ上のオブジェクトに対して、'library cache lock'を排他モードで取得します。通常のDDLは、瞬時に終了するのであまり気にする必要はありませんが、第15回で説明したようにTRUNCATE TABLE文はミニ・チェックポイント（オブジェクト・レベルのチェックポイント）を行います（これはDROP TABLE文でも行います）。そのため、ダーティ・ブロックが多いと長時間そのオブジェクトにアクセスすることができなくなります（ハードパース時に'library cache lock'を共有モードで取得するからです）。これは、ALTER TABLE文のパーティション・レベルでも同じように、オブジェクトに対して'library cache lock'を排他モードで取得して、TRUNCATE PARTITIONとDROP PARTITIONではミニ・チェックポイントを行います。そのため、大きなパーティションだと長時間になってしまう場合があります（MOVE PARTITIONについては、新しいデータ領域にコピーするので、読取り専用でアクセスできるようになっています）。そして、このときの排他制御のレベルは、TMエンキューを排他モードで取得するのはパーティション・レベルですが、'library cache lock'を排他モードで取得するのはテーブル・レベルになります（これを知らない方が多いように思います）。そのため、複数のパーティションを同時に行っても'library cache lock'待ちになってしまうことに注意してください。