暗黙の型変換の問題点検証 (Oracle環境)

#概要
暗黙変換はあるデータ型から別のデータ型への変換が意味を持つ場合、明示的に指定しなくでも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がその索引を使用しないことがあります。これは、パフォーマンスに悪影響を与えるおそれがあります。

https://docs.oracle.com/cd/E16338_01/server.112/b56299/sql_elements002.htm