묵시적인 형변환을 피하라

SQL 에서 묵시적인 형변환(Implicit Datatype Conversion) 을 피하라.
위와같은 말을 언젠가 들어보았을것이다.
WHERE 절의 묵시적 형변환에 의한 Full table scan 은 잘알려져 있다 .
이문제는 Oracle 10g 부터 SQL 이 PL/SQL내에서 사용된다면 dbms_warning 패키지나 plsql_warnings 기능을 사용하면 식별될수 있다.
아래 예제를 보자.

SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL'; --> warning 가능 활성화

SQL> CREATE TABLE t ( a VARCHAR2(10) ); --> varchar2 타입으로 컬럼생성
Table created.

SQL> CREATE OR REPLACE PROCEDURE p
2 IS
3 BEGIN
4 INSERT INTO t VALUES ( 10 ); --> number 타입으로 insert
5 END p;
6 /

SP2-0804: Procedure created with compilation warnings

SQL> SHO ERR
Errors for PROCEDURE P:

4/26 PLW-07202: bind type would result in conversion away from column type
-->4번째 라인에서 묵시적인 형변환이 일어나고 있음을 알려준다.

그런데 이런 묵시적인 형변환에의한 성능저하가 SQL에만 해당되는 이야기인가?
묵시적인 형변환에 의한 PL/SQL 자체의 성능도 한번쯤 의문을 가져볼 필요가 있다.

아래 예제는 데이터 타입선정을 잘못하면 프로그램이 얼마나 망가질수 있는지를 잘나타내고 있다.

create or replace procedure imp_type_conv_test is
x date;
y varchar2(12) := '01-MAR-03'
t number := dbms_utility.get_time;
begin
for i in 1 .. 1000000 loop
x := y; --> 묵시적인 형변환
end loop; dbms_output.put_line((dbms_utility.get_time-t)'hsec');
end;
/

결과 : 826hsec

create or replace procedure exp_type_conv_test is
x date;
y x%type := to_date('01-MAR-03'); --> date 형으로 선언하고 미리 conversion 함
t number := dbms_utility.get_time;
begin
for i in 1 .. 1000000 loop
x := y; --> 형변환이 일어나지 않음
end loop;
dbms_output.put_line((dbms_utility.get_time-t)'hsec');
end;
/

결과 : 38hsec --> 무려 2200% 가까이 성능이 향상됬다.

결론 :
Implicit Datatype Conversion 은 SQL 의 성능 뿐만아니라 PL/SQL 자체의 성능도 저하시킴을 알수 있다.
명시적으로 TO_DATE, TO_NUMBER 등의 함수를 사용함으로써 좀더 직관적이고
성능면에서도 유리한 프로그램을 개발할수 있다.

참조서적 : Mastering Oracle PL/SQL(Connor McDonald )