SQL BOOSTER 이어지는 이야기 .10

<잘 되던 LIKE도 다시 보자.>

 

LIKE는 문자열 중에, 일부 문자가 같은 데이터를 검색할 수 있는 매우 유용한 조건자다. 하지만 LIKE는 인덱스를 설계할 때 고민을 많이 하게 만든다. ‘같다(=)’ 조건을 사용해도 되는 SQL이라면 LIKE보다는 같다(=) 조건을 사용해야 한다.

 

SQL BOOSTER 에 이어지는 이야기들입니다.~!
SQL BOOSTER 를 보신 분들께 좀 더 도움을 드리고자 추가로 작성한 내용입니다.

www.aladin.co.kr/shop/wproduct.aspx?ItemId=216383877

SQL BOOSTER

설명의 편의상 반말체로 작성한 점 양해바랍니다.  pdf 파일도 첨부드리니 다운 받아 보셔도 됩니다.

SQL_Booster_이어지는이야기10.pdf

0.29MB

 

아래 SQL은 고객이 접속해서 자신의 주문 건수를 조회하는 SQL이다.

[SQL-1] 고객의 주문 건수 조회.(주문 일자는 전체일 수도 있다.)
SELECT	COUNT(*) ORD_CNT
FROM	T_ORD_JOIN T1
WHERE	T1.CUS_ID LIKE :v_CUS_ID||'%'
AND	T1.ORD_YMD LIKE :v_ORD_YMD||'%';

위 SQL에서는 CUS_ID와 ORD_YMD에 모두 LIKE 조건을 사용했다. 그러므로 인덱스 설계에 고민하게 된다. 업무를 가만히 생각해 보면, ‘고객이 접속해서 자신의 주문 건수를 조회’하는 것이다. 그러므로 고객ID(CUS_ID)는 빈 값이 절대 들어 올 수 없다. 아래와 같이 CUS_ID에는 같다(=) 조건을 사용해야 한다.

[SQL-2] 고객의 주문 건수 조회. (주문 일자는 전체일 수도 있다.) – 같다(=) 조건 사용
SELECT	COUNT(*) ORD_CNT
FROM	T_ORD_JOIN T1
WHERE	T1.CUS_ID = :v_CUS_ID
AND	T1.ORD_YMD LIKE :v_ORD_YMD||'%';

위 SQL은 CUS_ID+ORD_YMD 순서의 인덱스를 만들면 된다. 인덱스 설계에 고민이 덜하다. 부디 습관적으로 LIKE 조건을 사용하는 일은 없기 바란다.

‘LIKE 사용은 최대한 자제하자!’ 이것은 필자의 개인적인 의견이다. 인덱스를 효율적으로 설계해 SQL의 성능을 높일 수 있기 때문이다.

 

LIKE 사용으로 발생하는 문제점을 하나 더 살펴보자. 이번 설명을 위해서 M_SN(시리얼번호)이라는 새로운 테이블을 만들 것이다. M_SN 테이블은 아이템유형(ITM_TP)가 PC, ELEC 인 아이템의 시리얼번호를 관리한다. 이 테이블을 통해 LIKE 사용에 주의할 점을 알아볼 것이다.

 

 

M_SN 테이블은 ‘SQL BOOSTER’를 처음 작성할 때 이미 생각해서 설계했던 테이블이다. (그때는 SQL BOOSTER라는 이름이 탄생하기 전이다.) 하지만 해당 테이블을 사용한 활용 예제들을 충분히 담기가 쉽지 않아 결국 생략하게 되었다. 다행히도 이어지는 이야기를 통해 소개할 수 있게 되었다. 새로 만들 테이블은 아래와 같은 구조다.

그림 1

 

아래 스크립트로 M_SN 테이블을 생성하도록 하자.

[SQL-3] M_SN 테이블 만들기
CREATE TABLE M_SN
(SN VARCHAR2(40) NOT NULL
,PRD_YMD VARCHAR2(8) NULL
,SHP_YMD VARCHAR2(8) NULL
,ITM_ID VARCHAR2(40) NOT NULL
,ITM_TP VARCHAR2(40) NOT NULL);

ALTER TABLE M_SN
    ADD CONSTRAINT PK_M_SN PRIMARY KEY(SN);

 

아래 스크립트를 이용해 M_SN 테이블에 데이터를 생성한다.

[SQL-4] M_SN 데이터 만들기
INSERT INTO M_SN (SN ,PRD_YMD ,SHP_YMD ,ITM_ID ,ITM_TP)
SELECT  CASE WHEN T1.ITM_TP = 'ELEC' THEN 'E' ELSE 'P' END||LPAD(T3.RNO,3,'0')
          ||LPAD(T2.T_NO,4,T3.SN_ADD)
          ||LPAD(ROW_NUMBER() OVER(PARTITION BY T1.ITM_TP ORDER BY T2.BAS_YMD, T1.ITM_ID, T3.RNO),7,'0')
          ||SN_ADD
          ||CASE WHEN T1.ITM_TP != 'ELEC' THEN TO_CHAR(T2.YMD_NO) ELSE '' END SN
        ,T2.BAS_YMD PRD_YMD
        ,TO_CHAR(TO_DATE(T2.BAS_YMD,'YYYYMMDD') 
	+ MOD(TO_NUMBER(REGEXP_REPLACE(T1.ITM_ID,'([^[:digit:]])','')),8),'YYYYMMDD') SHP_YMD
        ,T1.ITM_ID
        ,T1.ITM_TP
FROM    M_ITM T1
        ,(
              SELECT  A.BAS_YMD
                      ,MOD(ROW_NUMBER() OVER(ORDER BY A.BAS_YMD ASC),4) YMD_NO
                      ,ROW_NUMBER() OVER(ORDER BY TO_NUMBER(SUBSTR(REVERSE(A.BAS_YMD),1,5))) T_NO 
              FROM    C_BAS_YMD A
              WHERE   A.BAS_YMD >= '20190101'
              AND     A.BAS_YMD <= '20200120'
              
        ) T2,
        (
              SELECT ROWNUM RNO 
                     ,CHR(MOD(ROWNUM, 8)+65) SN_ADD
              FROM DUAL CONNECT BY ROWNUM <= 100
        ) T3
WHERE   T1.ITM_TP IN ('ELEC','PC')
AND     MOD(TO_NUMBER(REGEXP_REPLACE(T1.ITM_ID,'([^[:digit:]])','')),4)  <= T2.YMD_NO
;

COMMIT;

위 SQL은 카테시안-조인을 이용해 총 721,500개의 시리얼 번호를 만들어낸다. 카테시안-조인을 활용해 테스트 데이터를 만드는 과정은 SQL BOOSTER에 설명되어 있다. 위 SQL에서는 REGEXP_REPLACE라는 정규식 함수를 사용하고 있다. REGEXP_REPLACE를 이용해 숫자가 아닌 데이터([^[:digit:]])는 빈 값으로 치환하고 있다.

오라클은 REGEXP_REPLACE 외에도 다양한 정규식 함수를 제공한다. 정희락님의 ‘불친절한 SQL 프로그래밍’에 설명이 되어 있으니 한 번 찾아보기 바란다. 이러한 정규식 함수는 개발자에게도 유용하지만, 데이터 클린징이나 이행을 담당해야 하는 사람에게도 매우 유용하다.

 

시리얼번호가 존재하는지 확인하는 SQL을 만들어보자. 아래와 같다.

[SQL-5] SN 조회 - 16자리 시리얼번호와 아이템유형을 입력 받는다.
SELECT  T1.*
FROM    M_SN T1
WHERE   T1.SN = 'E018CC400000418C'
AND     T1.ITM_TP = 'ELEC';

 

문제없이 시리얼번호가 조회된다. 이번에는 PC제품을 조회해보자. 아래와 같다.

[SQL-6] SN 조회 - 16자리 시리얼번호와 아이템유형을 입력 받는다.(PC제품 조회)
SELECT  *
FROM    M_SN T1
WHERE   T1.SN = 'P080AA400000680A'
AND     T1.ITM_TP = 'PC';

위 SQL을 실행하면 조회되는 데이터가 없다. 위 SQL에서 사용한 변수('P080AA400000680A')는 16자리다. 앞에서 M_SN에 데이터를 만들 때 의도적으로 ITM_TP(아이템유형)가 ELEC은 16자리, PC 는 17자리로 구성했다. 그러므로 PC아이템을 같다(=) 조건으로 조회하려면 17자리 SN을 입력해야 한다. 만약에 ‘SN 조회 화면’에서 17자리를 모두 입력 받을 수 있다면 위 SQL은 문제가 없을 것이다. 여기서는 PC 제품도 ‘SN 조회 화면’에서 16자리까지만 입력할 수 있다고 가정한다.

 

 

무조건 16자리만 입력된다고 가정했을 때, M_SN에서 PC 제품을 조회하기 위해 아래와 같은 SQL을 고민해 볼 수 있을 것이다.

[SQL-7] SN 조회 - SUBSTR 사용
SELECT  *
FROM    M_SN T1
WHERE   SUBSTR(T1.SN,1,16) = 'P080AA400000680A'
AND     T1.ITM_TP = 'PC';

 

위 SQL은 최악이다. SQL BOOSTER의 독자라면 절대 위와 같이 SQL을 개발하는 일은 없기 바란다. SUBSTR 대신에 아래와 같이 LIKE를 사용하면 16자리를 이용해 SN을 조회할 수 있다.

[SQL-8] SN 조회 - LIKE 사용
SELECT  *
FROM    M_SN T1
WHERE   T1.SN LIKE 'P080AA400000680A'||'%'
AND     T1.ITM_TP = 'PC';

위 SQL을 사용하면, ELEC과 PC 아이템 유형의 SN 조회에 모두 사용할 수 있다. 성능에 있어서 같다(=) 조건보다 아주 약간의 손해가 있지만, 신경 쓰지 않아도 될 정도다.

과연, 여기서 안심하고 개발을 마무리하면 될 것인가? 이 글의 제목은 “잘 되던 LIKE도 다시 보자”다. 프로그램 오류로 시리얼 번호가 16자리가 입력되지 않고, 3자리만 입력되었다고 생각해보자. 아래와 같이 말이다.

[SQL-9] SN 존재 확인 SQL LIKE – 프로그램 오류로 3자리만 입력됨
SELECT  *
FROM    M_SN T1
WHERE   T1.SN LIKE 'P08'||'%'
AND     T1.ITM_TP = 'PC';

위 SQL은 P08로 시작하는 모든 시리얼번호를 조회하게 된다. 성능에 문제가 있을 수 밖에 없다. 성능 부하를 고려해 ROWNUM = 1 조건을 추가하는 것을 고려해 볼 수 있다. 아래와 같이 말이다.

[SQL-10] SN 존재 확인 SQL LIKE, ROWNUM – 프로그램 오류로 3자리만 입력됨
SELECT  *
FROM    M_SN T1
WHERE   T1.SN LIKE 'P08'||'%'
AND     T1.ITM_TP = 'PC'
AND     ROWNUM = 1;

[SQL-10]은 한 건의 데이터만 조회되므로 성능의 문제는 없겠지만, 원하는 결과가 아니다. 성능보다는 데이터의 정확성이 먼저다.

길이가 짧은 변수로 성능 저하가 발생하지 않게 하기 위해 아래와 같은 SQL을 고려할 수 있다.

[SQL-11] SN 존재 확인 SQL LIKE, LENGTH – 프로그램 오류로 3자리만 입력됨
SELECT  *
FROM    M_SN T1
WHERE   T1.SN LIKE 'P08'||'%'
AND     T1.ITM_TP = 'PC'
AND     LENGTH('P08') = 16; -- 입력된 변수가 16자리일때만 SQL이 처리되도록 한다.

위와 같이 WHERE 절에 입력된 변수의 길이를 체크하는 조건을 추가하는 것이다. 이처럼 SQL을 작성하면 입력된 변수의 길이가 맞지 않으면 데이터 자체를 뒤질 일도 없다.

 

물론, 프로그램적으로 길이가 짧은 변수가 입력되는 일이 없도록 해주는 것이 기본이다. 하지만 개발하다 보면 어디선가 실수가 나게 된다. 프로그램과 함께 SQL에 위의 조건을 추가해 혹시 모를 오류를 한 번 더 막을 수 있다.

시리얼번호가 모두 16자리로 관리되고 있다면 이와 같은 고민을 할 필요조차 없다. 필자의 경험으로는, 업무는 계속 변하고, 만들어 놓은 규칙도 계속 변한다. 변하는 부분을 모델에 모두 반영하고 개발한 SQL에 제대로 제때 반영하는 작업은 만만하지 않다. 이러한 과정에서 실수가 발생하고, 이로 인해 성능 문제를 일으키는 SQL들이 만들어진다. 여기서 살펴본 예제를 통해 LIKE를 사용하기 전에 한 번 더 생각해 보기 바란다. 성능에 이슈가 없을지 말이다.