UPPERとLOWERを使った 大文字・小文字を区別する検索


Applies to
DB2Yes
MySQLNo
OracleYes
PostgreSQLYes
SQL ServerYes

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)だから です。データベースにとっては、これらは全くの別物 なのです。

このウェブサイトにぴったりのカップは僕たちのショップにあります。
#見た目もいい感じだし、ここでの僕の仕事を支えてくれています

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

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

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

Tweet this tip

ヒント

関数の名前を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データベースは索引を 使用するタイミングを正しく判断できます。

個人的なおすすめは、これをさらに進めて、インデックスを変更した 時は常に、元になるテーブルとその全てのインデックスに関する統計情報を 更新してしまうことです。しかし、これによる望ましくない副作用もあります。 この作業のためにデータベース管理者(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を選択するようになったことが 分かります。

注記

式やカラムグループに対する、いわゆる「拡張統計情報」の 機能は、Oracle 11gでの登場です。

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

ヒント

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

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

MySQL

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

例2.3

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は クエリを変えなくてもインデックスを使えることもあります。 しかし、インデックス内の新しい列の名前を参照するようクエリを書き換えなければ ならないこともあります。懸念があるときには必ず実行計画を確認しましょう。

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