by Hayato Matsuura.

UPPERLOWERを使った 大文字・小文字を区別する検索


where句にある大文字小文字の区別を無視すると、 非常にシンプルになります。例えば、以下のように比較の両辺を大文字に合わせることができます。

SELECT first_name, last_name, phone_number
  FROM employees
 WHERE UPPER(last_name) = UPPER('winand')

この場合、検索語あるいはLAST_NAME列の中身が 大文字であろうと小文字であろうと、UPPER関数が一致するように変換してくれます。

注記

大文字小文字を区別しないで一致させるもう一つの方法は、違う「照合順序」を使用することです。SQL ServerとMySQLのデフォルト照合順序は、大文字と小文字を区別しません。

クエリのロジックは完璧に筋が通っていますが、実行計画はそうなっていません。

DB2
Explain Plan
------------------------------------------------------
ID | Operation         |                   Rows | Cost
 1 | RETURN            |                        |  690
 2 |  TBSCAN EMPLOYEES | 400 of 10000 (  4.00%) |  690

Predicate Information
 2 - SARG ( UPPER(Q1.LAST_NAME) = 'WINAND')
Oracle
----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
|  0 | SELECT STATEMENT  |           |   10 |  477 |
|* 1 |  TABLE ACCESS FULL| EMPLOYEES |   10 |  477 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(UPPER("LAST_NAME")='WINAND')
PostgreSQL
                     QUERY PLAN
------------------------------------------------------
 Seq Scan on employees
   (cost=0.00..1722.00 rows=50 width=17)
   Filter: (upper((last_name)::text) = 'WINAND'::text)

我々の古い友人、フルテーブルスキャンのお出ましです。 LAST_NAMEにインデックスがあるにもかかわらず、それは 使われていません。検索が行われるのが、LAST_NAME ではなくUPPER(LAST_NAME)だから です。データベースにとっては、これらは全くの別物なのです。

協力してください

この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。

これは、多くの人が陥りがちな罠です。LAST_NAMEUPPER(LAST_NAME)の関係は見れば分かる通りですが、データ ベースも同じようにそれを「扱う」ことを期待してしまうのです。現実には、オプティマイザからこれを見ると以下のようになります。

SELECT first_name, last_name, phone_number
  FROM employees
 WHERE BLACKBOX(...) = 'WINAND'

UPPER関数は、単なるブラックボックスになって しまうのです。関数のパラメータとその結果の間には、常に成り立つ一般的な関係はないので、関数とパラメータの間にも関係はありません。

ヒント

関数の名前をBLACKBOXに 置き換えてみると、オプティマイザが関数をどう扱うか理解できるでしょう。

コンパイル時評価

オプティマイザは、入力されたパラメータが全て揃う「コンパイル時」に 右辺の式を評価します。しかし、Oracleの実行計画(“Predicate Information” セクション)では、検索語の大文字表現のみを表示します。これは、 コンパイラがコンパイル時に定数を評価する動作に非常によく似ています。

このようなクエリをサポートするには、本来の検索語をカバーする インデックスを作成する必要があります。つまり、LAST_NAME ではなくUPPER(LAST_NAME)に対するインデックスが必要になります。

CREATE INDEX emp_up_name 
    ON employees (UPPER(last_name))

関数や式を含んで定義されたインデックスを一般的に、関数インデックス(Function Based Index、FBI)といいます。インデックスに列のデータを直接コピーするのではなく、関数 インデックスでは、関数をまず適用してから、その結果をインデックスに 入れます。その結果、インデックスは全て大文字の状態で名前を保存します。

上の例のように、インデックスの定義と完全に 同じ表現がSQL文に現れた時にだけ、データベースは関数インデックスを使用できます。実行計画でこれを確認できます。

DB2
Explain Plan
-------------------------------------------------------
ID | Operation            |                 Rows | Cost
 1 | RETURN               |                      |   13
 2 |  FETCH EMPLOYEES     |     1 of 1 (100.00%) |   13
 3 |   IXSCAN EMP_UP_NAME | 1 of 10000 (   .01%) |    6

Predicate Information
 3 - START ( UPPER(Q1.LAST_NAME) = 'WINAND')
      STOP ( UPPER(Q1.LAST_NAME) = 'WINAND')

期待通りの結果を得るために、クエリはWHERE UPPER(last_name) = 'WINAND'(右側にUPPERがない)に変更してあります。 UPPER('winand')を使うと、オプティマイザは全く違う見積もりを してしまい、全体の4%のテーブルが選択されると考えてしまいます。 これにより、オプティマイザはインデックスを無視してTBSCAN してしまいます。なぜこのような動作をするかはフルテーブルスキャンを参照してください。

Oracle
--------------------------------------------------------------
|Id |Operation                   | Name        | Rows | Cost |
--------------------------------------------------------------
| 0 |SELECT STATEMENT            |             |  100 |   41 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |  100 |   41 |
|*2 |  INDEX RANGE SCAN          | EMP_UP_NAME |   40 |    1 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - access(UPPER("LAST_NAME")='WINAND')
PostgreSQL
                       QUERY PLAN
------------------------------------------------------------
Bitmap Heap Scan on employees
  (cost=4.65..178.65 rows=50 width=17)
  Recheck Cond: (upper((last_name)::text) = 'WINAND'::text)
  -> Bitmap Index Scan on emp_up_name
     (cost=0.00..4.64 rows=50 width=0)
     Index Cond: (upper((last_name)::text) = 'WINAND'::text)

これは、第1章に挙げたような、普通のINDEX RANGE SCANです。データベースは、Bツリーを走査し、リーフノード チェーンをたどります。関数インデックスに対する特別な操作やキーワードはありません。

参照

ORMツールは、開発者が知らないうちにUPPERLOWERを使うことがあります。例えばHibernateは、大文字小文字を区別しない検索に対して、暗黙的にLOWERを 付加します。

この実行計画は、前節の実行計画からUPPERを消した ものとはまだ異なっています。行数の見積もりが大きすぎるのです。そもそも、INDEX RANGE SCANで最初に読み出した行よりも多くの行をテーブルから読み出すようにオプティマイザが考えているとしたら、 おかしなことになります。前段のインデックススキャンで40行しか返さないのに、その後テーブルから100行を読み出すとは、どういうことでしょうか? 答えは、そんなことは不可能だということになります。このような矛盾した見積もりは、多くの場合、統計情報に問題があることを示しています。 今回に関しては、新しいインデックスを作成した際にOracleがテーブルの統計情報を更新しなかったことが原因です(Oracleにおける関数インデックスの統計情報を参照)。

Oracleにおける関数インデックスの統計情報

Oracleは、テーブルの統計情報の一部として、列の中で一意な値の数を持っています。その列が複合インデックスの一部で ある場合は、この値は再利用されます。

関数インデックス(FBI)の統計情報は、バーチャル カラムとして、テーブルレベルでも保持されます。Oracleは、 新しいインデックスに対するインデックスの統計情報を自動的に収集しますが(10g以降)、テーブルの 統計情報は更新しません。このため、Oracleのドキュメントでは、 関数インデックスを作成した後には、テーブルの統計情報を更新することを推奨しています。

ファンクション索引(訳注:関数インデックスのこと)の作成後、 DBMS_STATSパッケージを使用して、索引と実表の両方に関する統計情報を 収集します。これらの統計情報によって、Oracleデータベースは索引を使用するタイミングを正しく判断できます。

Oracle SQLリファレンス(14-83ページ)

個人的なおすすめは、これをさらに進めて、インデックスを変更した 時は常に、元になるテーブルとその全てのインデックスに関する統計情報を 更新してしまうことです。しかし、これによる望ましくない副作用もあります。 この作業のためにデータベース管理者(DBA)を調整する必要があるのと、元の統計情報のバックアップを取らねばならないことです。

統計情報の更新後、オプティマイザはより正しい見積もりを算出するようになりました。

Oracle
--------------------------------------------------------------
|Id |Operation                   | Name        | Rows | Cost |
--------------------------------------------------------------
| 0 |SELECT STATEMENT            |             |    1 |    3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |    1 |    3 |
|*2 |  INDEX RANGE SCAN          | EMP_UP_NAME |    1 |    1 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - access(UPPER("LAST_NAME")='WINAND')
PostgreSQL
                      QUERY PLAN
----------------------------------------------------------
 Index Scan using emp_up_name on employees
   (cost=0.00..8.28 rows=1 width=17)
   Index Cond: (upper((last_name)::text) = 'WINAND'::text)

行数の見積が、前の例では50だったのがこの実行計画では1まで減っていることにより、クエリプランナはより単純な Index Scanを選択するようになったことが分かります。

どちらの実行計画でもインデックスが適切に使われていたことから、 今回の例では、実行のパフォーマンス自体は改善されませんでした。しかし、オプティマイザの見積もりを確認しておくのは、どんな時でも大切なことです。 それぞれの操作で処理される行数(カーディナリティの見積もり)は、SQL ServerやPostgreSQLの実行計画にも出てくる、非常に重要な数値です。

ヒント

付録, 「実行計画 では、SQL ServerやPostgreSQLの実行計画での、行数見積もりについて説明しています。

SQL ServerとMySQLはここまで説明したような関数インデックスはサポートしていませんが、代わりに使える計算列あるいは 生成列という機能を提供しています。これを使うには、インデックスを作成する対象のテーブルに、後から計算列を追加します。

MySQL

MySQL 5.7からは、以下のように生成列に インデックスを作ることができます

ALTER TABLE employees
  ADD COLUMN last_name_up VARCHAR(255) AS (UPPER(last_name));
CREATE INDEX emp_up_name ON employees (last_name_up);
SQL Server
ALTER TABLE employees ADD last_name_up AS UPPER(last_name)
CREATE INDEX emp_up_name ON employees (last_name_up)

SQL ServerとMySQLは、インデックス表現が文に現れたら常にインデックスを 使うことができます。シンプルなケースでは、SQL ServerとMySQLは クエリを変えなくてもインデックスを使えることもあります。しかし、インデックス内の新しい列の名前を参照するようクエリを書き換えなければ ならないこともあります。懸念があるときには必ず実行計画を確認しましょう。

著者について

Markus Winandの写真

Markus Winand氏は、開発者がSQLパフォーマンスを改善するお手伝いをしています。彼は、SQL Performance Explainedの 著者でもあり、出張トレーニングhttp://winand.at/での リモート講義も 行っています。

彼の本

カバー『SQLパフォーマンス詳解』

核心をわかりやすく 解説。

Markusから購入します
(送料無料+PDF)

Amazonで購入
(印刷版のみ)

“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
法律上の通知 | 接触 | 無保証 | 商標 | Privacy | CC-BY-NC-ND 3.0 license