◇INDEXを使いたい！！
読者の方から質問のメールを受けました。
普段、このようなことがないので非常に嬉しいです♪
↓以下、いただいたメールより抜粋。
-------------------------------------------------------------------------------------------
SQLのWhere句に「||（パイプ）」を使った連結文字列を使用した場合にインデックスが効きません。
本などで調べると「||」はインデックスが効かない、と確かに記述してあるのですが、対処法とかありますか？
FunctionINDEXで「||」で連結させたキーを設定できるのかな？と思って試してみたのですがエラーがでてインデックス作成ができませんでした。ほかにいい方法があれば教えてください。
tblA
+---------+---------+---------+---------+
|A_1(CHAR)|A_2(CHAR)|A_3(CHAR)|A_4(CHAR)|
+---------+---------+---------+---------+
| H1601| 01| AAA| 123|
| H1601| 15| BBB| 123|
| H1602| 03| CCC| 123|
| H1603| 18| DDD| 123|
| H1607| 24| EEE| 123|
| H1607| 31| FFF| 123|
+---------+---------+---------+---------+
上記のようなテーブルがあったとして、以下のようなSQLを実行したいのですが・・・。
SELECT * FROM tblA WHERE A_1 || A_2 > 'H160301'
今、インデックスはA_1だけのものとA_1,A_2のものの2パターン作っているのですが、トレースで確認してもFULL検索になっています。
-------------------------------------------------------------------------------------------
なるほど。
確かに、このSQLだとINDEXを使えないなぁ。
では、ちょっと対策を考えてみよう。
◇検証環境構築
検証環境：
RDBMS:ORACLE10.1.0
OS:WindowsXP
OptimizeMode=ALL_ROWS
（統計情報未取得）
--TABLEの作成
create table tblA (A_1 CHAR(5),A_2 CHAR(2),A_3 CHAR(3),A_4 CHAR(3));
--RECORDの追加
insert into tblA values ('H1601','01','AAA','123');
insert into tblA values ('H1601','15','BBB','123');
insert into tblA values ('H1602','03','CCC','123');
insert into tblA values ('H1603','18','DDD','123');
insert into tblA values ('H1607','24','EEE','123');
insert into tblA values ('H1607','31','FFF','123');
commit;
--INDEXの作成
create index ind_tblA_1 on tblA(A_1);
create index ind_tblA_2 on tblA(A_1,A_2);
◇問題
さて、ここで問題。
要件を満たしつつINDEXを使用した検索にするには、どうしたら良いか？
◇正解への道のり
最初、このSQLを見た時、
SELECT * FROM tblA WHERE A_1 > 'H1603' and A_2 > '01'
で、いけるんじゃないか？？
なーんだ、簡単簡単
と思った（間違い）
よーく見るとCOLUMN A_1は年度と月、COLUMN A_2は日付を示していることがわかる。
つまり要件は
「指定した日付以降のデータを取得する」
ということになる。
もし、
SELECT * FROM tblA WHERE A_1 > 'H1603' and A_2 > '01'
と、やってしまうと本当はH16/03/01以降のデータを抽出したいはずなのにH16/04/01以降のデータしか抽出できない。
で、どうするか？？
考え方を、ちょっと変えてみる。
H16/03/01以降というのは
・H16/03/02～H16/03/31
と
・H16/04/01～
の２つに分割することができる。
ということは、つまり
・A_1 = 'H1603' and A_2 > '01'
・A_1 > 'H1603'
の２つの条件のうち、どちらかを満たすという式にすれば良いワケだ。
SQLにすると以下のようになる。
SELECT *
FROM tblA
WHERE (A_1 > 'H1603')
or (A_1 = 'H1603' and A_2 > '01')
これが正解。
条件式を２つに分割して要件を満たす。
これを「困難の分割」と呼ぼう。（探偵学園Ｑ風）
◇実行計画を確認してみよう！
では、作成したSQLの実行計画を確認してみよう。
比較の為、まずは変更前のSQLから。
SQL> SELECT * FROM tblA WHERE A_1 || A_2 > 'H160301';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=51)
1 0 TABLE ACCESS (FULL) OF 'TBLA' (TABLE) (Cost=2 Card=1 Bytes=51)
いや、もう気持ち良いくらいTABLE FULL SCANが出てる。
では、続いて修正したSQLの確認。
SQL> SELECT *
2 FROM tblA
3 WHERE (A_1 > 'H1603')
4 or (A_1 = 'H1603' and A_2 > '01');
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=3 Bytes=51)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TBLA' (TABLE) (Cost=2 Card=3 Bytes=51)
2 1 INDEX (RANGE SCAN) OF 'IND_TBLA_2' (INDEX) (Cost=1 Card=3)
こちらは、ちゃんとINDEX RANGE SCANになっている。
ちなみにRULE BASEの場合も確認してみた。
SQL> SELECT /*+ RULE */ *
2 FROM tblA
3 WHERE (A_1 > 'H1603')
4 or (A_1 = 'H1603' and A_2 > '01');
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 CONCATENATION
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TBLA' (TABLE)
3 2 INDEX (RANGE SCAN) OF 'IND_TBLA_1' (INDEX)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'TBLA' (TABLE)
5 4 INDEX (RANGE SCAN) OF 'IND_TBLA_2' (INDEX)
RBOの場合、２つのINDEXを使い分けて結果をCONCATENATEしているようだ。
◇他に方法は無いのかな？？
とりあえずは解決したのだが、他に方法は無いのだろうか？？
思いつく方法を試してみる。
・||の代わりに+を使ったらどうだろう？？
プログラム言語によっては+を使用することで文字列の結合をしてくれる。
SQLでは、どうなるのか試してみよう。
SQL> SELECT * FROM tblA WHERE A_1 + A_2 > 'H160301';
SELECT * FROM tblA WHERE A_1 + A_2 > 'H160301'
*
ERROR at line 1:
ORA-01722: invalid number
ん？
エラーが出てしまった。
エラーの内容を調べてみよう。
エラー名
数値が無効です。
原因
数値列が無効であるため、文字列から数値への変換は失敗しました。
処置
関数または式の中の文字列を確認してください。
あー、どうやらA_1とA_2を文字列から数値型に変換して計算しようとしているようだ。
A_1の値は'H1603'なので数値型に変換できない。
まぁ、仮にA_1に変換できる値が入っていたとしても期待していた結果は得られない。
・||の代わりに&を使ったらどうだろう？？
プログラム言語によっては&を使用することで文字列の結合をしてくれる。
SQLでは、どうなるのか試してみよう。
SQL> SELECT * FROM tblA WHERE A_1 & A_2 > 'H160301';
Enter value for a_2:
おや？
なんか聞かれてきた。
どうやら& A_2が変数として認識されてしまったようだ。
これもダメだな。。。
誰か良い方法を知ってたら教えてください。
読者の方から質問のメールを受けました。
普段、このようなことがないので非常に嬉しいです♪
↓以下、いただいたメールより抜粋。
-------------------------------------------------------------------------------------------
SQLのWhere句に「||（パイプ）」を使った連結文字列を使用した場合にインデックスが効きません。
本などで調べると「||」はインデックスが効かない、と確かに記述してあるのですが、対処法とかありますか？
FunctionINDEXで「||」で連結させたキーを設定できるのかな？と思って試してみたのですがエラーがでてインデックス作成ができませんでした。ほかにいい方法があれば教えてください。
tblA
+---------+---------+---------+---------+
|A_1(CHAR)|A_2(CHAR)|A_3(CHAR)|A_4(CHAR)|
+---------+---------+---------+---------+
| H1601| 01| AAA| 123|
| H1601| 15| BBB| 123|
| H1602| 03| CCC| 123|
| H1603| 18| DDD| 123|
| H1607| 24| EEE| 123|
| H1607| 31| FFF| 123|
+---------+---------+---------+---------+
上記のようなテーブルがあったとして、以下のようなSQLを実行したいのですが・・・。
SELECT * FROM tblA WHERE A_1 || A_2 > 'H160301'
今、インデックスはA_1だけのものとA_1,A_2のものの2パターン作っているのですが、トレースで確認してもFULL検索になっています。
-------------------------------------------------------------------------------------------
なるほど。
確かに、このSQLだとINDEXを使えないなぁ。
では、ちょっと対策を考えてみよう。
◇検証環境構築
検証環境：
RDBMS:ORACLE10.1.0
OS:WindowsXP
OptimizeMode=ALL_ROWS
（統計情報未取得）
--TABLEの作成
create table tblA (A_1 CHAR(5),A_2 CHAR(2),A_3 CHAR(3),A_4 CHAR(3));
--RECORDの追加
insert into tblA values ('H1601','01','AAA','123');
insert into tblA values ('H1601','15','BBB','123');
insert into tblA values ('H1602','03','CCC','123');
insert into tblA values ('H1603','18','DDD','123');
insert into tblA values ('H1607','24','EEE','123');
insert into tblA values ('H1607','31','FFF','123');
commit;
--INDEXの作成
create index ind_tblA_1 on tblA(A_1);
create index ind_tblA_2 on tblA(A_1,A_2);
◇問題
さて、ここで問題。
要件を満たしつつINDEXを使用した検索にするには、どうしたら良いか？
◇正解への道のり
最初、このSQLを見た時、
SELECT * FROM tblA WHERE A_1 > 'H1603' and A_2 > '01'
で、いけるんじゃないか？？
なーんだ、簡単簡単
と思った（間違い）
よーく見るとCOLUMN A_1は年度と月、COLUMN A_2は日付を示していることがわかる。
つまり要件は
「指定した日付以降のデータを取得する」
ということになる。
もし、
SELECT * FROM tblA WHERE A_1 > 'H1603' and A_2 > '01'
と、やってしまうと本当はH16/03/01以降のデータを抽出したいはずなのにH16/04/01以降のデータしか抽出できない。
で、どうするか？？
考え方を、ちょっと変えてみる。
H16/03/01以降というのは
・H16/03/02～H16/03/31
と
・H16/04/01～
の２つに分割することができる。
ということは、つまり
・A_1 = 'H1603' and A_2 > '01'
・A_1 > 'H1603'
の２つの条件のうち、どちらかを満たすという式にすれば良いワケだ。
SQLにすると以下のようになる。
SELECT *
FROM tblA
WHERE (A_1 > 'H1603')
or (A_1 = 'H1603' and A_2 > '01')
これが正解。
条件式を２つに分割して要件を満たす。
これを「困難の分割」と呼ぼう。（探偵学園Ｑ風）
◇実行計画を確認してみよう！
では、作成したSQLの実行計画を確認してみよう。
比較の為、まずは変更前のSQLから。
SQL> SELECT * FROM tblA WHERE A_1 || A_2 > 'H160301';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=51)
1 0 TABLE ACCESS (FULL) OF 'TBLA' (TABLE) (Cost=2 Card=1 Bytes=51)
いや、もう気持ち良いくらいTABLE FULL SCANが出てる。
では、続いて修正したSQLの確認。
SQL> SELECT *
2 FROM tblA
3 WHERE (A_1 > 'H1603')
4 or (A_1 = 'H1603' and A_2 > '01');
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=3 Bytes=51)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TBLA' (TABLE) (Cost=2 Card=3 Bytes=51)
2 1 INDEX (RANGE SCAN) OF 'IND_TBLA_2' (INDEX) (Cost=1 Card=3)
こちらは、ちゃんとINDEX RANGE SCANになっている。
ちなみにRULE BASEの場合も確認してみた。
SQL> SELECT /*+ RULE */ *
2 FROM tblA
3 WHERE (A_1 > 'H1603')
4 or (A_1 = 'H1603' and A_2 > '01');
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 CONCATENATION
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TBLA' (TABLE)
3 2 INDEX (RANGE SCAN) OF 'IND_TBLA_1' (INDEX)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'TBLA' (TABLE)
5 4 INDEX (RANGE SCAN) OF 'IND_TBLA_2' (INDEX)
RBOの場合、２つのINDEXを使い分けて結果をCONCATENATEしているようだ。
◇他に方法は無いのかな？？
とりあえずは解決したのだが、他に方法は無いのだろうか？？
思いつく方法を試してみる。
・||の代わりに+を使ったらどうだろう？？
プログラム言語によっては+を使用することで文字列の結合をしてくれる。
SQLでは、どうなるのか試してみよう。
SQL> SELECT * FROM tblA WHERE A_1 + A_2 > 'H160301';
SELECT * FROM tblA WHERE A_1 + A_2 > 'H160301'
*
ERROR at line 1:
ORA-01722: invalid number
ん？
エラーが出てしまった。
エラーの内容を調べてみよう。
エラー名
数値が無効です。
原因
数値列が無効であるため、文字列から数値への変換は失敗しました。
処置
関数または式の中の文字列を確認してください。
あー、どうやらA_1とA_2を文字列から数値型に変換して計算しようとしているようだ。
A_1の値は'H1603'なので数値型に変換できない。
まぁ、仮にA_1に変換できる値が入っていたとしても期待していた結果は得られない。
・||の代わりに&を使ったらどうだろう？？
プログラム言語によっては&を使用することで文字列の結合をしてくれる。
SQLでは、どうなるのか試してみよう。
SQL> SELECT * FROM tblA WHERE A_1 & A_2 > 'H160301';
Enter value for a_2:
おや？
なんか聞かれてきた。
どうやら& A_2が変数として認識されてしまったようだ。
これもダメだな。。。
誰か良い方法を知ってたら教えてください。