SQLでは型が暗黙に変換されてしまうことがある。その中でも文字列と数値はやってしまいがちだけど、理解していないと機能的にもパフォーマンス的にも問題が発生する。
具体的にどっちがどっちに変換されるのか見てみよう。
検証はOracle 18c
'1',1
'01',1
'001',1
'123',123
を登録する。
各々、カラムと異なる型で検索する場合と、カラムと同じ型で検索する場合の実行計画を確認してみよう。
この場合は、右辺の検索値ではなく、テーブル内のカラムの値が暗黙の型変換の対象になってしまう。
なので、インデックスが使われなくなる。これはパフォーマンスに大きな影響がある。
その上、'1','01','001'が、すべて1として評価されてしまうため、実際に検索したいことと異なってしまっている可能性もある。
この場合は、右辺の検索値が暗黙の型変換の対象になる。インデックスも使われるために特に問題にはならないことが多いだろう。
'1','01','001'が、すべて1として評価されてしまったことから考えると、その逆ができないということは理解しやすい。
だから、文字列から数値への暗黙変換は発生するけど、その逆は発生しない。
とはいえ、数値のカラムは素直に数値で検索した方がいいだろう。
わざわざ文字列で検索する必要があるとは考えにくい。
最後にカラムと同じ型で実行した場合を見てみよう。
具体的にどっちがどっちに変換されるのか見てみよう。
検証はOracle 18c
varhar2とnumberをカラムに持つテーブルを作成
varhar2とnumberをカラムに持つテーブルを作成して、'1',1
'01',1
'001',1
'123',123
を登録する。
SQL> create table char_number (col_char varchar2(10) not null, col_number number(10) not null);
Table CHAR_NUMBERは作成されました。
SQL> insert into char_number values ('1', 1);
1行挿入しました。
SQL> insert into char_number values ('123', 123);
1行挿入しました。
SQL> insert into char_number values ('01', 1);
1行挿入しました。
SQL> insert into char_number values ('001', 1);
1行挿入しました。
SQL> create unique index pk_char_number on char_number (col_char);
Index PK_CHAR_NUMBERは作成されました。
SQL> create index ind_char_number_col_number on char_number (col_number);
Index IND_CHAR_NUMBER_COL_NUMBERは作成されました。
SQL> alter table char_number add constraint pk_char_number primary key (col_char) using index;
Table CHAR_NUMBERが変更されました。
各々、カラムと異なる型で検索する場合と、カラムと同じ型で検索する場合の実行計画を確認してみよう。
文字列のカラムを数値で検索する場合
SQL> set autotrace on explain
自動トレース有効
実行計画のみを表示します。
SQL> select col_number from char_number where col_char = 1;
COL_NUMBER
_____________
1
1
1
Explain Plan
-----------------------------------------------------------
PLAN_TABLE_OUTPUT
________________________________________________________________________________________________________
Plan hash value: 4249545857
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 60 | 2 (0)| 00:00:01 |
| 1 | VIEW | index$_join$_001 | 3 | 60 | 2 (0)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
| 3 | INDEX FAST FULL SCAN| IND_CHAR_NUMBER_COL_NUMBER | 3 | 60 | 1 (0)| 00:00:01 |
|* 4 | INDEX FAST FULL SCAN| PK_CHAR_NUMBER | 3 | 60 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(ROWID=ROWID)
4 - filter(TO_NUMBER("COL_CHAR")=1)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
この場合は、右辺の検索値ではなく、テーブル内のカラムの値が暗黙の型変換の対象になってしまう。
なので、インデックスが使われなくなる。これはパフォーマンスに大きな影響がある。
その上、'1','01','001'が、すべて1として評価されてしまうため、実際に検索したいことと異なってしまっている可能性もある。
数値のカラムを文字列で検索する場合
SQL> select col_number from char_number where col_number = '1';
COL_NUMBER
_____________
1
1
1
Explain Plan
-----------------------------------------------------------
PLAN_TABLE_OUTPUT
__________________________________________________________________________________________________
Plan hash value: 2512486424
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 39 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IND_CHAR_NUMBER_COL_NUMBER | 3 | 39 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("COL_NUMBER"=1)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
この場合は、右辺の検索値が暗黙の型変換の対象になる。インデックスも使われるために特に問題にはならないことが多いだろう。
結局
これは数値から文字列への暗黙変換は変換先を一意に特定できないからだ。'1','01','001'が、すべて1として評価されてしまったことから考えると、その逆ができないということは理解しやすい。
だから、文字列から数値への暗黙変換は発生するけど、その逆は発生しない。
とはいえ、数値のカラムは素直に数値で検索した方がいいだろう。
わざわざ文字列で検索する必要があるとは考えにくい。
最後にカラムと同じ型で実行した場合を見てみよう。
文字列のカラムを文字列で検索する場合
SQL> select col_number from char_number where col_char = '1';
COL_NUMBER
_____________
1
Explain Plan
-----------------------------------------------------------
PLAN_TABLE_OUTPUT
_________________________________________________________________________________________________
Plan hash value: 4007525316
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CHAR_NUMBER | 1 | 20 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_CHAR_NUMBER | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL_CHAR"='1')
数値のカラムを数値で検索する場合
SQL> select col_number from char_number where col_number = 1;
COL_NUMBER
_____________
1
1
1
Explain Plan
-----------------------------------------------------------
PLAN_TABLE_OUTPUT
__________________________________________________________________________________________________
Plan hash value: 2512486424
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 39 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IND_CHAR_NUMBER_COL_NUMBER | 3 | 39 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("COL_NUMBER"=1)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
0 件のコメント:
コメントを投稿