오라클에서 is_number, is_date 함수 사용하기

지인에게서 전화가 오다
지인 : 데이터를 체크해야 하는데 오라클에 is_number, is_date 함수가 없어서 데이터를 체크하기가 불편합니다.
          데이터를 오라클에서 가져와서 자바에서 체크하고 있습니다. 그러다 보니 너무 느립니다.
필자 : 그럴 필요 없습니다.
지인 : 물론 External Function을 사용하면 자바를 사용하여 오라클에 함수를 생성할수도 있겠지요.
필자 : 그냥 PL/SQL 로 하시면 됩니다.
지인 : 네?

무서운 일이다. 전체 데이터를 Network를 타고 가져와서 자바로 체크하다니... Network I/O 가 엄청 날것이다.          

오라클에서 제공하는 함수가 없다
"오라클에서 is_number, is_date 함수가 없어서 데이터를 체크하기가 불편하다" 이말은 옳다. 하지만 오라클에서 체크함수를 제공하지 않는 이유는 아마도 개발자가 너무도 쉽게 만들 수 있어서 그런 것이 아닐까?
is_number, is_date 함수를 직접 만들어 보자.

 

CREATE OR REPLACE FUNCTION is_number(v_str_number IN varchar2)

RETURN NUMBER

IS  /* 데이터가 number 형인지 검사하는 함수임. 1 이 나오면 NUMBER 형임 */

     V_NUM NUMBER;

BEGIN

  V_NUM := TO_NUMBER(v_str_number);

  RETURN 1;

EXCEPTION

  WHEN OTHERS THEN RETURN 0    ;

END;

CREATE OR REPLACE FUNCTION is_date(v_str_date IN varchar2, V_FORMAT IN VARCHAR2 DEFAULT 'YYYYMMDD')

RETURN NUMBER

IS   /* 데이터가 DATE 형인지 검사하는 함수임. 1 이 나오면 DATE 형임 */

     V_DATE DATE;

BEGIN

  V_DATE := TO_DATE(v_str_date, V_FORMAT);

  RETURN 1;   

EXCEPTION

  WHEN OTHERS THEN RETURN 0    ;

END; 

너무나 쉽게 생성 되었다. 그럼 이제 사용해보자.

함수사용법

select  is_number('abcd'), is_number('1234'),

        is_date('20090230'), is_date('20090228')

  from dual ;

 

결과: 

IS_NUMBER('ABCD') IS_NUMBER('1234') IS_DATE('20090230') IS_DATE('20090228')

----------------- ----------------- ------------------- -------------------

                0                 1                   0                   1

1 row selected.

number 형 에서 벗어나는 데이터와 date 형 에서 벗어나는 데이터를 가려 내었다. 타 DBMS 에서 사용할 수 있는 함수와 기능이 똑같다. 이렇게 해서 개발자의 문제가 일시적으로 해결되었다.

-------------------------------------------------------------------------------------------------------------------------

문제는 성능이다
다음날 다시 전화가 왔다. 일회성이 아닌 지속적으로 데이터를 체크해야 하는데 이전보다는 빨라졌지만 여전히 성능이 느리다는 것이었다. 이제부터 함수의 성능에 대해 논의 해보자. 먼저 올바른 데이터 1000만 건을 만들고 number형이 아닌 데이터와 date형이 아닌 데이터를 1건 추가해보자.  

 

drop table test_tbl purge;

 

create table test_tbl nologging as

select a.*

  from (select to_char(level) as varchar_num, to_char(level + sysdate, 'YYYYMMDD') as varchar_date

          from dual

       connect by level <= 100) a,

       (select level from dual connect by level <= 100000) b ;

 

insert into test_tbl values('ABCD', '20090230');

commit;

이제 함수를 실행 해보자.

 

alter session set statistics_level = all;

alter system flush buffer_cache;

 

select /*+ gather_plan_statistics */ *

  from test_tbl a

 where is_number(varchar_num) = 0;

결과 :

VARCHAR_NUM      VARCHAR_DATE

---------------- ------------

ABCD             20090230

 

select * from table(dbms_xplan.display_cursor(null,null,'allstats last')) ;

 

-------------------------------------------------------------------------------

| Id  | Operation         | Name     | A-Rows |   A-Time   | Buffers | Reads  |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |          |      1 |00:00:45.47 |   23463 |  23447 |

|*  1 |  TABLE ACCESS FULL| TEST_TBL |      1 |00:00:45.47 |   23463 |  23447 |

-------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("IS_NUMBER"("VARCHAR_NUM")=0)

 

함수를 사용하면 너무 느리다
함수를 천만번 수행하는데 무려 45초 이상 걸렸다. 너무나 느려서 사용할 수 없는 수준이다. 함수를 빠르게 실행하기 위해서 Deterministic 형 함수로 수정해보자. Deterministic 함수는 Input 에 대한 Output 의 값이 항상 같을 때만 사용해야 한다. Deterministic 함수를 사용하면 같은 값의 Input이 여러 번 들어올 경우 한번만 수행할 수 있다. 하지만 Deterministic 함수도 비효율이 있다. 이 Post 의 마지막에 Deterministic 함수 의 비효율과 관련된 Link를 표시하였으므로 반드시 읽어보기 바란다.

 

-- Deterministic 함수로 바꿈

CREATE OR REPLACE FUNCTION is_number(v_str_number IN varchar2)

RETURN NUMBER DETERMINISTIC IS 

… 이후 생략

/

DETERMINISTIC 함수를 사용해보자

alter system flush buffer_cache;

 

select /*+ gather_plan_statistics */ *

  from test_tbl a

 where is_number(varchar_num) = 0;

 

select * from table(dbms_xplan.display_cursor(null,null,'allstats last')) ;

 

-------------------------------------------------------------------------------

| Id  | Operation         | Name     | A-Rows |   A-Time   | Buffers | Reads  |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |          |      1 |00:00:07.50 |   23463 |  23447 |

|*  1 |  TABLE ACCESS FULL| TEST_TBL |      1 |00:00:07.50 |   23463 |  23447 |

-------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("IS_NUMBER"("VARCHAR_NUM")=0)

 

대단한 성능향상이다. 수행시간이 45초 에서 7초로 줄어들었다. 하지만 여기서 멈출순 없다.

alter system flush buffer_cache;

 

select /*+ gather_plan_statistics */ *

  from test_tbl a

 where (select is_number(varchar_num) from dual) = 0;

 

select * from table(dbms_xplan.display_cursor(null,null,'allstats last')) ;

 

--------------------------------------------------------------------------------

| Id  | Operation          | Name     | A-Rows |   A-Time   | Buffers | Reads  |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |          |      1 |00:00:03.00 |   23439 |  23433 |

|*  1 |  FILTER            |          |      1 |00:00:03.00 |   23439 |  23433 |

|   2 |   TABLE ACCESS FULL| TEST_TBL |     10M|00:00:00.01 |   23439 |  23433 |

|   3 |   FAST DUAL        |          |    101 |00:00:00.01 |       0 |      0 |

--------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(=0)  


함수사용시 스칼라서브쿼리를 활용하라
천만 건을 체크하는데 3초 밖에 걸리지 않았다. 함수 사용시 스칼라 서브쿼리를 사용하면 비효율 없이 함수 호출을 최소화 할 수 있다. Deterministic 함수든 아니든 상관없이 스칼라 서브쿼리의 효과는 동일하다. 그렇다면 함수 + 스칼라서브쿼리의 조합이 최선인가? 만약 일회성이 아닌 지속적으로 데이터를 체크해야 하는 경우라면 FBI(Function Based Index)를 생성해야 한다. 

 

create index idx_is_number on test_tbl (is_number(varchar_num)) ; -- FBI 생성

 

alter system flush buffer_cache;

 

select /*+ gather_plan_statistics index_rs(a idx_is_number) */ *

  from test_tbl a

 where is_number(varchar_num) = 0;

 

select * from table(dbms_xplan.display_cursor(null,null,'allstats last')) ; 

 

----------------------------------------------------------------------------------------------

| Id  | Operation                   | Name          | A-Rows |   A-Time   | Buffers | Reads  |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |      1 |00:00:00.03 |       5 |      4 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TBL      |      1 |00:00:00.03 |       5 |      4 |

|*  2 |   INDEX RANGE SCAN          | IDX_IS_NUMBER |      1 |00:00:00.03 |       4 |      3 |

----------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("A"."SYS_NC00003$"=0)

FBI 가 최적이다
Block I/O 수(Buffers 항목)를 비교해보라. 함수 + 스칼라 서브쿼리를 사용하는 것과 인덱스를 사용하는 것은 성능의 비교가 되지 않는다.  지속적으로 데이터를 검증해야 하고 테이블의 건수가 많지만 데이터를 체크하여 만족하지 않는 데이터의 건수가 적은 경우는 인덱스를 사용하는 것이 최적임을 알 수 있다.

이제 내일은 is_date, is_number 함수와 관련된 문제로 필자에게 전화가 오지는 않으리라 믿는다.^^

관련 Post :
http://ukja.tistory.com/159
http://adap.tistory.com/entry/Deterministic-의-진실Multi-buffer