株式会社コーソル

コーソル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サービス

著者について

ファンクション索引 / ファンクション インデックス

ファンクション索引を作成すると、WHERE句にファンクションを用いたクエリで索引を使用できるようになります。これにより、クエリの実行を高速化できる場合があります。

特徴

  • RDBMS製品により名称が異なります。
    • Oracle: ファンクション索引
    • MySQL: Functional Key Parts、関数キー部品、機能キー部品
    • PostgreSQL: 式インデックス
  • 索引エントリに、式の計算結果が含まれます。
  • 式の計算は、表データ変更時に実行されます。
    • 索引アクセス時（≒表データ参照時）に、式の計算は実行されない

Bツリー以外の索引

リレーショナルデータベースで索引（インデックス）というと、たいていBツリー索引（Bツリーインデックス）のことですが、Bツリー以外の索引も存在します。

各RDBMS製品の、Bツリー以外の索引の対応状況をまとめました。

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

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

実行計画および実行例

各RDBMS製品の、ファンクション索引を使用するクエリの実行計画および実行例を示します。

Oracle

-- ■ CUST_LAST_NAME列に索引を作成します。
SQL> CREATE INDEX CUST_LASTNAME_IX ON CUSTOMERS(CUST_LAST_NAME);

-- ■ CUST_LAST_NAME列にUPPERファンクションを使用した条件を指定したSQLを実行すると、索引は使用されません。
SQL> SELECT *
  2     FROM CUSTOMERS WHERE UPPER(CUST_LAST_NAME) = 'AARON';
(略)

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b7v4b1t3nx76c, child number 0
-------------------------------------
SELECT *    FROM CUSTOMERS WHERE UPPER(CUST_LAST_NAME) = 'AARON'

Plan hash value: 2008213504

-----------------------------------------------------------------------------------------
| Id  | Operation         | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |      1 |        |     79 |00:00:00.01 |    1546 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS |      1 |    555 |     79 |00:00:00.01 |    1546 |
-----------------------------------------------------------------------------------------

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

   1 - filter(UPPER("CUST_LAST_NAME")='AARON')

18 rows selected.

-- ■ 当然ですが、CUST_LAST_NAME列にUPPERファンクションを使用しない条件を指定したSQLを実行すると、索引は使用されます。
SQL> SELECT *
   FROM CUSTOMERS WHERE CUST_LAST_NAME = 'Aaron';

(略)
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  59mvhkrrk0z2h, child number 0
-------------------------------------
SELECT *    FROM CUSTOMERS WHERE CUST_LAST_NAME = 'Aaron'

Plan hash value: 3574792607

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                  |      1 |        |     79 |00:00:00.01 |      13 |      1 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS        |      1 |     61 |     79 |00:00:00.01 |      13 |      1 |
|*  2 |   INDEX RANGE SCAN                  | CUST_LASTNAME_IX |      1 |     61 |     79 |00:00:00.01 |       3 |      1 |
---------------------------------------------------------------------------------------------------------------------------

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

   2 - access("CUST_LAST_NAME"='Aaron')

19 rows selected.

-- ■ CUST_LAST_NAME列に作成した索引を削除し、UPPERファンクションを適用したファンクション索引を作成します。
SQL> DROP INDEX CUST_LASTNAME_IX;
SQL> CREATE INDEX CUST_LASTNAME_FIX ON CUSTOMERS(UPPER(CUST_LAST_NAME));

-- ■ CUST_LAST_NAME列にUPPERファンクションを使用した条件を指定したSQLを再度実行すると、作成したファンクション索引は使用されます。

SQL> SELECT *
  2     FROM CUSTOMERS WHERE UPPER(CUST_LAST_NAME) = 'AARON';
(略)

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b7v4b1t3nx76c, child number 0
-------------------------------------
SELECT *    FROM CUSTOMERS WHERE UPPER(CUST_LAST_NAME) = 'AARON'

Plan hash value: 3195375111

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |      1 |        |     79 |00:00:00.01 |      13 |      1 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS         |      1 |    555 |     79 |00:00:00.01 |      13 |      1 |
|*  2 |   INDEX RANGE SCAN                  | CUST_LASTNAME_FIX |      1 |    222 |     79 |00:00:00.01 |       3 |      1 |
----------------------------------------------------------------------------------------------------------------------------

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

   2 - access("CUSTOMERS"."SYS_NC00024$"='AARON')

MySQL

  • MySQLでは、ファンクション索引のことを"Functional Key Parts"、「関数キー部品」、「機能キー部品」と呼びます。
  • MySQLは8.0.13 (2018-10)以降で、ファンクション索引に対応しています。

mysql> CREATE INDEX ix_cust_lname ON CUSTOMERS(CUST_LAST_NAME);
Query OK, 0 rows affected (1.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN FORMAT=TREE
    -> SELECT * FROM CUSTOMERS WHERE CUST_LAST_NAME = 'Aaron';
+-------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                         |
+-------------------------------------------------------------------------------------------------+
| -> Index lookup on CUSTOMERS using ix_cust_lname (CUST_LAST_NAME='Aaron')  (cost=27.6 rows=79)
 |
+-------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN FORMAT=TREE
    -> SELECT * FROM CUSTOMERS WHERE UPPER(CUST_LAST_NAME) = 'AARON';
+----------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (upper(CUSTOMERS.CUST_LAST_NAME) = 'AARON')  (cost=5484 rows=50910)
    -> Table scan on CUSTOMERS  (cost=5484 rows=50910)
 |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE INDEX fix_cust_lname ON CUSTOMERS((upper(CUST_LAST_NAME)));
Query OK, 0 rows affected (1.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN FORMAT=TREE
    -> SELECT * FROM CUSTOMERS WHERE UPPER(CUST_LAST_NAME) = 'AARON';
+---------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                 |
+---------------------------------------------------------------------------------------------------------+
| -> Index lookup on CUSTOMERS using fix_cust_lname (upper(CUST_LAST_NAME)='AARON')  (cost=27.6 rows=79)
 |
+---------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql>

PostgreSQL

  • PostgreSQLでは、ファンクション索引のことを「式インデックス」と呼びます。
    • ファンクション索引で、ファンクションの使用は必須ではありませんので、「式インデックス」が"最も意味的に適切な名前付け"に思えます。

sh=# EXPLAIN
sh-# SELECT * FROM CUSTOMERS WHERE CUST_LAST_NAME = 'Aaron';
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Bitmap Heap Scan on customers  (cost=4.78..219.72 rows=63 width=220)
   Recheck Cond: ((cust_last_name)::text = 'Aaron'::text)
   ->  Bitmap Index Scan on ix_cust_lname  (cost=0.00..4.76 rows=63 width=0)
         Index Cond: ((cust_last_name)::text = 'Aaron'::text)
(4 rows)

sh=# EXPLAIN
sh-# SELECT * FROM CUSTOMERS WHERE UPPER(CUST_LAST_NAME) = 'AARON';
                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on customers  (cost=0.00..2704.50 rows=278 width=220)
   Filter: (upper((cust_last_name)::text) = 'AARON'::text)
(2 rows)

sh=# CREATE INDEX fix_cust_lname ON CUSTOMERS(upper(CUST_LAST_NAME));
CREATE INDEX
sh=# EXPLAIN
sh-# SELECT * FROM CUSTOMERS WHERE UPPER(CUST_LAST_NAME) = 'AARON';
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Bitmap Heap Scan on customers  (cost=6.44..757.60 rows=278 width=220)
   Recheck Cond: (upper((cust_last_name)::text) = 'AARON'::text)
   ->  Bitmap Index Scan on fix_cust_lname  (cost=0.00..6.38 rows=278 width=0)
         Index Cond: (upper((cust_last_name)::text) = 'AARON'::text)
(4 rows)

sh=#

構文の比較

「主要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 他多数

カテゴリー

アーカイブ

一覧へ戻る