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_NAMEと
UPPER(LAST_NAME)の関係は見れば分かる通りですが、データ
ベースも同じようにそれを「扱う」ことを期待してしまうのです。現実には、オプティマイザからこれを見ると以下のようになります。
SELECT first_name, last_name, phone_number
FROM employees
WHERE BLACKBOX(...) = 'WINAND'UPPER関数は、単なるブラックボックスになって
しまうのです。関数のパラメータとその結果の間には、常に成り立つ一般的な関係はないので、関数とパラメータの間にも関係はありません。
ヒント
関数の名前をBLACKBOXに
置き換えてみると、オプティマイザが関数をどう扱うか理解できるでしょう。
このようなクエリをサポートするには、本来の検索語をカバーする
インデックスを作成する必要があります。つまり、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章1に挙げたような、普通のINDEX RANGE
SCANです。データベースは、Bツリーを走査し、リーフノード
チェーンをたどります。関数インデックスに対する特別な操作やキーワードはありません。
参照
ORMツールは、開発者が知らないうちにUPPERや
LOWERを使うことがあります。例えばHibernateは、大文字小文字を区別しない検索に対して、暗黙的にLOWERを
付加します。
この実行計画は、前節の実行計画からUPPERを消した
ものとはまだ異なっています。行数の見積もりが大きすぎるのです。そもそも、INDEX RANGE
SCANで最初に読み出した行よりも多くの行をテーブルから読み出すようにオプティマイザが考えているとしたら、
おかしなことになります。前段のインデックススキャンで40行しか返さないのに、その後テーブルから100行を読み出すとは、どういうことでしょうか？
答えは、そんなことは不可能だということになります。このような矛盾した見積もりは、多くの場合、統計情報に問題があることを示しています。
今回に関しては、新しいインデックスを作成した際にOracleがテーブルの統計情報を更新しなかったことが原因です(「Oracleにおける関数インデックスの統計情報」を参照)。
統計情報の更新後、オプティマイザはより正しい見積もりを算出するようになりました。
- 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からは、以下のように生成列に インデックスを作ることができます。
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は クエリを変えなくてもインデックスを使えることもあります。しかし、インデックス内の新しい列の名前を参照するようクエリを書き換えなければ ならないこともあります。懸念があるときには必ず実行計画を確認しましょう。
