SQL BOOSTER - 문서번호 처리 기술

 

1. SELECT MAX 방식

 

현업에서는 일자+순번으로 문서번호를 관리할 수도 있으며, 법인+일자+순번 등으로 문서번호를 관리한다.

CREATE TABLE T_PO
(

    PO_NO VARCHAR2(40) NOT NULL,
    TIT   VARCHAR2(100) NULL, 
    //...


);
CREATE UNIQUE INDEX PK_T_PO ON T_PO(PO_NO);
ALTER TABLE T_PO ADD CONSTRAINT PK_T_PO PRIMARY KEY(PO_NO) USING INDEX;

 

구매오더번호의 채번 규칙은 다음과 같다 

- PO(고정문자)+YYYYMMDD(요청일자)+NNNNNNNN(순번8자리)

 

DECLARE 
 v_NEW_PO_NO VARCHAR2(40);
 v_REQ_DT DATE;
 v_REQ_YMD VARCHAR2(8);
 
 BEGIN 
 
   v_REQ_DT := TO_DATE(20170301 23:59:59, 'YYYYMMDD HH24:MI:SS');
   v_REQ_YMD := TO_CHAR(v_REQ_DT, 'YYYYMMDD'); 
   
   SELECT 'po' || V_REQ_YMD || LPAD(TO_CHAR(TO_NUMBER(NVL(SBSTR(MAX(T1.PO_NO)-8,'0')+1),8,'0')
   INTO v_NEW_PO_NO
   FROM T_PO T1
   WHERE T1.REQ_DT >= TO_DATE(v_REQ_YMD,'YYYYMMDD')
   AND T1.REQ_DT <TO_DATE (v_REQ_YMD,'YYYYMMDD')+1;
   
   //..

 

해당 PL/SQL은 T_PO에서 요청일자에 해당하는 가장 큰 PO_NO를 가져와 새로운 PO_NO를 채번하고 있다.

채번 과정을 보면, MAX,SUBSTR,TO_CHAR,LPAD와 같은 함수를 복잡하게 사용하고 있다. 

이와 같은 방식을 SELECT ~ MAX 방식이라 한다. 

해당 방식은 성능 이슈와 잠재적인 오류를 내포하고 있다. 

 

1.2 SELECT MAX 방식의 성능

 

SELECT ~ MAX 채번 방식의 성능 문제를 알아보자 

 

아래 SQL을 통해 100일 간에 해당하는 테스트 데이터를 생성해보자 

 

INSERT INTO T_PO
(PO_NO, TIT, REQ_DT, REQ_UID)

SELECT 'PO'||T2.REQ_YMD||LPAD(TO_CHAR(T1.RNO),8,'0') PO_NO, 'TEST PO' TIT
,TO_DATE(T2.REQ_YMD,'YYYYMMDD') + (RNO /60 /60/ 24) REQ_DT, 'TEST' REQ_UID

FROM (SELECT ROWNUM RNO FROM DUAL CONNECT BY ROWNUM <= 10000) T1,
(
  SELECT TO_CHAR(TO_DATE('20170101','YYYYMMDD')+(ROWNUM -1), 'YYYYMMDD') REQ_YMD FROM DUAL A 
  CONNECT BY ROWNUM <= 100 ) T2;

)

COMMIT;

 

- ROWNUM 만개와 REQ_YMD 카테시안 곱을 통해 백만 건의 데이터 생성