技術ブログ
目次
Oracle ACE Proの渡部です。 主要なRDBMS製品を比較します。
立場の表明
ファンクション索引を作成すると、WHERE句にファンクションを用いたクエリで索引を使用できるようになります。これにより、クエリの実行を高速化できる場合があります。
リレーショナルデータベースで索引（インデックス）というと、たいていBツリー索引（Bツリーインデックス）のことですが、Bツリー以外の索引も存在します。
各RDBMS製品の、Bツリー以外の索引の対応状況をまとめました。
Bツリー索引については、以下の記事を参照してください。
主要RDBMS製品の比較 – インデックスの仕組みと物理構造
各RDBMS製品の、ファンクション索引を使用するクエリの実行計画および実行例を示します。
-- ■ 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> 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>
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=#
Database Performance Analyzer (DPA) は、オンプレミス＆クラウドに対応するデータベース性能監視／分析ツールです。
この記事で取り上げたRDBMS製品を含む、非常に多くのデータベース製品／サービスに対応しています。
以下の特徴があり、導入しやすく有用な製品です。
コーソルはDatabase Performance Analyzer (DPA)の一次代理店で、Database Performance Analyzer (DPA)の製品販売を行います。 SIer様、販社様がDatabase Performance Analyzer (DPA)を販売および導入することも可能です。
コーソルはデータベースの技術力を強みとしています。なかでもOracle Database技術力は日本随一です。MySQL、PostgreSQL、MS SQL Serverの資格や実績を持つエンジニアも多数在籍しております。
DPAの導入や監視設定に関する手順をナレッジとして公開しています。評価版をご利用される際の参考にしていただけると幸いです。
7年連続ORACLE MASTER Platinum取得者数No.1！ Oracle Certification Award 2020
コーソルでは、データベース運用を製品とサービスでご支援します。
Database Performance Analyzer （DPA）は、オンプレミスとクラウド上の多くのデータベース製品に対応したデータベース性能管理製品です。低価格であるため、非常に導入しやすいです。
Database Performance Analyzer （DPA）で検出された問題SQLをチューニングする際に、Toad for Oracle / Toad for SQL Serverの SQL Optimizer機能を使用できます。
リモートDBAサービスはDB・運用の専門家がお客様のデータベースに対して 必要な時に必要な対応を行うリモート接続型運用保守サービスです。
データベース運用・保守なら常駐しないリモートDBA
時間制コンサルティングサービスは”必要な時に” ”必要な時間だけ”契約できる 時間契約型のコンサルティングサービスです。
データベース コンサルティングなら時間制コンサルティング
プロフィール
渡部 亮太
・Oracle ACE
・AWS Certified Solutions Architect - Associate
・ORACLE MASTER Platinum Oracle Database 11g, 12c 他多数
カテゴリー
アーカイブ
2024年
2023年
2022年
2021年
2020年
2019年
2018年
2017年
2016年
2015年
2014年
2013年
2012年
2000年