概要
暗黙変換はあるデータ型から別のデータ型への変換が意味を持つ場合、明示的に指定しなくでもDatabaseは値を自動的に変換する機能です。
ただし、暗黙の型変換はいろいろな問題があるので原則避けるべきです。
主要なDatabase製品は暗黙変換機能あります、Oracleを例にして問題点を検証した。
ラボ環境
Database：
Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production
Table：
CREATE TABLE "TEST_TABLE1"
( "C1" VARCHAR2(20 BYTE)
)
/
CREATE INDEX "INDEX_C1" ON "TEST_TABLE1" ("C1")
/
問題点
問題1．INDEX効かない場合があるので、パフォーマンスが悪くなる
下記の二つのSQLの実行計画を比較した。。
暗黙変換ない場合INDEX効いている
SQL> SELECT T1.* FROM TEST_TABLE1 T1 WHERE T1.C1= '1';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 687607139
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_TABLE1 | 1 | 11 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX_C1 | 2 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C1"='1')
暗黙変換ある場合INDEX効いていない、TABLE ACCESS FULLになっています。
SQL> SELECT T1.* FROM TEST_TABLE1 T1 WHERE T1.C1= 1 ;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2372207557
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_TABLE1 | 1 | 11 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("T1"."C1")=1)
問題2．データによって、ORA-01722エラーなる
データによって暗黙変換できない場合、エラーになる場合があります。
１の例をみると、
SELECT T1.* FROM TEST_TABLE1 T1 WHERE T1.C1= 1
を実行するとき、ORACLEは内部で
SELECT T1.* FROM TEST_TABLE1 T1 WHERE TO_NUMBER(T1.C1)= 1
を実行しています。TO_NUMBERを使っているので、データによってエラーになる可能性があります。
例えば：
C1カラムに全部数字に変換できるデータをINSERTした場合、SQLは正しく実行されます。
SQL> SELECT T1.C1 FROM TEST_TABLE1 T1 ;
no rows selected
SQL> INSERT INTO TEST_TABLE1(C1) VALUES ('1');
1 row created.
SQL> INSERT INTO TEST_TABLE1(C1) VALUES ('2');
1 row created.
SQL> SELECT T1.C1 FROM TEST_TABLE1 T1 WHERE T1.C1= 1;
C1
--------------------
1
数字に変換できないデータをINSERTしてみた場合、ORA-01722 エラー発生しました。
SQL> INSERT INTO TEST_TABLE1(C1) VALUES ('A');
1 row created.
SQL> SELECT T1.C1 FROM TEST_TABLE1 T1 WHERE T1.C1= 1;
ERROR:
ORA-01722: invalid number
no rows selected
問題3．環境設定によって、同じSQL違う結果になる可能性があります。
まず、NLS_DATE_FORMATを設定してSYSDATEをVARCHAR2に定義されたカラムに暗黙変換を使ってデータをINSERTした。
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
Session altered.
SQL> INSERT INTO TEST_TABLE1(C1) VALUES (SYSDATE);
1 row created.
SQL> SELECT T1.C1 FROM TEST_TABLE1 T1;
C1
--------------------
2017-05-15 18:01:19
次にNLS_DATE_FORMATを別の値に変更して同じSQLを実行してみた。
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR HH24:MI:SS';
Session altered.
SQL> INSERT INTO TEST_TABLE1(C1) VALUES (SYSDATE);
1 row created.
SQL> SELECT T1.C1 FROM TEST_TABLE1 T1;
C1
--------------------
2017-05-15 18:01:19
15-MAY-17 18:06:24
まったく同じSQLを実行した結果、設定によって値が異なる可能性があるので、システムは望ましくない。
終わりに
暗黙の型変換機能はたくさんリスクあるので、できるだけ避けるようにSQL記述したほうがいいと思います。
そのた
Oracleの公式見解
1. 明示的なデータ型変換ファンクションを使用すると、SQL文がわかりやすくなります。
2. 暗黙的なデータ型変換(特に列値のデータ型が定数に変換される場合)は、パフォーマンスに悪影響を及ぼす可能性があります。
3. 暗黙的な変換はその変換が行われるコンテキストに依存し、どんな場合でも同様に機能するとはかぎりません。たとえば、日時値からVARCHAR2型へ値を暗黙的に変換すると、NLS_DATE_FORMATパラメータに指定されている値によっては、予期しない年が戻される場合があります。
4. 暗黙的な変換のアルゴリズムは、ソフトウェア・リリースやOracle製品の変更によって変更されることがあります。明示的な変換を指定しておくと、その動作は将来的にも確実になります。
5. 索引式で暗黙的なデータ型変換が発生した場合、その索引が変換前のデータ型に対して定義されているために、Oracle Databaseがその索引を使用しないことがあります。これは、パフォーマンスに悪影響を与えるおそれがあります。