'data quality'에 해당되는 글 2건

  1. 2009.11.11 오라클에서 is_number, is_date 함수 사용하기 457
  2. 2009.10.21 Varchar2(8) VS Date 어느 것이 우월한가? 18

지인에게서 전화가 오다
지인 : 데이터를 체크해야 하는데 오라클에 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



'Oracle > PL/SQL Pattern' 카테고리의 다른 글

PL/SQL-면접문제  (808) 2010.05.07
묵시적인 형변환을 피하라  (414) 2008.05.09
PL/SQL 에서 NUMBER 타입의 성능 테스트  (0) 2008.05.02
Posted by extremedb
,

일자 데이터 타입이란 YYYYMMDD 형(시/분/초 제외)을 이야기 하는 것이다. 이때 DATE 타입을 선택할 것인가 아니면 VRACHR2(8)을 선택할 것인가의 문제이다. 이것은 성능 문제이기도 하지만 물리 모델링, 개발효율성, 데이터 품질 등을 같이 생각 해야 한다. 물리모델링 시에 많은 모델러들이 일자 데이터 타입과 관련하여 이구동성으로 이야기 하는 것이 아래의 SQL 이다.

 

SELECT ...                     

  FROM ...                     

 WHERE 기준일자 = TO_DATE('20091021', 'YYYYMMDD') ;

 

위의 SQL 에서 일자 컬럼에 시//초가 포함되어 있다면 조회가 되지 않는다.

그렇다고 SQL 을 아래처럼 작성하는 것은 개발효율성이 떨어지고 성능에도 이롭지 못하다.

 

SELECT ...                     

  FROM ...                     

 WHERE 기준일자 BETWEEN TO_DATE('20091021','YYYYMMDD') AND TO_DATE('200910212359', 'YYYYMMDDHH24MISS') ;

 

아니 땐 굴뚝에 연기가 날까?

VARCHAR2(8)을 선호하는 사람들이 주로 이 문제를 제기한다. DATE 타입은 시//초가 들어감으로써 세가지 문제(데이터가 조회되지 않을 수 있고, 개발효율성과 성능이 떨어짐)가 발생함으로  VARCHAR2(8)을 사용해야 한다는 것이다.

 

하지만 과연 이 말이 사실일까? 모든 문제는 모델러가 시//초가 들어갈 수 있게 설계를 했기 때문이다. 왜 그런지 아래 스크립트를 보고 증명해보자

 

-- DATE 형과 VARCHAR2 형을 동시에 가진 테이블 생성                           

CREATE TABLE DT                                                              

( V_DT  VARCHAR2(8 BYTE),                                                    

  D_DT  DATE ) ;                                                                                                                                    

                                                                              

--일자 타입이 date 인 경우에 시//초가 입력될 경우 걸러내는 Constraint.        

--ex) //초를 포함하는 SYSDATE를 입력하면 에러를 발생시키기 위함.                        

--CHECK 절에 OR 가 있는 이유는 NULL 을 허용하기 위해서 이다.

ALTER TABLE DT                                                               

ADD CONSTRAINT D_DT_CHK                                                      

CHECK (D_DT = TRUNC(D_DT) OR D_DT IS NULL) ;        

            

이제 DATE 타입에 정상적인 데이터와 걸러져야 하는 데이터를 INSERT 해보자                            

 

--NULL을 대입해도 에러 발생하지 않음                                                                              

INSERT INTO DT (V_DT, D_DT) VALUES(NULL, NULL) ;                                                  

 

--SYSDATE는 시//초가 들어감으로 INSERT 되면 안됨                                                                              

INSERT INTO DT (V_DT, D_DT) VALUES (NULL, SYSDATE) ;                          

ORA-02290: 체크 제약조건(D_DT_CHK)이 위배되었습니다                   

                   

--에러 발생하지 않음                                                                             

INSERT INTO DT (V_DT, D_DT) VALUES (NULL, TRUNC(SYSDATE)) ;            

 

Constraint는 데이터 품질을 보장해준다

위에서 보는 바와 같이 Constraint는 시//초가 들어가지 않도록 보장해준다.

이것은 성능이 느린 VARCHAR2 타입을 사용하지 말아야 하는 이유가 될 수 있다.

 

어쩔 수 없이 VARCHAR2(8)을 사용하더라도 Constraint를 사용하라

일자 데이터 타입에 VARCHAR2(8)을 사용할 때 날짜가 아닌 데이터가 들어가는 문제도 마찬가지로 해결할 수 있다. 아래처럼 Constraint를 사용하면 된다.

 

--일자 타입이 VARCHAR2 인 경우에 잘못된 데이터를 걸러내는 Constraint .        

--ex) ‘20090230’을 걸러낸다.

--CHECK 절에 OR 가 있는 이유는 NULL 을 허용하기 위해서 이다.                                                  

ALTER TABLE DT                                                               

ADD CONSTRAINT V_DT_CHK                                                      

CHECK (V_DT = TO_CHAR(TO_DATE(V_DT,'YYYYMMDD'), 'YYYYMMDD') OR V_DT IS NULL) ;

 

이제 VARCHAR2(8)에 일자가 아닌 데이터를 넣어보자

 

--230일은 일자가 아니므로 INSERT 되면 안됨                                                                             

INSERT INTO DT (V_DT, D_DT) VALUES ('20090230', NULL) ;                       

ORA-01839: 지정된 월에 대한 날짜가 부적합합니다

 

--에러 발생하지 않음                                                                             

INSERT INTO DT (V_DT, D_DT) VALUES ('20090228', NULL) ;  

 

성공적으로 일자가 아닌 String 을 걸러 내었다.

 

결론

이제 알 것이다.  Constraint가 데이터 품질을 향상시키고 개발효율성을 높이며 성능에 까지 영향을 끼친다는 사실을

반박하라. //초가 들어감으로 VARCHAR2(8)을 사용해야 한다는 주장을

사용하라. 성능과 데이터 품질을 향상 시키는 DATE 타입을

오라클에서 시/분/초는 제외하고 일자만 저장되는 데이터 타입을 제공한다면 하는 아쉬움이 진하게 남는다.  미래의 버젼에 이러한 요구사항을 해결한 데이터 타입이 나오길 기대한다.


PS

DATE 타입을 성급히 적용하면 안 된다. 기존의 시스템은 일자가 아닌 데이터도 문제 없이 처리가 되었을 것이다. 하지만 DATE 타입으로 바꾸면 에러로 떨어진다. 따라서 에러의 처리정책과 처리Logic 등이 세워진 이후에 적용하라. 이런 골치 아픈 문제 때문에 VARCHAR2(8)을 사용하는 것은 말이 안 된다. 원칙적으로 에러로 떨어지는 것이 정당한 것이고 데이터를 수정하고 다시 처리하는 것이 옳다.

  

아래는 DATE 타입과 VARCHAR2 타입의 장단점 이므로 참고하기 바란다.

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



성능상으로는 DATE 형이 우월하다

여기에는 두 가지 이유가 있다.

 

첫 번째, DATE 타입은 옵티마이져가 날짜임을 인식한다.

 

SELECT ...                     

  FROM ...

 WHERE 기준일자 BETWEEN to_date('20091020', 'yyyymmdd') and to_date('20091021', 'yyyymmdd)

 

기준일자는 DATE 타입이다. 위의 조건대로라면 옵티마이져는 정확히 이틀간(10 20일부터 21일까지)이라는 것을 인지한다. 따라서 옵티마이져는 인덱스로 SCAN 할 것인지 아니면 FULL TABLE SCAN 할 것인지 판단할 수 있다.

 

하지만 기준일자가 아래처럼 VARCHAR2(8) 타입이라면 달라진다.

 

SELECT ...                      

  FROM ...

 WHERE 기준일자 BETWEEN '20091020' and '20091021'

 

옵티마이져는 '20091020'가 일자인지 인식 하지 못한다. 따라서 이틀 치의 데이터를 조회한다는 사실을 인식하지 못한다. String 타입이므로 이것은 당연한 것이다.

 

두 번째, DATE 타입은 7 byte를 차지하고 VARCHAR2(8) 8 byte를 차지한다.

 

두 가지 이유에 의해서 성능은 DATE 타입이 조금이라도 우월함을 알 수 있다.  

 

그럼에도 불구하고 DATE 타입을 사용하지 않는 가장 큰 이유는 무엇일까? 크게 3가지 이유로 요약된다.

 

첫 번째, DATE 타입의 문제점은 sysdate 등을 입력할 경우 시//초 가 들어감으로 SQL을 실행하면 결과값이 나오지 않는다.(이 문제는 위에서 이미 언급되었음)

 

두 번째, 년도나 월 데이터를 조회할 때 LIKE를 사용하지 못하고 BETWEEN 을 사용해야 한다는 것이다. 이것은 큰 문제라고 보지 않는다. 조건절이 조금 길어질 뿐 INDEX RANGE SCAN 이라는 점은 같기 때문이다.

 

세 번째, SYSDATE 등을 INSERT할 때 TRUNCT 등의 함수를 사용하여 시//초 등을 잘라내야 한다.

                

그렇다면 VARCHAR2 타입의 문제점은 없는가?
크게 3가지의 문제점이 있다.

 

첫 번째, 성능문제(옵티마이져가 일자인지 알 수 없음)

이 문제는 ORACLE 11g를 사용하면 더욱 심각한 차이가 발생할 수 있다.

왜냐하면 Bind Aware 기능이 강화되었기 때문에 변수를 마치 상수처럼 취급할 것이고 이에 따라 DATE 타입이 성능 면에서 훨씬 우월해 질것이다. 

                 

두 번째, VARCHAR2 타입의 문제점은 날짜가 아닌 데이터가 들어갈 수 있다는 것이다. 예를 들면 'ABCDEFGH' 혹은 '20090231' 등의 잘못된 데이터가 입력될 수 있다. 이것은 데이터 품질에 치명적이다. 혹자는 'DATE 타입도 시//초가 들어가므로 마찬가지 아니냐' 라고 생각할 수 있지만 근본적으로 다르다. DATE 타입을 사용하면 시//초는 TRUNC 등의 함수를 사용하여 Cleansing할 수 있지만 VARCHAR2는 그렇게 할 수 없다.

 

예를 들어 '20091032' 라는 데이터를 Cleansing 해야 한다고 치면 10 31일로 할 것인가? 아니면 11 1일로 할 것인가? 이것은 함부로 판단할 수 없는 것이다. 혹자는 'CHECK 기능을 추가하여 모든 프로그램에서 일자가 아닌 것을 CHECK 하면 되지 않냐?' 라고 할 수 있다.

 

맞는 말이다. 하지만 프로그램을 사용하지 않고 직접 DB KEY IN 하여 INSERT 할 수도 있기 때문에 원천적으로 원인을 제거한다고 볼 수 없다.(급한 경우에는 이렇게 하기도 한다)

 

세 번째, 성능문제에도 불구하고 개발편의성을 위해 VARCHAR2를 사용하였지만 일자연산이 발생하면 오히려 개발생산성이 저하된다.

 

예를 들면 일자끼리 빼서 차이를 본다든지 아니면 일자에 며칠을 더해서 본다든지 일자에 연산이 일어날 경우 오히려 TO_DATE 등의 함수를 사용해야 한다. 이런 경우는 자주 발생되는 편이다.

 

이상으로 DATE 타입과 VARCHAR2 타입의 장단점을 살펴보았다.

이 글과 관련된 POST 도 참고하기 바란다.

http://ukja.tistory.com/265

Posted by extremedb
,