株式会社コーソル

コーソルDatabaseエンジニアのブログ

技術ブログ

パーティション索引 – Oracle,MySQL,PostgreSQLの比較

Oracle ACE Proの渡部です。 主要なRDBMS製品を比較します。

  • 大枠を整理することが最大の目的です。細かい例外事項は適宜記載を割愛しています。
  • この記事の対象製品およびバージョンは以下のとおりですが、バージョン依存性は比較的少ないはずです。
    • Oracle 19c (19.22以降)
    • MySQL 8.0.37以降
    • PostgreSQL 16以降
  • 諸所の事情により、2023年以前の記事とは異なり、一旦SQL Serverを記載対象外としました。
    • SQL Serverについての記載をご要望される場合は、https://x.com/wrcsus4 にリクエストくださいませ。
  • 時間ができた時に随時追記予定です。
  • もし誤りを見つけた場合は、優しく教えていただけると嬉しいです。→ https://x.com/wrcsus4 or ryota.watabe at cosol dot jp

「主要RDBMS製品の比較」ページ一覧

立場の表明

  • コーソルはデータベース関連製品の販売およびプロフェッショナルサービス提供を行っている営利企業です。
  • https://cosol.jp にある全てのコンテンツは、情報提供に加えて、コーソルの認知度向上、コーソルの営利活動の促進を目的としています。

Database Performance Analyzer DPA

コーソルのDbvisitサービス

著者について

パーティション索引 / インデックスのパーティション化

表と同様に索引もパーティション化できます。

索引をパーティション化する方法として、ローカルとグローバルがあります。

Oracleはローカル索引とグローバル索引に対応していますが、MySQLとPostgreSQLはローカル索引にのみ対応しています。

実行計画 - Oracle

ローカル索引を使用した実行計画と、グローバル索引を使用した実行計画を以下に示します。

ローカル索引を使用した実行計画

SELECT * FROM sales1 WHERE order_id = 50

Plan hash value: 622186933

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |            |      1 |        |       |     2 (100)|          |       |       |      1 |00:00:00.01 |       3 |
|   1 |  PARTITION RANGE SINGLE                    |            |      1 |      1 |    12 |     2   (0)| 00:00:01 |     1 |     1 |      1 |00:00:00.01 |       3 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1         |      1 |      1 |    12 |     2   (0)| 00:00:01 |     1 |     1 |      1 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN                        | LIX_T1_ORD |      1 |      1 |       |     1   (0)| 00:00:01 |     1 |     1 |      1 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ORDER_ID"=50)
  • 表T1のパーティション単位はorder_id列であり、索引LIX_T1_ORDのパーティション単位と同じです。
  • 検索条件に order_id = 50 が使用されているため、索引と表の両方についてパーティションプルーニングが機能しています。
    • 索引へのアクセスと、表へのアクセスの両方について Pstart=1 かつ、Pstop =1

グローバル索引を使用した実行計画

SELECT * FROM t1 WHERE cust_id = 1001

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |            |      1 |        |       |     2 (100)|          |       |       |      3 |00:00:00.01 |       5 |
|   1 |  PARTITION HASH SINGLE                      |            |      1 |      1 |    16 |     2   (0)| 00:00:01 |     4 |     4 |      3 |00:00:00.01 |       5 |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T1         |      1 |      1 |    16 |     2   (0)| 00:00:01 | ROWID | ROWID |      3 |00:00:00.01 |       5 |
|*  3 |    INDEX RANGE SCAN                         | GIX_T1_CST |      1 |      1 |       |     1   (0)| 00:00:01 |     4 |     4 |      3 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CUST_ID"=1001)
  • 表のパーティション単位はorder_id列であり、索引GIX_T1_CSTのパーティション単位と異なります。索引GIX_T1_CSTのパーティション単位はcust_id列です。
  • 検索条件に cust_id = 1001 が使用されているため、索引についてパーティションプルーニングが機能しています。
  • 表についてはパーティションプルーニングが機能していません。
    • Pstart=ROWID, Pstop=ROWID

部分索引 / パーシャル インデックス (Oracle)

Oracleは12.2以降で、パーティションの一部を索引対象外とする部分索引（パーシャル インデックス）に対応しています。

索引不要なパーティションに対して、索引を作成しないことで、 索引のメンテナンスコストを下げることができます。

各製品における様々な索引の対応状況

リレーショナルデータベースで索引（インデックス）というと、たいていBツリー索引（Bツリーインデックス）のことです。当然ながら、今回取り上げたRDBMS製品はBツリー索引に対応しています。

Bツリー索引については、以下の記事を参照してください。

主要RDBMS製品の比較 – インデックスの仕組みと物理構造

しかし、リレーショナルデータベースにはBツリー以外の索引も存在します。 各RDBMS製品の、Bツリー以外の索引の対応状況は以下のとおりです。

「主要RDBMS製品の比較」ページ一覧

[PR] オンプレミス＆クラウドのマルチDB製品に対応した性能管理ツールDPA

Database Performance Analyzer (DPA) は、オンプレミス＆クラウドに対応するデータベース性能監視／分析ツールです。

Database Performance Analyzer DPA

この記事で取り上げたRDBMS製品を含む、非常に多くのデータベース製品／サービスに対応しています。

  • Oracle Database
  • MS SQL Server
  • Sybase SAP ASE
  • IBM Db2
  • MySQL / MariaDB / Percona Server for MySQL
  • PostgreSQL / Enterprise DB
  • AWS
    • Amazon RDS for Oracle Database / SQL Server / MySQL / MariaDB / PostgreSQL
    • Amazon Aurora for MySQL / PostgreSQL
  • Azure
    • Azure SQL Database
    • Azure SQL Managed Instance
    • Azure SQL for PostgreSQL
    • Azure Database for MySQL / MariaDB
  • Google Cloud
    • Google Cloud SQL for MySQL / PostgreSQL / SQL Server

以下の特徴があり、導入しやすく有用な製品です。

  • 非常に低価格。課金単位は監視インスタンスの数で、1インスタンス14.7万円/年から（2023年1月時点）。
  • インストールが容易。DBサーバへのエージェント導入は不要。
  • オンラインデモサイトですぐに使用感を確認可能 → https://cosol.jp/techdb/2022/08/dpa_online_demo/
  • 待機時間を基礎とする性能分析（近年主流の性能分析メソッド）
  • 機械学習アルゴリズムに基づく異常検知機能(Anomaly Detection)

なぜコーソルからDatabase Performance Analyzer (DPA)を購入すべきなのか

コーソルはDatabase Performance Analyzer (DPA)の一次代理店で、Database Performance Analyzer (DPA)の製品販売を行います。 SIer様、販社様がDatabase Performance Analyzer (DPA)を販売および導入することも可能です。

Database Performance Analyzer DPA

コーソルはデータベースの技術力を強みとしています。なかでもOracle Database技術力は日本随一です。MySQL、PostgreSQL、MS SQL Serverの資格や実績を持つエンジニアも多数在籍しております。

独自のDPAナレッジを公開

DPAの導入や監視設定に関する手順をナレッジとして公開しています。評価版をご利用される際の参考にしていただけると幸いです。

多数のOracle関連書籍を執筆

ORACLE MASTER Platinum取得者数 No.1

  • 単年度ORACLE MASTER Platinum取得者数7年連続No.1

7年連続ORACLE MASTER Platinum取得者数No.1！ Oracle Certification Award 2020

[PR] コーソルのデータベース運用関連製品とサービス

コーソルでは、データベース運用を製品とサービスでご支援します。

Database Performance Analyzer （DPA）

Database Performance Analyzer （DPA）は、オンプレミスとクラウド上の多くのデータベース製品に対応したデータベース性能管理製品です。低価格であるため、非常に導入しやすいです。

自動SQLチューニング機能を持つToad

Database Performance Analyzer （DPA）で検出された問題SQLをチューニングする際に、Toad for Oracle / Toad for SQL Serverの SQL Optimizer機能を使用できます。

リモートDBAサービス

リモートDBAサービスはDB・運用の専門家がお客様のデータベースに対して 必要な時に必要な対応を行うリモート接続型運用保守サービスです。

データベース運用・保守なら常駐しないリモートDBA

時間制コンサルティングサービス

時間制コンサルティングサービスは”必要な時に” ”必要な時間だけ”契約できる 時間契約型のコンサルティングサービスです。

データベース コンサルティングなら時間制コンサルティング

関連記事

プロフィール

On7tWW6m1Ul4

渡部 亮太

・Oracle ACE
・AWS Certified Solutions Architect - Associate
・ORACLE MASTER Platinum Oracle Database 11g, 12c 他多数

カテゴリー

アーカイブ

一覧へ戻る