Upgrade to Pro — share decks privately, control downloads, hide ads and more …

津島博士のパフォーマンス講座「Oracle Partitioningの基本と最新情報」

oracle4engineer
April 26, 2024

津島博士のパフォーマンス講座「Oracle Partitioningの基本と最新情報」

Oracle Database Technology Night#78 開催分 セッションスライドです

oracle4engineer

April 26, 2024
Tweet

Video

More Decks by oracle4engineer

Transcript

  1. Agenda 1. Oracle Partitioningの概要 2. パーティション化⽅法 3. パーティション化と索引 4. パーティション・メンテナンス

    5. パフォーマンスと管理性の向上 6. ⾃動パーティション化 Copyright © 2024, Oracle and/or its affiliates 2
  2. Copyright © 2024, Oracle and/or its affiliates 3 Oracle Partitioningの概要

    • Oracle Partitioningとは • パーティション化のしくみ • Oracle Partitioningを使⽤する理由 • 現在のOracle Partitioning
  3. Oracle Partitioningとは 4 Copyright © 2024, Oracle and/or its affiliates

    オブジェクトをより⼩さな部分に論理的に分割する強⼒な機能 ⾼パフォーマンスや⾼可⽤性を必要とする⼤規模データベースの主要な要件 ビジネス要件ドリブン
  4. SALES JAN FEB EMEA APAC SELECT * FROM SALES; MOVE

    PARTITION COMPRESS READ ONLY; Oracle Partitioningとは ⼀つの表に⼆つの視点 5 Copyright © 2024, Oracle and/or its affiliates ⼀つの表 多数のパーティション
  5. Oracle Partitioningを使⽤する理由 パフォーマンス – データ・アクセス時間を短縮 可⽤性 – 重要な情報へのアクセス性が向上 コスト –

    複数のストレージ層を活⽤ 実装が容易 – アプリケーションや問合せの変更不要 成熟した機能 – 幅広いパーティション化⽅法サポート ⾼い評価 – 何万もの顧客が使⽤ 7 Copyright © 2024, Oracle and/or its affiliates SALES JAN FEB SALES MAR
  6. Oracle Partitioningを使⽤する理由 パフォーマンス – データ・アクセス時間を短縮 可⽤性 – 重要な情報へのアクセス性が向上 コスト –

    複数のストレージ層を活⽤ 実装が容易 – アプリケーションや問合せの変更不要 成熟した機能 – 幅広いパーティション化⽅法サポート ⾼い評価 – 何万もの顧客が使⽤ パーティション化により、次のようなデータ管理操作が可能 (関連するデータに対してのみ動作) • 問合せパフォーマンス • プルーニングおよび結合 (パーティション・ワイズ処理) • 管理パフォーマンス • データのロードおよび オプティマイザ統計収集 • 索引の作成と再構築 • バックアップおよびリカバリ … 表全体でなくパーティション・レベルで可能になる 8 Copyright © 2024, Oracle and/or its affiliates SALES JAN FEB MAR 結果: パフォーマンスが桁違いに向上
  7. Oracle Partitioningを使⽤する理由 パフォーマンス – データ・アクセス時間を短縮 可⽤性 – 重要な情報へのアクセス性が向上 コスト –

    複数のストレージ層を活⽤ 実装が容易 – アプリケーションや問合せの変更不要 成熟した機能 – 幅広いパーティション化⽅法サポート ⾼い評価 – 何万もの顧客が使⽤ パーティション化により削減されるもの • 個々のパーティションの… • メンテナンス期間 • 定期的な停⽌時間や障害の影響 • リカバリ時間 … 重要な表と索引がパーティション化されている場合 9 Copyright © 2024, Oracle and/or its affiliates SALES JAN FEB MAR 結果: 重要な情報へのアクセス性が向上 他のパーティションを表示、使用可能
  8. Oracle Partitioningを使⽤する理由 パフォーマンス – データ・アクセス時間を短縮 可⽤性 – 重要な情報へのアクセス性が向上 コスト –

    複数のストレージ層を活⽤ 実装が容易 – アプリケーションや問合せの変更不要 成熟した機能 – 幅広いパーティション化⽅法サポート ⾼い評価 – 何万もの顧客が使⽤ パーティション化では次の項⽬間でバランスを取る (各デー タを最適な⽅法で格納) • データの重要性 • ストレージ・パフォーマン • ストレージの信頼性 • ストレージの形式 … 複数のストレージ層を利⽤できる 10 Copyright © 2024, Oracle and/or its affiliates 結果: ストレージ・コストを2倍以上削減 ローエンド・ストレージ層 中間ストレージ層 10% アクティブ 85%⾮アクティブ ハイエンド・ ストレージ層 5% アクティブ 1990 2012 2020 … …
  9. Oracle Partitioningを使⽤する理由 パフォーマンス – データ・アクセス時間を短縮 可⽤性 – 重要な情報へのアクセス性が向上 コスト –

    複数のストレージ層を活⽤ 実装が容易 – アプリケーションや問合せの変更不要 成熟した機能 – 幅広いパーティション化⽅法サポート ⾼い評価 – 何万もの顧客が使⽤ 実装が容易 • アプリケーションに対して透過的 • パーティション化ではアプリケーションや問合せに対する変 更が不要 – パーティション化の利点をフル活⽤するには調整が必 要になることがある 成熟した定評ある機能 • 10年以上の開発期間 • 何万もの顧客が使⽤ • 幅広いパーティション化⽅法をサポート 11 Copyright © 2024, Oracle and/or its affiliates
  10. 現在のOracle Partitioning 12 Copyright © 2024, Oracle and/or its affiliates

    コア機能 パフォーマンス 管理性 Oracle 8.0 レンジ・パーティション化 ローカルおよびグローバル・レンジ索引 静的パーティション・プルーニング 基本的なメンテナンス: ADD, DROP, EXCHANGE Oracle 8i ハッシュ・パーティション化 レンジ-ハッシュ・パーティション化 パーティション・ワイズ結合 動的パーティション・プルーニング メンテナンスの拡張: MERGE Oracle 9i リスト・パーティション化 グローバル索引メンテナンス Oracle 9i R2 レンジ-リスト・パーティション化 ⾼速パーティションSPLIT Oracle 10g グローバル・ハッシュ索引 ローカル索引 Oracle 10g R2 表ごとに100万のパーティション 複数ディメンション・プルーニング ⾼速DROP TABLE Oracle 11g バーチャル・カラム・パーティション化 より多くの種類のコンポジット・パーティション リファレンス・パーティション化 インターバル・パーティション化 Partition Advisor 増分統計管理 Oracle 11g R2 ハッシュ-* パーティション化 リファレンス・パーティション化の拡張 “AND”プルーニング 複数ブランチ実⾏ (表OR拡張) Oracle 12c R1 インターバル-リファレンス・パーティション化 複数パーティションでのパーティション・メンテナンス ⾮同期グローバル索引メンテナンス オンライン・パーティションMOVE, TRUNCATEのカスケード, 部分索引 Oracle 12c R2 ⾃動リスト・パーティション化 複数列リスト・[サブ]パーティション化 パーティション外部表 オンライン・パーティション・メンテナンス操作 パーティション表へのオンライン表変換 DDLでのカーソル無効化の減少 フィルタ付きパーティション・メンテナンス操作 読取り専⽤パーティション 交換⽤の表作成 Oracle 18c オンライン・パーティション・メンテナンスの完了 オンライン表変換の拡張 データ・コンテンツの検証 Oracle 19c ハイブリッド・パーティション表 オブジェクト・ストレージ・アクセス* Oracle 23c Interval and auto list for hybrid partitioned tables Logical partition change tracking for materialized views ALTER TABLE MOVE for all partitions of a table (21c) Enhanced Partition metadata Table and partition level read access tracking * Manual installation of DBMS_CLOUD on non-autonomous, see MOS 2748362.1
  11. Copyright © 2024, Oracle and/or its affiliates 13 パーティション化⽅法 •

    単⼀レベルのパーティション化 • コンポジット・パーティション化 • パーティション化の拡張機能 • パーティション化と外部データ • パーティション化戦略の選択
  12. パーティション化の対象 • 表 • ヒープ表、索引構成表、外部表(12.2から) • 索引 • グローバル索引、ローカル索引 •

    マテリアライズド・ビュー • ハッシュ・クラスタ グローバル・パーティション索引 ローカル・パーティション索引 グローバル⾮パーティション索引 パーティション化⽅法 パーティション化⽅法 • 単⼀レベルのパーティション化 • レンジ (連続データの分割) • リスト (⾮連続データの分) • 複数列リスト (12.2から) • ハッシュ (データ分散、競合回避) • コンポジット・レベルのパーティション化 • [レンジ(インターバル) | リスト(⾃動リスト) | ハッシュ] – [レンジ | リスト | ハッシュ] • パーティション化の拡張機能 (特殊なパーティション) • インターバル (⾃動レンジ) • リファレンス (PK/FK制約を活⽤) • インターバル・リファレンス (2つの組合せ) • バーチャル・カラム (仮想列) • ⾃動リスト (12.2から) 14 Copyright © 2024, Oracle and/or its affiliates
  13. 単⼀レベルのパーティション化 レンジ・パーティション • データはレンジで整理されている • 下限は、前のパーティションの上限によって導出される • 必要に応じてスプリットとマージを⾏う • ギャップはない

    • 時系列データに最適 ハッシュ・パーティション • データはパーティション・キーのハッシュ値に基づいて配置 • ハッシュ・バケットの数はパーティションの数と同じ • 均等にデータ分散するのに最適 • 均等にデータ分散するにはパーティション数は2の累乗にする • パラレルDMLなどのパフォーマンスが最適化できる • パーティション・プルーニングは等価とINリスト条件のみ 15 Copyright © 2024, Oracle and/or its affiliates … JUL 2021 AUG 2021 SEP 2021 JAN 2022 MAXVALUE キー値 ハッシュ関数
  14. 単⼀レベルのパーティション化 リスト・パーティション • データは値リストで整理されている • リストあたり1つ以上の順序付けられていない個別値 • 関連もなく順序づけもされていないデータのセットをグループ化して 管理できる •

    DEFAULTパーティション機能 (未指定値を⼀つにまとめる) • DEFAULTパーティションの内容を確認し、必要に応じて新 しいパーティションを作成する • 地域など、個別の値のセグメント化に最適 複数列リスト・パーティション (12.2) • データは複数値のリスト (複数列) で整理されている • 最⼤16個のパーティション・キー列 • パーティション化キーの各セットは⼀意である必要がある • リスト-リストもほぼ同等だが、キーは2レベルまで • 個々のパーティションに複数値のセットを格納できる • ((JAPAN, Partners), (JAPAN, WEB)) • 1つのみのDEFAULTパーティション • ヒープ表および外部表、サブパーティションにも設定可能 • リファレンス・パーティション、⾃動リスト・パーティションにも対応 • 例えば (地域、チャネルなど) 、個別の値ペアのセグメント化に 最適 16 Copyright © 2024, Oracle and/or its affiliates … (USA, Direct) (USA, Partners) (GERMANY , Direct) ((JAPAN, Partners), (JAPAN, WEB)) DEFAULT … USA GERMANY JAPAN DEFAULT FRANCE
  15. コンポジット・パーティション化 データは2つのディメンション (単⼀レベル・パーティションの 組合せ) に沿って整理されている • レコード配置はディメンションによって明確に識別される • 例︓レンジ-リスト パーティション・プルーニングはコンポジットの順序とは関係ない

    • 1つまたは両⽅のディメンションに沿ったプルーニング • レンジ-リストとリスト-レンジは同じプルーニング 常に適切なコンポジット戦略を使⽤する トップレベルのディメンションは、主に管理性で選択する • 例えば、時間レンジの追加や削除 サブレベルのディメンションは、パフォーマンスや管理性で選 択する • 例えば、load_id、customer_id ⾮対称には利点もありますが、よく考えてください • 例えば、異なるリージョンでの異なる時間粒度 • ⾮対称コンポジット・パーティション化の影響を忘れない 17 Copyright © 2024, Oracle and/or its affiliates … … SEP 2021 JAN 2022 FEB 2022 AUG 2021 JUL 2021 WEST EAST CREATE TABLE EVENTS ..PARTITION BY RANGE (time_id) SUPARTITION BY LIST (region)
  16. コンポジット・パーティション化 ⾮対称サブパーティション サブパーティションの数は個々のパーティションで異なる • リスト・サブパーティション戦略でもっとも⼀般的 18 Copyright © 2024, Oracle

    and/or its affiliates TYP3 TYP1 TYP7 JAN 2022 FEB 2022 CREATE TABLE EVENTS.. PARTITION BY RANGE (time_id) SUPARTITION BY LIST (model) … … JAN 2022 FEB 2022 DEFAULT SELECT .. FROM events WHERE model = ‘TYP7’; パーティション・プルーニング機能への影響はなし • 存在しないパーティションはDEFAULTになる パーティション・ワイズ結合に影響する
  17. コンポジット・パーティション化 パーティションの追加 パーティションの追加 ADD PARTITIONは常に最上位レベルのディメンション上に • 新しく追加されたすべてのサブパーティションで同⼀ • レンジ-リスト: 新しいtime_idレンジ

    • リスト-レンジ: 新しいregion値リスト サブパーティションの追加 1つのパーティションのみに対するADD SUBPARTITION • ⾮対称サブパーティション・レベル上でのみ可能 • パーティション・ワイズ結合に影響 すべてのパーティションに対するADD SUBPARTITION • N回の操作が必要 (既存の各パーティションごと) • 将来のパーティションのサブパーティション・テンプレートを調整 19 Copyright © 2024, Oracle and/or its affiliates … … JAN 2022 FEB 2022 MAR 2022 AUG 2021 JUL 2021 WEST EAST CREATE TABLE EVENTS.. PARTITION BY RANGE (time_id) SUPARTITION BY LIST (region) … … SEP 2021 JAN 2022 FEB 2022 AUG 2021 JUL 2021 WEST EAST … … … … SOUTH
  18. コンポジット・パーティション化 パーティションの追加 (インターバル-*・パーティション) サブパーティション・テンプレートがない場合、次の1つのサ ブパーティションのみが作成される • レンジ: MAXVALUE • リスト:

    DEFAULT • ハッシュ: 1つのハッシュ・バケット サブパーティション・テンプレート サブパーティション・テンプレートにより将来のサブパーティ ションを定義する • 任意の時点で追加または変更可能 • 既存の[サブ]パーティションへの影響はない サブパーティションの物理属性も制御する • パーティションに対するパーティション表のデフォルト設 定と同じように インターバル・パーティション化とレンジ・パーティション化の 相違 • テンプレートでの命名はレンジのみ (パーティション名_ サブパーティション名) • インターバルでは名前はシステム⽣成 20 Copyright © 2024, Oracle and/or its affiliates JAN 2022 MAR 2022 FEB 2021 JAN 2021 FEB 2022
  19. パーティション化の拡張機能 ⾃動管理パーティション インターバル・パーティション レンジ・パーティション化の拡張 • 同⼀サイズのレンジ・パーティションの完全⾃動化 パーティションはメタデータ情報としてのみ作成される • 開始パーティションは永続化される 新しいデータが届くとセグメントが割り当てられる

    • 新規パーティションの作成は不要 • ローカル索引も作成およびメンテナンスされる インターバル・パーティション化はレンジ・パーティション化の透過的 な拡張とほぼ同じ (いくつかの点で微妙に異なる) ⾃動リスト・パーティション (12.2) リスト・パーティション化の拡張 • 各パーティションに1つの値を持つリスト・パーティションを⾃動作成 • サブパーティション・レベルはサポートされていない データを受け取ると⾃動的にパーティションを作成 • 個別のパーティション・キー値はすべて別々のパーティションに 格納される (DEFAULTパーティションの概念はない) • ⾃動作成されたパーティションのパーティション名はシステム が⽣成する • パーティションの特定には、パーティション名の代わりに FOR (<value>) 句を使⽤ • alter table <表名> drop partition for (<値>); リスト・パーティションを⾃動リスト・パーティションに進化させられる • 要件はDEFAULTパーティションがないこと 21 Copyright © 2024, Oracle and/or its affiliates JAN 2020 FEB 2020 MAR 2020 JAN 2020 FEB 2020 JAN 2020 パーティション管理が不要 SENSOR 1 SENSOR 7 SENSOR 3 SENSOR 1 SENSOR 7 SENSOR 1
  20. パーティション化の拡張機能 インターバルとレンジ・パーティションの相違点 パーティションの境界 • インターバル・パーティションには下限と上限がある • 無限上限 (MAXVALUE) はない •

    レンジ・パーティションは上限のみ • 下限は前のパーティションにより導出される • 上限を無限 (MAXVALUE) にできる パーティション名 • インターバル・パーティションに事前に名前を付けること はできない • PARTITION FOR (<value>)句を使⽤する • レンジ・パーティションには名前を付ける必要がある パーティション・マージ • 複数の実在しないインターバル・パーティションは暗黙 的にマージされる • 任意の時点で隣接する2つのレンジ・パーティションの みをマージできる (12.2からの複数パーティション・メン テナンスで3つ以上が可能に) パーティション数 • インターバル・パーティション表は常に100万個のパー ティションを持つ • 実在しないパーティションはINTERVAL句を介して“存在” • インターバル・パーティションにはMAXVALUE句がない • 最⼤値はパーティション数とINTERVAL句によって定義する • レンジ・パーティショニングは最⼤100万パーティション まで可能 • MAXVALUE句は最も上位のパーティションを定義する 22 Copyright © 2024, Oracle and/or its affiliates
  21. パーティション化の拡張機能 インターバルとレンジ・パーティションの相違点 レンジ・パーティション化のパーティション境界 前のパーティションのDropで下限が移動 • “2022年2⽉”は2022年1⽉1⽇から2022年2⽉28⽇ までとなる インターバル・パーティション化のパーティション境界 Dropはパーティション境界に影響しない •

    “2022年2⽉”はまだ、2022年2⽉1⽇から2022年2 ⽉28⽇の間である 24 Copyright © 2024, Oracle and/or its affiliates OCT 2021 NOV 2021 DEC 2021 FEB 2022 values less than (’01-MAR-2022’) values less than (‘01-JAN-2022’) OCT 2021 NOV 2021 DEC 2021 FEB 2022 values less than (‘01-JAN-2022’) less than (’01-JAN-2021’ + 2 x INTERVAL (1 MONTH))
  22. パーティション化の拡張機能 特殊なパーティション・キー リファレンス・パーティション ⼦表はPK-FKにより親表のパーティション化戦略を継承 • ⼦表 (events) ではパーティション・キー (run_date) が不要

    • 冗⻑格納とメンテナンスのオーバーヘッド削減 バーチャル・カラム・パーティション すべての属性を持つベース表に対して • 仮想 (派⽣) カラムで拡張される • 仮想カラムはパーティション・キーとして使われる 現在、パーティション・プルーニングは、仮想カラム (パーティション・ キー: acc_branch) 上の条件のみによって機能する • ベース・カラム (acc_no) の条件では影響しない 25 Copyright © 2024, Oracle and/or its affiliates 12500 12507 12666 12875 Adams Blake King Smith 12 12 12 12 32320 32407 32758 32980 Jones Clark Hurd Kelly 32 32 32 32 … CREATE TABLE accounts (acc_no number(10) not null, acc_name varchar2(50) not null, ... acc_branch number(2) generated always as (to_number(substr(to_char(acc_no),1,2))) partition by list (acc_branch) … RUNS … EVENTS … RANGE(run_date) Primary key run_id RANGE(run_date) Foreign key run_id SEP 2021 OCT 2021 NOV 2021 FEB 2022 FEB 2022 NOV 2021 OCT 2021 SEP 2021 ・パーティション化キーの継承は PK-FKリレーションシップによる
  23. パーティション化の拡張機能 インターバル・リファレンス・パーティション 新しいデータを受け取ると、新しいパーティションが⾃動的 に作成される • すべての⼦表は⾃動的にメンテナンスされる • ビジネス・モデリング改善のため、2つの有効なパーティション化 戦略の組合せ •

    移⼊済データの少ないリファレンス・パーティション表に最適化 されている パーティション名は既存のパーティションから継承される • 直接に関係するパーティションから名前を継承 • 親パーティションp100から⼦パーティションp100が⽣成される • 親パーティションp100と⼦パーティションc100から孫パーティ ションc100が⽣成される 26 Copyright © 2024, Oracle and/or its affiliates JAN 2020 FEB 2020 JAN 2020 CHANNELS RUNS SENSORS EVENT DETAILS JAN CHANNELS RUNS SENSORS EVENT DETAILS JAN CHANNELS RUNS SENSORS EVENT DETAILS FEB INSERT INTO events VALUES (’14-FEB-2020’, ... );
  24. パーティション化と外部データ ハイブリッド・パーティション表 (19c) 27 Copyright © 2024, Oracle and/or its

    affiliates CREATE TABLE hybrid_partition_orders ( prod_id NUMBER NOT NULL, cust_id NUMBER NOT NULL, time_id DATE NOT NULL, channel_id NUMBER NOT NULL, promo_id NUMBER NOT NULL, quantity_sold NUMBER(10,2) NOT NULL, amount_sold NUMBER(10,2) NOT NULL ) EXTERNAL PARTITION ATTRIBUTES ( TYPE ORACLE_LOADER DEFAULT DIRECTORY sales_data … ) PARTITION BY RANGE (time_id) ( PARTITION sales_2022 VALUES LESS THAN ('01-01-2023'), PARTITION sales_2021 VALUES LESS THAN ('01-01-2022') EXTERNAL LOCATION ('sales2021_data.txt'), PARTITION sales_2020 VALUES LESS THAN ('01-01-2021') EXTERNAL LOCATION ('sales2020_data.txt') • 外部表を含めたパーティション表の作成 • 古くなったパーティションをストレージに外だしするが、DBからも参 照できるようにする • 単⼀のレンジおよびリスト・パーティションのみ対応 (23cからイン ターバルと⾃動リストが追加) • 外部パーティションについてはREAD ONLYになる • 外部パーティションに対してSPLIT、MERGE、MOVEメンテナン ス操作はできない Q4_2018 Q3_2018 Q2_2018 Q1_2018 Q4_2017 Q3_2017 Q2_2017 Q1_2017 Q4_2016 ORDERS OBJECT STORAGE TABLE PARTITIONED BY QUARTER SQL HOT DATA COLD DATA UPDATES
  25. パーティション化と外部データ オブジェクト・ストアのデータへのアクセス どのオブジェクト・ストア内のデータにもアクセスできる • Oracle Object Store, AWS S3, Azure

    明⽰的認証または事前認証済みURI パーティション化とオブジェクト・ストアを組み合わせれば、 メリットは倍増する • 必要なデータだけをその場で扱う • 最適な場所でデータを管理・保存 28 Copyright © 2024, Oracle and/or its affiliates CREATE TABLE orders ( order_id number, order_date DATE, … ) ORGANIZATION EXTERNAL ( TYPE oracle_loader DEFAULT DIRECTORY data_dir ACCESS PARAMETERS ( CREDENTIAL ‘OSS_ACCESS' ) ) REJECT LIMIT unlimited PARTITION BY RANGE(order_date) ( partition q1_2015 values less than (‘2014-10-01’) LOCATION (‘https://swiftobjectstorage.us-ashburn-1 …’), partition q2_2015 values less than (‘2015-01-01’) LOCATION (‘...’), partition q3_2015 values less than (‘2015-04-01’) LOCATION (‘...’), partition q4_2015 values less than (‘2015-07-01’) ); Any Object Storage
  26. パーティション化と外部データ データ配置検証 パーティション化はデータが適切なパーティションに配置さ れる • ここでも1つ例外がある 外部パーティションはファイル内のデータがパーティションに 正しくマッピングされることに依存する • 間違ったパーティションに配置されても判断できない

    パーティション外部表によって追加される新機能によって データ配置を⾏ごとに検証する • ORA_PARTITION_VALIDATION(rowid) • データ配置が正しい場合には1を、正しくない場合に は0を返す SQL> SELECT hpto.*, ORA_PARTITION_VALIDATION(rowid) AS correct_partition FROM hpto; DEPTNO DNAME LOC CORRECT_PARTITION -------- ----------- -------------- ----------------- 12 dept_12 xp1_15 1 16 dept_16 dept_loc_16 1 17 dept_17 dept_loc_17 1 29 dept_29 xp2_30 1 31 dept_31 dept_loc_31 1 32 dept_32 dept_loc_32 1 9999 dept_50 xp_wrong 0 Copyright © 2024, Oracle and/or its affiliates 29
  27. パーティション化戦略の選択 考慮事項 • データ • 使⽤状況 パーティション化に期待する内容 • 問合せパフォーマンスの向上 •

    DMLの⾼速化 (競合ポイントの緩和) • ロード (またはパージ) パフォーマンスの向上 • データ管理の向上 データの論理形態 • システムへのデータの挿⼊⽅法は︖ • 時間、場所、テナント、ビジネス・ユーザー、… • レンジ、関連性のない値リスト、“とにかく量が多い場合”、… • システムでのデータ・メンテナンス⽅法は︖ • アクティブなデータの時間枠の移動、法的要件、 “永続”データ… • システムのデータへのアクセス⽅法は︖ • 常にフル・スキャン、共通のフィルタ条件付き、 常に索引アクセス… 30 Copyright © 2024, Oracle and/or its affiliates
  28. パーティション化戦略の選択 ⼀般的な利⽤パターン レンジ (インターバル) は依然として最も⼀般的なパーティション化戦略 • ほとんどの場合、時間に依存する リストはますます⼀般化している • 多くの場合、時間に基づいて、サブパーティション戦略としてよく使われる

    (レンジ-リスト) ハッシュはパフォーマンス (PWJ、DMLの競合) のために使われるだけではない • データ配置については、制御できないがある程度理解している • 均等な分散や等価条件のパーティション・プルーニング • 2の累乗ルールを忘れないこと ⼀般的には、管理性を優先してレンジ(時間など)を使⽤し、パフォーマンスや管理性としてリスト、ハッシュ、別キーのレンジ を使⽤したい場合はコンポジット (レンジ-リスト、レンジ-ハッシュ、レンジ-レンジ) を使⽤する • 時系列で管理 (追加や削除) されていない場合で、そのデータが⾮連続データであればリストをメインとする • その他のパーティション化は特殊な利⽤パターンになる 31 Copyright © 2024, Oracle and/or its affiliates
  29. パーティション化戦略の選択 データ量とパーティション数 表が100TBの場合 • 100万のパーティションでは各パーティションのサイズは 100MB 表が10TBの場合 • 100万のパーティションでは各パーティションのサイズは 10MB

    表が1TBの場合 • 100万のパーティションでは各パーティションのサイズは 1MB 1MBを読み込むのに何分かかりますか︖ • Exadataの全表スキャン速度は数⼗GB/秒から数 百GB/秒 多ければ良いとは限らない • すべてのパーティションはディクショナリのメタデータを表す • すべてのパーティションがSGAのメタデータ・フットプリントを 増⼤させる • パーティション数が多いとカタログ・ビューのパフォーマンスに 影響する可能性がある パーティション数と平均サイズのバランスを⾒つける • 「通常システム」であれば、セグメントのサイズが1桁GBで も問題はない • セグメント・サイズ5GB以上のパーティションを検討する 32 Copyright © 2024, Oracle and/or its affiliates
  30. Copyright © 2024, Oracle and/or its affiliates 33 パーティション化と索引 •

    パーティション表の索引 • ⼀意制約/主キー • 部分索引 (12.1)
  31. パーティション表の索引 グローバル索引は任意のパーティション内の⾏を参照する • 索引はパーティション索引と⾮パーティション索引がある • 索引パーティション・キーは索引キーのプリフィックス (同⼀キー索引) でなければならない • グローバル⾮同⼀キー・パーティション索引は

    サポートされていない ローカル索引は表と同様にパーティション化される • 索引パーティション・キーは索引キーと異なることがある • ただし、⾮同⼀キー索引は⼀意索引にはできない すべての索引に同じ表パーティション化戦略をやみくもに適⽤しない • さまざまな戦略が存在するのには理由がある 34 Copyright © 2024, Oracle and/or its affiliates グローバル・パーティション索引 ローカル・パーティション索引 グローバル⾮パーティション索引 C1 C2 C1 索引キー パーティション・キー 同⼀キー索引
  32. パーティション表の索引 それぞれの索引 ローカル索引 索引は表パーティションと同じ境界でパー ティション化される • Bツリーまたはビットマップ(ローカル索引のみ) ⻑所 • 管理が容易、並列索引スキャン

    短所 • 少量のデータを取り出すには効率が悪い (パーティション・プルーニングしない場合) グローバル⾮パーティション索引 すべてのパーティションにまたがる1つの索 引Bツリー構造 ⻑所 • 個々のレコードへの効率的なアクセス (OLTP環境で最も⼀般的) 短所 • パーティション・メンテナンスは常に索引メンテ ナンスを伴う グローバル・パーティション索引 索引はデータとは無関係にパーティション 化される • 各索引は全てのパーティションを参照できる ⻑所 • 可⽤性と管理性 短所 • パーティション・メンテナンスは常に索引メンテ ナンスを伴う 35 Copyright © 2024, Oracle and/or its affiliates
  33. パーティション表の索引 データ・アクセス (ローカルとグローバルのパーティション索引) 索引プローブの数はアクセスされたパーティション数と同じ • パーティション・プルーニングを⾏わないと、すべての索引 パーティションにプローブが⼊る 索引プリフィックスに基づくパーティション索引 (同⼀キー索 引)

    でのプルーニング OLTP環境には最適ではない • パーティション・プルーニングが常に⾏われる保証はない • 少ないアクセス⾏でプルーニングがないと効率が悪い • 例外︓DMLの競合を緩和するためのグローバル・ハッ シュ・パーティション索引 単⼀パーティション・プルーニングありのパーティション索引アク セス パーティション・プルーニングなしのパーティション索引アクセス 36 Copyright © 2024, Oracle and/or its affiliates
  34. パーティション表の索引 データ・メンテナンス (データの削除) 37 Copyright © 2024, Oracle and/or its

    affiliates • レコードが削除される – 索引メンテナンス – UndoとRedo • パーティションが削除される – グローバル索引の⾼速メンテナンス(12c) – Undoが少ない • パーティションが削除される – ローカル索引の削除 – 最⼩限のUndo DELETE FROM ... WHERE ... ALTER TABLE ... DROP PARTITION ... ⾮パーティション表 グローバル索引 ローカル索引
  35. パーティション表の索引 データ・メンテナンス ローカル索引 増分索引作成が可能 • 使⽤不可で初期作成、個別パーティションの再構築 • 索引メンテナンスを不要にする 1 つのパーティションのみを扱うパーティション・メンテナンス

    操作で⾼速索引メンテナンス (表と同じ操作で⾏う) • Exchange, Drop, Truncate 複数のパーティションに関わるパーティション・メンテナンスに は、索引メンテナンスが必要 • Merge、Splitで新しいデータセグメントを作成 • 新しい索引セグメントも作成される グローバル索引 増分索引作成は不可能ではないが難しい Oracle Database 12cからのDropとTruncateのための ⾼速索引メンテナンス • 速いのは索引メンテナンスが遅れるということ DropとTruncate以外のパーティション・メンテナンスには 索引メンテナンスが必要 • DML操作と同等の従来の索引メンテナンス 38 Copyright © 2024, Oracle and/or its affiliates
  36. ⼀意制約/主キー ⼀意索引により⼀意制約が適⽤される • 主キー制約は列にNOT NULLを追加 (表に1つのみ) ローカル索引とグローバル索引のどちらを選択すべきか ⼀意性を適⽤するためのローカル索引の要件 • パーティション・キー列が⼀意キーのサブセットにする

    (同⼀キー索引の必要がある) グローバル索引は表のパーティションに関係を持たない • 定義上、すべてのパーティションからのデータを含む • パーティションと⾮パーティションのグローバル索引に当ては まる グローバル索引は常に⼀意性を適⽤するために使⽤でき る (同⼀キー索引のみのため) 39 Copyright © 2024, Oracle and/or its affiliates PARTITION BY (col1), PK(col1) PARTITION BY (col1), PK(col2) PARTITION BY (col1), PK(col1) PARTITION BY (col1), PK(col2)
  37. 部分索引 部分ローカル索引と部分グルーバル索引 部分索引は⼀部の表パーティションのみ に関わる • ローカル索引以外にグローバル索引に も適⽤可能 フル索引の補完 オンライン索引メンテナンスの完全サポート 40

    Copyright © 2024, Oracle and/or its affiliates グローバル⾮パーティション索引 Table Partition Table Partition Table Partition グローバル・パーティション索引 ローカル・パーティション索引 部分グローバル索引 部分ローカル・パーティション索引 部分グローバル・パーティション索引 Indexing on Indexing off 索引なし フル索引 部分索引
  38. 部分索引 部分ローカル索引と部分グルーバル索引 部分索引により、表および[サブ]パーティション・レベルのメ タデータが導⼊される • パーティション表のパーティション・レベルで索引を作成す る/作成しないを指定可能に グローバル索引でも索引を作成しない表パーティションを 指定できる ローカル・パーティション索引はUsable/Unusable状態を

    活⽤する • Indexing ONのパーティションにはUsable、 Indexing OFFのパーティションにはUnusable • ローカル索引は部分索引ポリシーをUsable/Unusable で上書きできる 12cより前 Usable(使⽤可能)/Unusable(使⽤禁⽌) の索引セグ メント (索引が永続的でなく、表との関係性がない) 使⽤禁⽌索引パーティションは、⼀般的に⾼速ロード要 件がある環境で使⽤される • データ挿⼊時の索引メンテナンスのための時間を“節約”する • 使⽤禁⽌索引セグメントは、領域を消費しない (11.2から) 使⽤禁⽌索引はオプティマイザによって無視される (⼀部 のパーティションが使⽤禁⽌のパーティション索引でもオプ ティマイザが使⽤できる) • 11.2より前は、静的プルーニングと使⽤可能な索引パーティ ションへのアクセスのみでした • 11.2では、UNION ALLを使⽤した問合せのインテリジェント・ リライト (表拡張) 41 Copyright © 2024, Oracle and/or its affiliates SKIP_UNUSABLE_INDEXES = [TRUE | FALSE ]
  39. 部分索引 表拡張 (表OR拡張) 複数のSQLブランチが⽣成、実⾏される 部分的に使⽤禁⽌索引がある場合のインテリジェントな UNION ALLへの拡張 • 透過的な内部リライト •

    使⽤可能な索引パーティションが使⽤される • 使⽤禁⽌な索引パーティションに対するフル・パーティション・ アクセス サンプル計画 42 Copyright © 2024, Oracle and/or its affiliates select count(*) from toto where name = 'FOO' and rn between 1300 and 1400 Plan hash value: 2830852558 ------------------------------------------------------------------------- | Id | Operation | Name | Pstart| Pstop | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 1 | SORT AGGREGATE | | | | | 2 | VIEW | VW_TE_2 | | | | 3 | UNION-ALL | | | | | 4 | PARTITION RANGE SINGLE | | 14 | 14 | | 5 | TABLE ACCESS BY LOCAL INDEX ROWID| TOTO | 14 | 14 | |* 6 | INDEX RANGE SCAN | I_TOTO | 14 | 14 | | 7 | PARTITION RANGE SINGLE | | 15 | 15 | |* 8 | TABLE ACCESS FULL | TOTO | 15 | 15 | ------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 6 - access("NAME"='FOO') 8 - filter(("NAME"='FOO' AND "TOTO"."RN"=1400))
  40. 部分索引 実⾏例 パーティションp99を除外する部分グローバル索引 • 索引でパーティション・パルーニングは⾏わないので、 TBL$OR$IDX$PART$NUMファンクションでフィルターを ⾏っている 43 Copyright ©

    2024, Oracle and/or its affiliates SQL> CREATE TABLE tab1 (c1 NUMBER, c2 NUMBER, c3 CHAR(1000)) 2 PARTITION BY RANGE (c1) ( 3 PARTITION p01 VALUES LESS THAN (100), 4 PARTITION p02 VALUES LESS THAN (200), 5 PARTITION p03 VALUES LESS THAN (300), 6 PARTITION p99 VALUES LESS THAN (MAXVALUE) INDEXING OFF); SQL> CREATE INDEX tab1_ix1 ON tab1 (c2) GLOBAL INDEXING PARTIAL; SQL> SELECT partition_name,indexing 2 FROM user_tab_partitions 3 WHERE table_name = 'TAB1'; PARTITION_NAME INDEXING -------------- -------- P01 ON P02 ON P03 ON P99 OFF SQL> SELECT index_name,indexing FROM user_indexes 2 WHERE index_name = 'TAB1_IX1'; INDEX_NAME INDEXING ------------ -------- TAB1_IX1 PARTIAL SQL> SELECT * FROM tab1 WHERE c1 > 200 AND c2 = 1; ----------------------------------------------------------------------------------- | Id | Operation | Name | | Pstart| Pstop | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | 1 | VIEW | VW_TE_2 | | | | | 2 | UNION-ALL | | | | | |* 3 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TAB1 | | 3 | 3 | |* 4 | INDEX RANGE SCAN | TAB1_IX1 | | | | | 5 | PARTITION RANGE SINGLE | | | 4 | 4 | |* 6 | TABLE ACCESS FULL | TAB1 | | 4 | 4 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("TAB1"."C1">200 AND "TAB1"."C1"<300) 4 - access("C2"=1) filter(TBL$OR$IDX$PART$NUM("TAB1",0,0,65535,ROWID)>=3 AND TBL$OR$IDX$PART$NUM("TAB1",0,0,65535,ROWID)<=3) 6 - filter("C2"=1 AND "TAB1"."C1" IS NOT NULL)
  41. パーティション化と索引のまとめ 索引メンテナンスとパーティション・メンテナンス 完全なパーティションの独⽴性から可能な限りローカル索引を使⽤する • 主キー/⼀意索引を作成できない場合はグローバル索引 DROPとTRUNCATEの場合、ローカル索引とグローバル索引の両⽅で⾼速索引メンテナンス • Oracle 12c Release

    1 で追加された⾮同期グローバル索引メンテナンス グローバル索引とローカル索引の両⽅で利⽤可能なオンライン索引メンテナンス (パーティション・メンテナンス操作と⼀緒に) • Oracle 9i 以降のグローバル索引メンテナンス (UPDATE GLOBAL INDEXES)、Oracle 10g 以降のローカル索引メ ンテナンス (UPDATE INDEXES) その他のすべてのパーティション・メンテナンス操作では、ローカル索引とグローバル索引の両⽅で索引メンテナンスが必要 索引メンテナンスとパーティション・メンテナンスの判断は、常にパフォーマンス対可⽤性であるべき • 5%〜10%以上のデータに触れると、索引再構築は常に速くなる 新旧両⽅のデータに対しては部分索引の作成を検討する • 最初からすべてのデータに索引を付ける必要はない Copyright © 2024, Oracle and/or its affiliates 44
  42. Copyright © 2024, Oracle and/or its affiliates 45 パーティション・メンテナンス •

    基本コンセプト • 複数パーティションのメンテナンス (12.1) • フィルタ付きパーティション・メンテナンス (12.2) • オンライン・パーティション移動 (12.1) • ⾮同期グローバル索引メンテナンス (12.1) • パーティション表へのオンライン表変換 (12.2/18.1)) • リファレンス・パーティション化のためのカスケード切捨てとカスケード交換 (12.1)
  43. 基本コンセプト パフォーマンスが最も可視化しやすいが、その他の領域も 忘れてはならない • パーティション化では、パフォーマンス、管理性、可⽤ 性のすべてのビジネス関連領域に対応しなければな らない パーティションの独⽴性が重要 • パーティション・メンテナンス操作(PMOP)

    の基本要件 である • パーティションをデータ・ディクショナリのメタデータとして 認識する (つまり、データ移動がなければ⾼速) 完全なパーティションの独⽴性を提供する • 可能な限りローカル索引を使⽤する • パーティションで、全表レベル操作 (TRUNCATE、 MOVE、COMPRESSなど) を可能にする パーティションを可視化し、データベース管理に利⽤できる ようにする • 使いやすいパーティション名をつける メンテナンス操作はパーティションを意識したものでなけれ ばならない • 索引についても同様 メンテナンス操作はパーティション表のオンライン使⽤と⼲ 渉しないように 46 Copyright © 2024, Oracle and/or its affiliates
  44. 基本コンセプト データの管理 (パーティション・メンテナンス操作で対処) データの⾼速移⼊ • EXCHANGE • パーティションごとのダイレクト・パス・ロード データの⾼速削除 •

    DROP, TRUNCATE, EXCHANGE データの⾼速再編成 • MOVE, SPLIT, MERGE メンテナンス中は全てのパーティションが常に使⽤可能 • オンライン操作にはDMLロックはない • オフライン・モードでは、影響を受けるパーティションにDMLロックがかかる 47 Copyright © 2024, Oracle and/or its affiliates 表パーティション・メンテナンス操作 ALTER TABLE ADD PARTITION(S) ALTER TABLE DROP PARTITION(S) ALTER TABLE EXCHANGE PARTITION ALTER TABLE MODIFY PARTITION [PARALLEL][ONLINE] ALTER TABLE MOVE PARTITION [PARALLEL][ONLINE] ALTER TABLE RENAME PARTITION ALTER TABLE SPLIT PARTITION [PARALLEL][ONLINE] ALTER TABLE MERGE PARTITION(S) [PARALLEL] [ONLINE] ALTER TABLE COALESCE PARTITION [PARALLEL] ALTER TABLE ANALYZE PARTITION ALTER TABLE TRUNCATE PARTITION(S) Export/Import [by partition] Transportable tablespace [by partition] 索引メンテナンス操作 ALTER INDEX MODIFY PARTITION ALTER INDEX DROP PARTITION(S) ALTER INDEX REBUILD PARTITION ALTER INDEX RENAME PARTITION ALTER INDEX RENAME ALTER INDEX SPLIT PARTITION ALTER INDEX ANALYZE PARTITION
  45. 複数パーティションのメンテナンス 複数のパーティションに対する操作 複数のパーティションに対して、1回の操作でパーティショ ン・メンテナンスが可能 完全な並列処理 ローカルおよびグローバル索引の透過的なメンテナンス 複数のパーティションを順番に指定する パーティションの範囲を指定する 3個以上のパーティションに分割 48

    Copyright © 2024, Oracle and/or its affiliates ALTER TABLE events MERGE PARTITIONS Jan2021, Feb2021, Mar2021 INTO PARTITION Q1_2021 COMPRESS FOR ARCHIVE HIGH; … JAN 2021 FEB 2021 MAR 2021 APR 2021 DEC 2021 … Q 1 2021 APR 2021 DEC 2021 SQL > alter table pt merge partitions part05,part15,part25 into partition p30; Table altered. SQL > alter table pt merge partitions part10 to part30 into partition part30; Table altered. SQL > alter table pt split partition p30 into 2 (partition p10 values less than (10), 3 partition p20 values less than (20), 4 partition p30); Table altered. すべてのPMOPに対応
  46. フィルタ付きパーティション・メンテナンス操作 フィルタ条件を追加して特定のデータのみを選択できる データ・メンテナンスとパーティション・メンテナンスを組み合 わせる (複数のステップが必要であった) • パーティションの構成を変更 • パーティションの属性を変更 •

    パーティション・データを変更 (12.2) • 共通のデータ・メンテナンス・ワークフローを劇的に簡 素化 • すべてのオンラインおよびオフライン・パーティション・メンテ ナンス操作で使⽤可能 Copyright © 2024, Oracle and/or its affiliates 「1年以上経った注⽂はすべて削除するが、まだ完了してい ない注⽂は保持し、別の表領域に圧縮形式で格納する」 49 注⽂ 注⽂ 2015年 第3四半期 2015年 第3四半期 クローズした注⽂ 未処理の注⽂ 表領域: active 表領域: archive 49
  47. フィルタ付きパーティション・メンテナンス操作 フィルタ付きパーティション・メンテナンス操作の詳細 MOVE、SPLITおよびMERGE操作に単⼀表のフィルタ 条件を指定できる • 指定は、すべてのパーティション・メンテナンスで⼀貫し ている必要がある • 対象データを明確に指定する必要がある 様々な新しいパーティションやサブパーティションのセグメン

    トを作成するために、再帰的に⽣成されるCTASコマンド に指定が追加される フィルタ条件は、オフラインおよび新しいオンラインのパー ティション・メンテナンス操作の両⽅に対して動作する パーティションの移動の構⽂例 50 Copyright © 2024, Oracle and/or its affiliates ALTER TABLE orders MOVE PARTITION q3_2015 TABLESPACE archive INCLUDING ROWS WHERE order_state = ‘open’; ..オンラインの場合はどうなるでしょうか ALTER TABLE orders MOVE PARTITION q3_2015 TABLESPACE archive online INCLUDING ROWS WHERE order_state = ‘open’;
  48. フィルタ付きパーティション・メンテナンス操作 オンライン動作時の場合のDML動作 フィルタ条件は、進⾏中の同時DMLには適⽤されない 挿⼊は、常に実⾏される 含まれているデータに対する削除は、常に実⾏される 削除されたデータに対する削除は無効 含まれているデータに対する更新は常に実⾏される 除外されたデータに対する更新は無効 INSERT VALUES(order_state

    =‘closed’) DELETE WHERE order_state = ‘open’ DELETE WHERE order_state = ‘closed’ UPDATE set order_status = ‘closed’ WHERE order_state = ‘open’ UPDATE set order_status = ‘open’ WHERE order_state = ‘closed’ INCLUDING ROWS WHERE order_state = ‘open’ Copyright © 2024, Oracle and/or its affiliates 51
  49. オンライン・パーティション移動 52 Copyright © 2024, Oracle and/or its affiliates JAN

    2021 透過的なMOVE PARTITION ONLINE操作 DMLとクエリの同時実⾏ ローカル索引とグローバル索引の索引メンテナンス … SEP 2021 OCT 2021 NOV 2021 DEC 2021
  50. オンライン・パーティション移動 53 Copyright © 2024, Oracle and/or its affiliates …

    JAN 2021 SEP 2021 OCT 2021 NOV 2021 DEC 2021 透過的なMOVE PARTITION ONLINE操作 DMLとクエリの同時実⾏ ローカル索引とグローバル索引の索引メンテナンス 21cからパーティション単位ではなく、パーティション表全体 をMOVEできるようになった ベストプラクティス 可能であれば、同時 DML 操作を最⼩限に抑える • ジャーナリング⽤に追加のディスク・スペースとリソースが必要 • ジャーナルは最初の⼀括移動の後、再帰的に適⽤される • ジャーナルが⼤きいほど実⾏時間が⻑くなる DMLの同時実⾏は圧縮効率に影響 • 最初の⼀括移動で最⾼の圧縮率
  51. ⾮同期グローバル索引メンテナンス DROPやTRUNCATE PARTITIONの後でも、索引のメ ンテナンスなしで使⽤可能なグローバル索引 • 索引の⾃動更新 (update indexes) を指定すると動作 •

    影響を受けるパーティションは内部的に把握され、デー タ・アクセス時にフィルタリングされる • 実⾏計画の述語情報に以下が出⼒される • filter(TBL$OR$IDX$PART$NUM( … )) DROPとTRUNCATEは⾼速なメタデータのみの操作に • ⼤幅な⾼速化と初期リソース消費の削減 グローバル索引メンテナンスの遅延実⾏ • スケジュール・ジョブによる⾃動クリーンアップ (午前2:00) • ALTER INDEX REBUILD|COALESCE CLEANUPに よる⼿動メンテナンス実⾏ 54 Copyright © 2024, Oracle and/or its affiliates Before SQL> select count(*) from pt partition for (9999); COUNT(*) ---------- 25341440 SQL> select index_name, stutus, orphaned_entries from user_indexes; INDEX_NAME STATUS ORPHANED_ENTRIES ------------------ -------- ------------------ I1_PT VALID NO SQL> alter table pt drop partition for (9999); Table altered. Elapsed: 00:02:04.52 SQL> select index_name, stutus, orphaned_entries from user_indexes; INDEX_NAME STATUS ORPHANED_ENTRIES ------------------ -------- ------------------ I1_PT VALID NO After SQL> select count(*) from pt partition for (9999); COUNT(*) ---------- 25341440 SQL> select index_name,stutus,orphaned_entries from user_indexes; INDEX_NAME STATUS ORPHANED_ENTRIES ------------------ -------- ------------------ I1_PT VALID NO SQL> alter table pt drop partition for (9999) update indexes; Table altered. Elapsed: 00:00:00.04 SQL> select index_name,stutus,orphaned_entries from user_indexes; INDEX_NAME STATUS ORPHANED_ENTRIES ------------------ -------- ------------------ I1_PT VALID YES
  52. ⾮同期グローバル索引メンテナンス 索引クリーンアップの改善 最新リリースでの機能強化 (12.1でも利⽤可能) - 以下のBugを修正 • Bug 24515918 PMO_DEFERRED_GIDX_MAINT_JOB

    causes TX contention on busy tables and runs serially ⾃動クリーンアップPMO_DEFERRED_GIDX_MAINT_JOBの強化機能 • 索引ブロックのCoalesceを実施しないモードを追加して、OPTIONSパラメータで選択可能に • CLEANUP_ORPHANS: ORPHANED_ENTRIESだけをクリーンアップ (12.1より前の動作のようにCoalesceを⾏わない) • COALESCE: 索引ブロックのCoalesceも⼀緒に⾏う (12.1の最初はこれのみ) • より頻繁な索引クリーンアップの推奨がCLEANUP_ORPHANSになるので、これをデフォルトとした • パラレル処理を選択可能に (PARALLELパラメータ) ⼿動クリーンアップ (ALTER INDEX) の機能強化 • ONLY (索引ブロックのCoalesceをしない) 指定を追加 • ALTER INDEX <索引名> COALESCE CLEANUP ONLY PARALLEL <degree> ; 55 Copyright © 2024, Oracle and/or its affiliates EXEC DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE ('SYS.PMO_DEFERRED_GIDX_MAINT_JOB','OPTIONS','CLEANUP_ORPHANS'); EXEC DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE ('SYS.PMO_DEFERRED_GIDX_MAINT_JOB','PARALLEL','16');
  53. パーティション表へのオンライン表変換 ビジネスを中断させることなく、“ワンクリック”でパーティショ ン表に変換する ⾮パーティション表を1つのSQLコマンドでパーティションに変換 • 完全ノンブロッキング (オンライン) DDL • インプレース変換なし

    オンラインおよびオフライン・モード 索引 • 索引は変換プロセスを通じてオンラインで変換および保持 • 索引として⼗分な柔軟性(さまざまなルールに従う) • アクセス変更動作を最⼩限に抑えるためのデフォルトの索 引付けルール (INDEXES句を指定していない場合など) • グローバル・パーティション索引は元のパーティション形態を保持 • ⾮同⼀キー索引は、グローバル⾮パーティション索引になる • 同⼀キー索引は、ローカル・パーティション索引に変換される • ビットマップ索引は、ローカル・パーティション索引になる 56 Copyright © 2024, Oracle and/or its affiliates SALES SALES USA GERMANY JAPAN DEFAULT CREATE TABLE sales ( order_num NUMBER, region VARCHAR2 (10), … ); ALTER TABLE EVENTS MODIFY PARTITION BY LIST ( region ) (partition p1 values (‘USA’), partition p2 values (‘Germany’), partition p3 values (‘Japan’), partition p4 values (DEFAULT)) UPDATE INDEXES ONLINE;
  54. 表と索引の完全ノンブロッキング (オンライン) DDL MICRO DEFAULT THERMO GYRO EVENTS GYRO CAMERA

    THERMO DEFAULT EVENTS 2020 Jan 2020 Feb パーティション表へのオンライン表変換 パーティション表のオンライン表変換 (18cから) パーティション化はしているが 、“間違った”タイプ/粒度を 選んでしまった場合は、18cからのパーティション表のオンラ イン表変換 索引は変換プロセスを通じてオンラインで変換および保持 される デフォルトの索引作成ルールにより、アクセス変更時の動 作を最⼩限に抑えることが可能 • ⾮パーティショニング表の変換ルールとほぼ同じ • 相違点 • ローカル索引は、2つのディメンションのパーティション・キー のいずれかが含まれる場合、ローカルのまま • グローバル同⼀キー・パーティション索引は、ローカル・パー ティション索引に変換される ルールに従って、索引に⼗分な柔軟性を持たせる • 変更したい内容があればオーバーライドする 57 Copyright © 2024, Oracle and/or its affiliates CREATE TABLE EVENTS ( run_id NUMBER, sensor_type VARCHAR2 (50), … ) PARTITION BY LIST ( … ) ALTER TABLE EVENTS MODIFY PARTITION BY RANGE ( run_id ) SUBPARTITION BY LIST ( sensor_type )… UPDATE INDEXES (i1_run_id GLOBAL, i2_sensor LOCAL, i3 GLOBAL PARTITION BY RANGE ( … ) (PARTITION p0100 VALUES LESS THAN (100000), PARTITION p1500 VALUES LESS THAN (1500000), PARTITION pmax VALUES LESS THAN (MAXVALUE))) ONLINE;
  55. リファレンス・パーティション化のためのカスケード切捨てとカスケード交換 TRUNCATEおよびEXCHANGE PARTITIONのカスケード ビジネス継続性を向上させるためのTRUNCATEとEXCHANGE のカスケード 1つの原⼦トランザクションによってデータの整合性を維持 単純化されエラーが発⽣しにくいコード開発 カスケードはリファレンス・ツリー全体に対して適⽤される • 1つの原⼦トランザクション

    (オール・オア・ナッシング) • 複雑さや深さは重要ではない • リファレンス・パーティション表の任意のレベルで指定可能 • すべての外部キーに対してON DELETE CASCADEが必要 TRUNCATEのカスケードは⾮パーティション表にも利⽤可能 • ⾮パーティション表の依存性ツリーは、無効な外部キー 制約によって中断される可能性ある リファレンス・パーティション階層は、交換対象のターゲットと表 に適合する必要がある 同じレベルで複数の⼦が存在する複雑なツリーでは、所定の レベルの各⼦は親表の別キーを参照する必要がある • 階層ツリー内の表を明確にペアにするために必要 58 Copyright © 2024, Oracle and/or its affiliates ALTER TABLE events TRUNCATE PARTITION Jan2020 CASCADE; CHANNELS RUNS SENSORS EVENT DETAILS JAN CHANNELS RUNS SENSORS EVENT DETAILS FEB JA N FEB MAR …
  56. リファレンス・パーティション化のためのカスケード切捨てとカスケード交換 TRUNCATE PARTITIONのカスケード 適切なボトムアップ処理が必要です 7つの個別Truncate操作 1つのTruncate操作 59 Copyright © 2024,

    Oracle and/or its affiliates Parent Child 1 Child 2 Grandchild 1 Grandchild 2 Grandchild 3 Great Grandchild 1 Parent Child 1 Child 2 Grandchild 1 Grandchild 2 Grandchild 3 Great Grandchild 1 1 7 5 6 1 2 4 3
  57. リファレンス・パーティション化のためのカスケード切捨てとカスケード交換 TRUNCATE PARTITIONのカスケード 60 Copyright © 2024, Oracle and/or its

    affiliates SQL> create table intRef_p (pkcol number not null, col2 varchar2(200), 2 constraint pk_intref primary key (pkcol)) 3 partition by range (pkcol) interval (10) 4 (partition p1 value less than (10)); Table created. SQL> create table infRef_c1 (pkcol number npt null, col2 varchar2(200), fkcol number not null, 2 constraint pk_c1 primary key (pkcol), 3 constraint fk_c1 foreign key (fkcol) references intRef_p(pkcol) ON DELETE CASCADE) 4 partition by range reference (fk_c1); Table created. SQL> select * from infRef_p; PKCOL COL2 --------- -------------------------- 333 data for truncate - p 999 data for truncate – p SQL> select * from infRef_p; PKCOL COL2 FKCOL --------- -------------------------- --------- 333 data for truncate – p 333 999 data for truncate – p 999 SQL> alter table infRef_p truncate partition for (999) cascade update indexes; SQL> select * from infRef_p; PKCOL COL2 --------- -------------------------- 333 data for truncate - p SQL> select * from infRef_p; PKCOL COL2 FKCOL --------- -------------------------- --------- 333 data for truncate – p 333
  58. EXCHANGE PARTITIONのカスケード ターゲットからのボトムアップExchange(クリア) ターゲットへのトップダウンExchange(移⼊) 階層ツリーのすべてをExchangeする 1つのExchange操作 61 Copyright © 2024,

    Oracle and/or its affiliates Parent Child 1 Child 2 Grand child 1 Grand child 2 Grand child 3 Great Grandchild 1 Parent Child Grand child 1 2 3 4 5 6 Parent Child 1 Child 2 Grand child 1 Grand child 2 Grand child 3 Great Grandchild 1 Parent Child Grand child 1
  59. EXCHANGE PARTITIONのカスケード 63 Copyright © 2024, Oracle and/or its affiliates

    SQL> select * from intRef_p; PKCOL COL2 ---------- ---------------------------------- 333 p333 - data BEFORE exchange - p 999 p999 - data BEFORE exchange - p SQL> select * from intRed_c1; PKCOL COL2 FKCOL ---------- ---------------------------------- ---------- 1333 p333 - data BEFORE exchange - c1 333 1999 p999 - data BEFORE exchange - c1. 999 SQL> select * from intRed_gc1; PKCOL COL2 FKCOL ---------- ---------------------------------- ---------- 1333 p333 - data BEFORE exchange - gc1 1333 1999 p999 - data BEFORE exchange - gc1 1999 QL> select * from XintRef_p; PKCOL COL2 ---------- ---------------------------------- 333 p333 - data AFTER exchange - p SQL> select * from XintRed_c1; PKCOL COL2 FKCOL ---------- ---------------------------------- ---------- 1333 p333 - data AFTER exchange - c1 333 SQL> select * from XintRed_gc1; PKCOL COL2 FKCOL ---------- ---------------------------------- ---------- 1333 p333 - data AFTER exchange - gc1 1333
  60. EXCHANGE PARTITIONのカスケード 64 Copyright © 2024, Oracle and/or its affiliates

    SQL> select * from intRef_p; PKCOL COL2 ---------- ---------------------------------- 333 p333 - data AFTER exchange - p 999 p999 - data BEFORE exchange - p SQL> select * from intRed_c1; PKCOL COL2 FKCOL ---------- ---------------------------------- ---------- 1333 p333 - data AFTER exchange - c1 333 1999 p999 - data BEFORE exchange - c1. 999 SQL> select * from intRed_gc1; PKCOL COL2 FKCOL ---------- ---------------------------------- ---------- 1333 p333 - data AFTER exchange - gc1 1333 1999 p999 - data BEFORE exchange - gc1 1999 QL> select * from XintRef_p; PKCOL COL2 ---------- ---------------------------------- 333 p333 - data BEFORE exchange - p SQL> select * from XintRed_c1; PKCOL COL2 FKCOL ---------- ---------------------------------- ---------- 1333 p333 - data BEFORE exchange - c1 333 SQL> select * from XintRed_gc1; PKCOL COL2 FKCOL ---------- ---------------------------------- ---------- 1333 p333 - data BEFORE exchange - gc1 1333 SQL> alter table infRef_p exchange partition for (333) with table XintRef_p cascade update indexes; Table altered.
  61. パーティション・メンテナンスのまとめ オンライン操作 (データ移動) パーティション・メンテナンス操作 (PMOP) はオンライン • Move: 場所属性とストレージ属性を変更 •

    Merge: 多数のパーティションを1つにする • Split: 1つのパーティションを多数のパーティションに 表変換操作はオンライン • ⾮パーティション表をパーティション表に変更する • パーティション表を新しいパーティション表に変更する • パーティション表の形態を変更する すべてのオンライン操作が索引メンテナンスをサポート 可能な範囲で最適な時間枠を計画する オンライン操作はアプリケーションの透過性を維持し、ビジ ネスへの影響を最⼩限に抑える • アプリケーションのワークフローと設計について考えることを やめるために導⼊されたのではない オンライン操作のコストは並⾏処理によって増加 可能な場合は、同時実⾏DML操作を最⼩限に抑える • ジャーナリングに追加のディスク領域とリソースが必要 • ジャーナルは最初の⼀括移動の後、再帰的に適⽤ • ジャーナルが⼤きいほど、実⾏時間が⻑くなる 同時DMLは圧縮効率に影響する • 最初の⼀括移動で最⾼の圧縮率 65 Copyright © 2024, Oracle and/or its affiliates
  62. パーティション・メンテナンスのまとめ PMOPとDML パーティション・メンテナンス操作は、データをロードまたはアンロードするための⾼速で効率的な⽅法 ... しかし、それには代償がある • パーティション・メタデータを更新するための再帰的DML • 関連するパーティション (表と索引)

    の数に⼀般的に⽐例するが、例外もある • カーソルの無効化 • よりきめ細かな無効化と増分メタデータ無効化/リフレッシュを⾏うことに努⼒する • ローリング無効化(DEFERRED INVALIDATION) DMLは有効な代替⼿段 • 特にデータ量が少ない場合 66 Copyright © 2024, Oracle and/or its affiliates
  63. Copyright © 2024, Oracle and/or its affiliates 67 パフォーマンスと管理性の向上 •

    パーティション・プルーニング • パーティション・ワイズ結合 (PWJ) • パーティション交換 (PK/⼀意制約がある場合の交換、交換⽤の表作成(12.2)) • パーティション化の統計管理 (12.2) • DDLによるカーソル無効化の減少 (12.2) • 読取り専⽤パーティション (12.2) • ゾーン・マップ
  64. パーティション・プルーニング パーティション・エリミネーション • ストレージから取得するデータ量を激減させる • 関連するパーティションでのみ操作を実⾏ • 問合せパフォーマンスを透過的に向上させ、リソース使⽤量 を最適化する 単純なSQL⽂から複雑なSQL⽂まで対応

    • すべてのアプリケーションに対して透過的 2種類のプルーニング (実⾏計画にPstart/Pstopが追加) • コンパイル時の静的プルーニング (Pstart/Pstop: 数字) • 実⾏時の動的プルーニング (Pstart/Pstop: KEY/KEY(xx)) Exadata Storage Serverを補完 • パーティション化はパーティション・エリミネーションによって論理 的にプルーニングする • Exadataはストレージ索引によって物理的にプルーニングする • フィルタリングと射影 (列の絞り込み) を使⽤してデータをさらに削減 68 Copyright © 2024, Oracle and/or its affiliates EVENTS May 5 May 4 May 3 May 2 May 1 Apr 30 Apr 29 Apr 28 Apr 27 5⽉1⽇〜2⽇に開催される イベントの総数は︖ パーティション・メタデータを使⽤して、対象のパーティション のみにアクセスする
  65. パーティション・プルーニング 69 Copyright © 2024, Oracle and/or its affiliates プルーニング

    Operation Pstart/Pstop 備考 プルーニングなし PARTITION xxxx ALL 1/最⼤番号 静的 PARTITION xxxx SINGLE (1つのみ) PARTITION xxxx ITERATOR (2つ以上) 数字 これが効果的なとき データ型の暗黙変換 KEY できるだけ発⽣させない (第22回) バインド変数 KEY ネステッド・ループ結合 PARTITION xxxx ITERATOR KEY 内部表に対して (第46回) INリスト条件 PARTITION xxxx INLIST KEY(I) 第46回 OR条件 PARTITION xxxx OR KEY(OR) 第46回 複数列パーティション PARTITION xxxx MULTI-COLUMN KEY(MC) 先頭の列が条件にない (第22回) 副問合せ PARTITION xxxx SUBQUERY KEY(SQ) 第22回 ジョイン・フィルター PARTITION xxxx JOIN-FILTER フィルター名 ハッシュ結合のとき (第22回) AND Pruning PARTITION xxxx AND KEY(AP) 静的+動的(11gR2から)(第46回)
  66. 静的プルーニングと動的プルーニング “AND”プルーニング (FACT表とディメンションの両⽅にプルーニング条件があるスター変換) パーティション・プルーニング 70 SQL> SELECT * FROM tab01

    WHERE sdate = TO_DATE('2012/08/01','YYYY/MM/DD'); 実行計画(静的パーティション・プルーニング) -----------------------------------------<途中省略>------------------ | Id | Operation | Name | | Pstart| Pstop | -----------------------------------------<途中省略>------------------ | 0 | SELECT STATEMENT | | | | | | 1 | PARTITION RANGE SINGLE | | | 8 | 8 | |* 2 | TABLE ACCESS FULL | TAB01 | | 8 | 8 | 実行計画(動的パーティション・プルーニング) -----------------------------------------<途中省略>------------------ | Id | Operation | Name | | Pstart| Pstop | -----------------------------------------<途中省略>------------------ | 0 | SELECT STATEMENT | | | | | | 1 | PARTITION RANGE SINGLE | | | KEY | KEY | |* 2 | TABLE ACCESS FULL | TAB01 | | KEY | KEY | • バインド変数 • データ型の暗黙変換 • など Copyright © 2024, Oracle and/or its affiliates PARTITION RANGE ITERATOR PARTITION RANGE ALL パーティション ・プルーニング SQL> SELECT * FROM sales_h S, time T 2 WHERE S.time_cd = T.time_cd AND T.year IN ('2012','2013') 3 AND s.time_id BETWEEN TO_DATE('20120101','YYYYMMDD') AND TO_DATE('20130101','YYYYMMDD') ; 動的プルーニング 静的プルーニング
  67. パーティション・プルーニング ジェイン・フィルター (ブルーム・フィルタリング) 結合列のパーティション・プルーニング • パーティション化された明細表をマスタ表の条件で検索するなど • 結合時に効果的にフィルタリングする(結合列でフィルタリングする) • ymでフィルターされたtime_cd

    (結合列) のビットマップを作成し、sales_h.time_cdでパーティション・プルーニングを⾏う • PstartとPstopはフィルター名になる 71 Copyright © 2024, Oracle and/or its affiliates SQL> SELECT * FROM sales_h S, time T 2 WHERE S.time_cd = T.time_cd AND T.ym = TO_DATE('201201','YYYYMM') ; -------------------------------------------------<途中省略>------------------ | Id | Operation | Name | | Pstart| Pstop | -------------------------------------------------<途中省略>------------------ | 0 | SELECT STATEMENT | | | | | |* 1 | HASH JOIN | | | | | | 2 | PART JOIN FILTER CREATE | :BF0000 | | | | | 3 | PARTITION RANGE SINGLE | | | 1 | 1 | |* 4 | TABLE ACCESS FULL | time | | 1 | 1 | | 5 | PARTITION RANGE JOIN-FILTER | | |:BF0000|:BF0000| | 6 | TABLE ACCESS FULL | sales_h | |:BF0000|:BF0000| time_cdでフィルターを作成
  68. パーティション・プルーニング パーティション・キー・フィルタ条件に対して関数を使⽤しない SELECT avg( luminosity ) FROM atlas.EVENTS s, altas.times

    t WHERE s.time_id = t.time_id AND TO_CHAR(s.time_id, ‘YYYYMMDD’) between ‘20210101’ and ‘20220101’ Plan hash value: 672559287 ----------------------------------------------------- | Id | Operation | Name | Pstart| Pstop | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 1 | SORT AGGREGATE | | | | | 2 | PARTITION RANGE ALL| | 1 | 16 | |* 3 | TABLE ACCESS FULL | EVENTS| 1 | 16 | ----------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 –filter((TO_CHAR(INTERNAL_FUNCTION(“S”.”TIME_ID”), ’YYYYMMDD’)>=‘20210101’ AND TO_CHAR(INTERNAL_FUNCTION(“S”.”TIME_ID”),‘YYYYMMDD’)<=‘ 20200101’)) 23 rows selected. SELECT avg( luminosity ) FROM atlas.EVENTS s, altas.times t WHERE s.time_id = t.time_id AND s.time_id between TO_DATE(‘20210101’,’YYYYMMDD’) and TO_DATE(‘20220101’,’YYYYMMDD’) Plan hash value: 2025449199 ---------------------------------------------------------- | Id | Operation | Name | Pstart| Pstop | ---------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 1 | SORT AGGREGATE | | | | | 2 | PARTITION RANGE ITERATOR| | 9 | 13 | |* 3 | TABLE ACCESS FULL | EVENTS| 9 | 13 | ---------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 – filter(“S”.”TIME_ID”<=TO_DATE(‘2020-01-01 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’)) 22 rows selected. Copyright © 2024, Oracle and/or its affiliates 72
  69. パーティション・ワイズ結合 パーティション・プルーニングとPWJの実際の動作 (主パーティションでの結合) ⼤規模結合は複数の⼩規模結合に分 割され、並⾏して実⾏される (最⼩限の リソース消費で結合) • 結合するパーティション数は、DOPの 倍数である必要がある

    • 両⽅の表が、結合列上で同じ⽅法 でパーティション化されている必要が ある • 異なっている場合は、パーシャル・パー ティション・ワイズ結合 73 Copyright © 2024, Oracle and/or its affiliates … … MAR APR FEB JAN … … JAN FEB MAR APR Jan, Hash 1 Jan, Hash 2 JAN JAN
  70. パーティション・ワイズ結合 パーティション・プルーニングとPWJの実際の動作 (サブパーティションでの結合) 74 Copyright © 2024, Oracle and/or its

    affiliates … … MAR APR FEB JAN JAN CUSTOMER hash 1 hash 2 hash 3 hash 4 hash1 CUSTOMER FEB MAR APR hash 1 hash 2 hash 3 hash 4 hash 1 ⼤規模結合は複数の⼩規模結合に分 割され、並⾏して実⾏される (最⼩限の リソース消費で結合) • 結合するパーティション数は、DOPの 倍数である必要がある • 両⽅の表が、結合列上で同じ⽅法 でパーティション化されている必要が ある • 異なっている場合は、パーシャル・パー ティション・ワイズ結合 • パーティション・ワイズ処理は結合以 外でも動作する • GROUP BY, DISTINCT, ORDER BY, 分析 ファンクション
  71. パーティション交換 ロードとパージのためのパーティション交換 (EXCHANGE PARTITION) データの削除と追加はメタデータのみの操作となる • パーティションと表のメタデータを交換 • データ・ロード: 交換表にはロードされる新しいデータが含まれているが、

    交換⽤のパーティションは通常空である • データ・パージ: データが格納されていパーティションが空の表と交換される どちらの表でも論理形態が同⼀であることが必須の事前要件 • 列の数とデータ型は同じ • 列名は重要でない • 同⼀の制約 • 索引の数と型は同じ • 交換表上の索引はローカル・パーティション索引と同⼀ 75 Copyright © 2024, Oracle and/or its affiliates 交換表 May 18th 2021 May 19th 2021 May 20th 2021 May 21st 2021 May 22nd 2021 May 23rd 2021 EVENTS Table May 24th 2021 空または 新しいデータ パーティション表のPK索引でグローバル索引を必要とする場合は どうするか︖
  72. パーティション交換 PK/⼀意制約がある場合の交換 グローバル索引はパーティション表のためにのみ存在する • しかし、 交換表の⼀意性のためにも索引が必要では 正しくない • ⼀意索引は制約がENABLE状態 (新規データの制約チェッ

    ク) にのみ必要 • 新規または変更のデータは索引プローブを介して制約チェックする • DISABLE VALIDATE状態の制約はデータ挿⼊を防⽌する が索引は存在しない 解決策 パーティション化されたターゲット表 • グローバル索引 (パーティションまたは⾮パーティション) によっ てPK制約または⼀意制約を⾏う 交換するスタンドアロン表 (交換表) • 制約をDISABLED VALIDATE状態とする • 制約チェックのための索引がなく、パーティション交換の問題 もない 76 Copyright © 2024, Oracle and/or its affiliates SQL> alter table tt add(constraint x unique (col1) disable validate); Table altered. SQL> insert into tt values(1,2); insert into tt values(1,2); * ERROR at line 1; ORA-25128: No insert/update/delete on table with constraint (SCOTT.X) disabled and validated
  73. パーティション交換 PK/⼀意制約がある場合の交換 (簡単な例) 77 Copyright © 2024, Oracle and/or its

    affiliates CREATE TABLE tx_simple ( TRAN_KEY NUMBER, INQUIRY_TIMESTAMP TIMESTAMP(6), RUN_DATE DATE) PARTITION BY RANGE (RUN_DATE) ( PARTITION TRAN_202107 VALUES LESS THAN (TO_DATE('20210801','yyyymmdd')), PARTITION TRAN_202108 VALUES LESS THAN (TO_DATE('20210901','yyyymmdd')), PARTITION TRAN_202109 VALUES LESS THAN (TO_DATE('20211001','yyyymmdd')), PARTITION TRAN_202110 VALUES LESS THAN (TO_DATE('20211101','yyyymmdd')), PARTITION TRAN_MAX VALUES LESS THAN (MAXVALUE) ) ; Table created. INSERT into tx_simple ( select object_id, LAST_DDL_TIME, add_months(TO_DATE('20210501','yyyymmdd'), mod(OBJECT_ID,12)) from DAB_OBJECTS where object_id is not null) ; 73657 rows created. CREATE UNIQUE INDEX tx_simple_PK ON tx_simple (TRAN_KEY) nologging GLOBALE PARTITION BY RANGE (TRAN_KEY) ( PARTITION P_Max VALUES LESS THAN (MAXVALUE) ) ; Index created. ALTER TABLE tx_simple ADD (CONSTRAINT tx_simple_PK PRIMARY KEY (TRAN_KEY) USING INDEX nologging) ; Table altered. create table DAILY_ETL_table as select * from tx_simple partition (TRAN_202107) ; Table created. alter table daily_ETL_table add (CONSTRAINT pk_etl primary key (TRAN_KEY) DISABLE VALIDATE) ; Table altered. alter table tx_simple exchange partition TRAN_202107 with table dailt_ETL_table including indexes WITHOUT VALIDATION UPDATE GLOBALE INDEXES ; Table altered.
  74. パーティション交換 交換⽤の表作成 EXCHANGE PARTITIONは、データの削除と追加をメタ データのみの操作で⾏うため、⾮常に⾼速に実⾏できる • この交換するための表を簡単に作成する 単純なDDLコマンドで作成 パーティション交換コマンドが常に成功するように、セマン ティックおよび内部的な表形態の同⼀性が保証される

    特別なCREATE TABLE AS SELECT操作のように動作 常に空の表を作成する これまでの問題点 • 以下のSQLで空の表を作成していた • これには以下が含まれない • 使⽤不可 (UNUSED) の列、 • ⾮表⽰ (INVISIBLE) の列、 • 仮想式の列、 • ファンクション索引式の列、 • 他の内部設定や属性など 78 Copyright © 2024, Oracle and/or its affiliates CREATE TABLE events_cp TABLESPACE ts_boson FOR EXCHANGE WITH events; CREATE TABLE events_cp TABLESPACE test AS SELECT * FROM events WHERE 1=2 ;
  75. パーティション化の統計管理 統計収集 オプティマイザ統計を収集する必要がある • 動的サンプリングの使⽤は適切な解決⽅法ではない • デフォルトではグローバル (表レベル)、パーティション・レベ ル、サブ・パーティション・レベルの統計を収集 •

    問合せに必要なパーティションが1つの場合、オプティマイ ザはパーティション統計を使⽤しする • 必要なサブパーティションが1つの場合、サブパーティション・レ ベルの統計のみを使⽤ • 2つ以上のパーティションに関与する場合、グローバル・レベル とパーティション・レベルの統計を組み合わせて使⽤ 空の表に対してすべての問合せを実⾏して、列の使⽤状 況を設定 • これにより、ヒストグラムを⾃動的に取得する列を識別で きる オプティマイザ統計はデータがロードされた後、索引が作 成される前に収集する • 索引の統計は、作成時に⾃動収集される 効率的な統計管理 AUTO_SAMPLE_SIZEを使⽤する • 新しい効率的な統計収集を可能にする唯⼀の設定 • ハッシュ・ベースのアルゴリズム、表全体をスキャン • サンプリング速度、計算の正確さ 増分グローバル統計収集を可能にする (デフォルトでは動 作しない) • 1つパーティションの変更後にすべてのパーティションを スキャンするのを回避する • 表単位で管理される • パーティション交換ロードを使⽤する場合、交換前に ⾮パーティション表でシノプシスを作成する (12c) 79 Copyright © 2024, Oracle and/or its affiliates
  76. Sales Table パーティション化の統計管理 増分グローバル統計 80 2021/10/22 2021/10/23 2021/10/18 2021/10/19 2021/10/20

    2021/10/21 3. 新しいパーティションをテーブルに 追加してデータロード 2021/10/24 S7 4. 新しいパーティションの パーティション統計を収集 5. SYSAUXから他のパーティション のシノプシスを取得 6.既存と新しい一つのパーティ ションのシノプシスを集計するこ とでグローバル統計を作成する グローバル 統計 S1 S2 S3 S4 S5 S6 1. パーティション・レベル統計が収集され シノプシスを作成 2. パーティションレベルの統計とシノプシスを集計すること でグローバル統計を作成する Sysaux表領域 Copyright © 2024, Oracle and/or its affiliates
  77. パーティション化の統計管理 正確な統計を収集するために必要なステップ 表の増分機能をオンにする • すべての表に対して増分をtrueに設定することが可能 • 既存の表に対してのみ機能する ロード後にGATHER_TABLE_STATSを使⽤して表統計を収集 • パラメータの指定は不要

    コマンドはパーティションの統計を収集し、パーティション・レベルの統計およびシノプシスに基づいてグローバル統計を更新 81 Copyright © 2024, Oracle and/or its affiliates EXEC DBMS_STATS.SET_TABLE_PREFS('ATLAS','EVENTS','INCREMENTAL','TRUE'); EXEC DBMS_STATS.GATHER_TABLE_STATS('ATLAS','EVENTS'); EXEC DBMS_STATS.SET_GLOBAL_PREFS('INCREMENTAL','TRUE');
  78. DDLのカーソル無効化の減少 DDL(PMOP) によるハードパースの回数を減らす • ハード解析が避けられない場合、ワークロードを経時的に分散する • ⼀部のDDL⽤の新しいオプション句“[DEFERRED | IMMEDIATE] INVALIDATION”

    • DEFERREDの場合、可能な限り依存カーソルの無効化を回避する(避けられないとローリング無効化が⾏う) • IMMEDIATEの場合、依存カーソルを即座に無効化する • どちらでもない場合、CURSOR_INVALIDATIONパラメータがデフォルト動作を制御する (デフォルトはIMMEDIATE) サポートされるDDL: • パーティション表に対する Alter Table (列の操作、セグメントの操作) • パーティション表に対する Truncate Table • Alter Table … PARALLEL • Create Index, Drop Index, Alter Index 82 Copyright © 2024, Oracle and/or its affiliates DROP INDEX meas_campaign DEFERRED INVALIDATION;
  79. 読取り専⽤パーティション 読取り専⽤機能を表レベルからパーティションに拡張 ⼀部のデータを更新から保護しながら、同じ表の他のデー タの読取り/書込みを許可する パーティションとサブパーティションは、読取り専⽤または読取り /書込みに設定できる 読取り専⽤パーティションでデータを変更しようとするとエラー が発⽣する ユーザーまたはトリガーによる意図しないDMLからデータを保 護するのに最適

    読取り専⽤オブジェクトと読取り専⽤表領域 • 読取り専⽤表領域は更新から物理ストレージを保護する • 格納に影響しないDDL操作は許可される • 例えば、ALTER TABLE SET UNUSED, DROP TABLE • データ不変性は保証されない • 読取り専⽤オブジェクトは変更からデータを保護する • ‘データの不変性’ • データの中⾝に影響しない変更は禁⽌されない • 例えば、ALTER TABLE MOVE COMPRESS, ALTER TABLE MERGE PARTITIONS 83 Copyright © 2024, Oracle and/or its affiliates CREATE TABLE events ( event_id number, evnt_date DATE, … ) read only PARTITION BY RANGE(event_date) (partition q1_2020 values less than (‘2020-04-01’), partition q2_2020 values less than (‘2020-07-01’), partition q3_2020 values less than (‘2020-10-01’), partition q4_2020 values less than (‘2021-01-01’) read write ); … Q1 2020 Q2 2020 Q3 2020 Q4 2020 Read only Read only Read only Read write insert ブロックされたDML操作 許可されたDML操作 modify delete insert
  80. ゾーン・マップ 属性クラスタリングによるゾーン・マップ 組み合わせることのメリット 問合せパフォーマンスと同時実⾏性の向上 • 物理データ・アクセスの削減 • 選択性の⾼い操作のIO処理を⼤幅に削減 領域の使⽤効率を最適化 •

    索引の必要性を軽減 • ゾーン・マップは必要ないものを除外、索引は必要なもの を検出 • データ・クラスタリングを介した圧縮率の改善 完全なアプリケーション透過性 • あらゆるアプリケーションにメリット • 問合せの変更やヒントは不要 84 Copyright © 2024, Oracle and/or its affiliates 属性クラスタリング 列値がまとまってディスクに格納され るようにデータを配置 (データを順序 付けする) X ゾーン・マップ ゾーンごとに指定した列の最⼩値/ 最⼤値を格納 (表につき1つ) 問合せ実⾏時に不必要なデータ をフィルタするために使⽤ (パーティ ション・キー以外のプルーニング)
  81. ゾーン・マップ ゾーン・マップとパーティション ゾーンマップは、パーティション (またはサブパーティション) キーに含まれない列に対して、パーティションをプルーニングすること ができる 85 Copyright © 2024,

    Oracle and/or its affiliates JAN FEB MAR APR パーティション・キー: ORDER_DATE ゾーンマップ: SHIP_DATE ゾーンマップ列 SHIP_DATEは パーティション・キー ORDER_DATE と相関する JAN FEB MAR
  82. ゾーン・マップ ゾーン・マップとパーティション ゾーンマップは、パーティション (またはサブパーティション) キーに含まれない列に対して、パーティションをプルーニングすること ができる 86 Copyright © 2024,

    Oracle and/or its affiliates JAN FEB MAR APR パーティション・キー: ORDER_DATE ゾーンマップ: SHIP_DATE JAN FEB MAR MARパーティションと APRパーティションは プルーニングされる JAN FEB MAR WHERE ship_date = TO_DATE(’10-JAN-2011’)
  83. Copyright © 2024, Oracle and/or its affiliates 87 ⾃動パーティション化 (Autonomous

    Databaseのみ) • ⾃動パーティション化の概要 • ⾃動パーティション化のしくみ • ⾃動パーティション化のインターフェースと使い⽅
  84. ⾃動パーティション化の概要 ⾃動実装 Copyright © 2024, Oracle and/or its affiliates データベース・パフォーマンス・リポジトリ

    • ワークロード情報 (⾃動STS) • データオブジェクトと統計 分析 推奨エンジン • ワークロードの分析、パーティション 化の選択、性能検証のためのエキ スパート・システム 最適化された物理データベース • ⾃動パーティション化された表 実装 89 ⾃動STSの詳細は以下を参照してください Tech Night #64 「Automatic SQL Plan Management は使えるか」
  85. ⾃動パーティション化の概要 ⼿動実装 Copyright © 2024, Oracle and/or its affiliates データベース・パフォーマンス・リポジトリ

    • ワークロード情報 (⾃動STS) • データオブジェクトと統計 分析 レポーティング 最適化された物理データベース • ⾃動パーティション化された表 実装 90 ⾃動STSの詳細は以下を参照してください Tech Night #64 「Automatic SQL Plan Management は使えるか」
  86. ⾃動パーティション化のしくみ Copyright © 2024, Oracle and/or its affiliates 環境の検証 パーティション化

    の推奨 パーティション化の適⽤ ⾃動SQLチューニング・セット (⾃動STS) がアプリケーションのワークロードSQLを⻑期間にわたって捕捉する 環境の検証 ⾃動パーティション化の候補となる表が特定される (ある閾値を満たせば、候補として認定される) パーティション化の推奨 関連するワークロード (「テストワークロード」) を分析し、最適なパーティション化戦略を特定する 最適なパーティション化戦略を⽤いて、候補となる表が作成される (隠し表) • リソースを⼤量に消費する可能性がある、⼀時的に候補表と索引の約2倍のスペースが必要 隠し表を使⽤してテストワークロードを実⾏し、パフォーマンス効果を測定する (リソースを⼤量に消費する可能性がある) 性能上の利点が最⼩限の基準を満たす場合、⾃動パーティション化として推奨される パーティション化の適⽤ 推奨された表は⾃動でパーティション表になるように変更される (パーティション表へのオンライン変更) 現在、⾃動パーティション化の起動は⼿動のみ (バックグランドで⾃動起動されない) 主な3つの運⽤フェーズ • コンフィギュレーションと⼊⼒パラメータ制御で動作 • 個別ステップを柔軟に制御できる 91
  87. ⾃動パーティション化のインターフェースと使い⽅ DBMS_AUTO_PARTITIONのサブプログラム DBMS_AUTO_PARTITIONの サブプログラム ⽤途 CONFIGURE ⾃動パーティション化の動作を制御する VALIDATE_CANDIDATE_TABLE 特定の表が⾃動パーティション化の対象である かどうかを評価する

    (オプション) RECOMMEND_PARTITIONING _METHOD ⾃動パーティション化モードの推奨を⾏う 設定により⾃動的に推奨を適⽤することも可能 APPLY_RECOMMENDATION 推奨事項の適⽤ REPORT_ACTIVITY 特定期間内の⾃動パーティション化操作のレ ポート REPORT_LAST_ACTIVITY 直近の⾃動パーティション化操作のレポート Copyright © 2024, Oracle and/or its affiliates REPORT_ONLY IMPLEMENT VALIDATE_CANDIDATE_TABLE (対象表かの評価) AUTO_PARTITION_MODE APPLY_RECOMMENDATION (推奨事項の適⽤) dba_auto_partition_recommendations (PARTITION_METHODと分析レポートの確認) RECOMMEND_PARTITIONING_METHOD (TABLE_OWNER, TABLE_NAME) RECOMMEND_PARTITIONING_METHOD CONFIGURE dba_auto_partition_recommendations (分析された表の確認) REPORT_LAST_ACTIVITY 表指定 (TABLE_OWNER,TABLE_NAME) Yes 92
  88. CONFIGUREプロシージャのパラメータ 構⽂ お使いの環境のすべてのコンフィギュレーション設定を確認 ⾃動パーティション化のインターフェースと使い⽅ Copyright © 2024, Oracle and/or its

    affiliates dbms_auto_partition.configure(PARAMETER_NAME, PARAMETER_VALUE, ALLOW); PARAMETER _NAME ⽤途 AUTO_PARTITION_MODE ⾃動パーティション操作のモードを設定する OFF: ⾃動パーティション化は無効 REPORT_ONLY: 推奨事項とレポートが⽣成される (デフォルト) IMPLEMENT: REPORT_ONLYに推奨事項の実装が追加される AUTO_PARTITION_SCHEMA ⾃動パーティション化に対するスキーマの包含リスト(allow=>TRUE)と除外リスト(allow=>FALSE)を設定する (ALLOW=>NULLで、指定スキーマを包含リストまたは除外リストから削除) AUTO_PARTITION_TABLE ⾃動パーティション化に対する表の包含リスト(allow=>TRUE)と除外リスト(allow=>FALSE)を設定する (ALLOW=>NULLで、指定表を包含リストまたは除外リストから削除) PARAMETER_VALUEは<スキーマ名>.<表名>と指定する AUTO_PARTITION_REPORT_RETENTION ⾃動パーティション化ログが削除される前にデータベースに保持される⽇数を設定する(デフォルト値は90⽇) SQL> SELECT * FROM dba_auto_partition_config; PARAMETER_NAME PARAMETER_VALUE LAST_MODIFIED MODIFIED_BY ----------------------------------- -------------------- -------------------- -------------------- AUTO_PARTITION_SCHEMA AUTO_PARTITION_REPORT_RETENTION 90 AUTO_PARTITION_MODE REPORT ONLY AUTO_PARTITION_TABLE 0 93
  89. ⾃動パーティション化のインターフェースと使い⽅ 候補表の検証 オプションのステップで、推奨エンジンが⾃動的に候補表を特定する • 単⼀表に対する検証の例 • 評価するワークロード指定 (SQLSET_OWNER、SQLSET_NAME) も可能 (デフォルトは⾃動STS)

    主な検証項⽬ • 表サイズが64GB以上、最新の統計がある、STSに表に対する問合せが5つ以上ある • 外部表、ハイブリット・パーティション表、⼀時表、索引構成表、クラスタ化表でない • ⼿動でパーティション化されていない • など 94 Copyright © 2024, Oracle and/or its affiliates SQL> select dbms_auto_partition.validate_candidate_table( table_owner => 'TPCH',table_name => 'LINEITEM’) table_validation from dual; TABLE_VALIDATION ---------------- VALID
  90. ⾃動パーティション化の推奨 (実⾏) システムが⾃動的にワークロードと表を選択して、推奨事項が⽣成される • バックグランドで定期的に実⾏はされない (RECOMMEND_PARTITION_METHODファンクションを⼿動で起動) • オプションで単⼀表や特定のワークロードに対して実⾏可能 • 表の指定

    (TABLE_OWNER、TABLE_NAME) :デフォルトはNULL • 指定がない場合 (NULL) は TIME_LIMITパラメータで実⾏時間が制限される (デフォルト1⽇) • ワークロードの指定 (SQLSET_OWNER、SQLSET_NAME) : デフォルトはSYSとSYS_AUTO_STS (⾃動STS) デフォルトモードREPORT_ONLYでの実⾏を推奨 • パフォーマンス分析結果の⼿動スクリーニング (選別) を可能にする ⾃動パーティション化のインターフェースと使い⽅ Copyright © 2024, Oracle and/or its affiliates SQL> declare recommendation_id varchar2(32); begin recommendation_id := dbms_auto_partition.recommend_partition_method( table_owner => 'TPCH', table_name => 'LINEITEM'); end; / PL/SQL procedure successfully completed. 95
  91. ⾃動パーティション化のインターフェースと使い⽅ 特定されたパーティション化戦略 データ・ディクショナリで⾃動パーティション化の推奨事項に ついて確認 • 分析された表と⾃動パーティションの推奨事項 • 表を⼿動で⾃動パーティション表に変更するDDL インターバル、⾃動リスト、ハッシュに対する⾃動パーティ ションの推奨事項

    • パフォーマンス・ベースの推奨事項 • ⾃動パーティション化はユーザーのメンテナンス操作を必 要としない • ⾃動パーティション化のインターバルは内部関数を使⽤す るため、通常のインターバル・パーティション化とは異なる 分析された表のパーティション・キーとパーティション・メソッドを 確認 推奨事項を⼿動で適⽤する場合 (REPORT_ONLYを 指定) • 推奨事項に従って表がパーティション化された後にワーク ロードに対して⽣成されたパフォーマンス分析レポートを取 得して検証する • 推奨事項を⼿動で検証した後、推奨事項を適⽤する (次⾴) Copyright © 2024, Oracle and/or its affiliates SQL> SELECT * FROM dba_auto_partition_recommendations; SQL> SELECT table_owner,table_name, partition_method,partition_key FROM dba_auto_partition_recommendations WHERE recommendation_id = :recommendation_id; SQL> SELECT report FROM dba_auto_partition_recommendations WHERE recommendation_id = :recommendation_id; 96
  92. ⾃動パーティション化のインターフェースと使い⽅ ⾃動パーティション推奨事項の適⽤ ローカルで適⽤ 推奨分析が⾏われたシステムと同じシステムで推奨事項 を適応する • 表と索引のオンライン変更 • 推奨の⼀部として実⾏されない場合のオプション リモート

    (本番環境など別のデータベース) で適⽤ 与えられた推奨事項をリモート・システムに適⽤する2ス テップの操作 1. 推奨分析が⾏われたシステム上で修正するための変 更DDLを抽出する • 表と索引をオンラインで変更するためのDDLを⽣成する 2. ⽣成された変更DDLをターゲット・システムで実⾏して 表を変更する Copyright © 2024, Oracle and/or its affiliates SQL> begin dbms_auto_partition.apply_recommendation('<id>'); end; / PL/SQL procedure successfully completed. SQL> select modify_table_ddl from DBA_AUTO_PARTITION_RECOMMENDATIONS where RECOMMENDATION_ID = :RECOMMENDATION_ID; 97
  93. レポーティング (進捗状況とパフォーマンス分析) 進捗状況 ⾃動パーティション化の進⾏状況は⻑時間実⾏されるオペレーションとして追跡される パフォーマンス分析 調査結果レポートはプレーンテキスト、XML、またはHTML⽂書として利⽤可能 • 特定期間の調査結果 • 前回の活動での調査結果

    ⾃動パーティション化のインターフェースと使い⽅ Copyright © 2024, Oracle and/or its affiliates SQL> select message from v$session_longops where opname = 'DBMS_AUTO_PARTITION'; MESSAGE ----------------------------------------------------------- DBMS_AUTO_PARTITION: execution ID 1: 2 out of 5 phases done SQL> select dbms_auto_partition.report_activity(…) from dual; SQL> select dbms_auto_partition.report_last_activity from dual; 98
  94. まとめ 1. Oracle Partitioningの概要 2. パーティション化⽅法 3. パーティション化と索引 4. パーティション・メンテナンス

    5. パフォーマンスと管理性の向上 6. ⾃動パーティション化 Copyright © 2024, Oracle and/or its affiliates 99