오라클 로그 테이블 설계 - olakeul logeu teibeul seolgye

간단한 로그를 저장하는 테이블을 설계해보자.

1. SEQUENCE

-- MySQL과 달리 Oracle에서는 Auto Increment 선언을 직접 해줘야 한다. 그것을 SEQUENCE 라는 것을 생성하여 공통객체로 관리한다.
CREATE SEQUENCE LOG_NO_SEQ START WITH 1 INCREMENT BY 1 MAXVALUE 99999999999999;
ALTER SEQUENCE LOG_NO_SEQ CYCLE;

-- SEQUENCE 증가 (YYYYMMDD + 14자리)
-- 최대한 중복을 방지하기 위하여 아래와 같은 형식으로 CYCLE을 돌게함.
SELECT TO_NUMBER(CONCAT(TO_CHAR(SYSDATE, 'YYYYMMDD'), LPAD(LOG_NO_SEQ.NEXTVAL, 14, 0))) AS LOG_NO_SEQ FROM DUAL;

2. TABLE

-- 테이블 생성문
-- TABLESPACE는 TEST로 가정하고 추가함
CREATE TABLE TB_TEST (
    LOG_NO NUMBER NOT NULL,
    SVC_NM VARCHAR2(200) DEFAULT NULL,
    DB_NM VARCHAR2(200) NOT NULL,
    TBL_NM VARCHAR2(200) DEFAULT NULL,
    PROCDR_NM VARCHAR2(200) DEFAULT NULL,
    CALL_URL VARCHAR2(2000) NOT NULL,    
    CALL_MTHD_SP_VAL VARCHAR2(15) NOT NULL,    
    CALL_PARA_VAL VARCHAR2(2000) DEFAULT NULL,    
    EXEC_TME NUMBER DEFAULT 0,        
    LOG_DESC VARCHAR2(4000) DEFAULT NULL, 
    INPT_DM DATE DEFAULT SYSDATE
)
LOGGING
TABLESPACE TEST;

-- 테이블 코멘트 추가
COMMENT ON COLUMN "TB_TEST"."LOG_NO" IS '로그번호';
COMMENT ON COLUMN "TB_TEST"."SVC_NM" IS '서비스명';
COMMENT ON COLUMN "TB_TEST"."DB_NM" IS 'DB명';
COMMENT ON COLUMN "TB_TEST"."TBL_NM" IS '테이블명';
COMMENT ON COLUMN "TB_TEST"."PROCDR_NM" IS '프로시져명';
COMMENT ON COLUMN "TB_TEST"."CALL_URL" IS '호출URL';
COMMENT ON COLUMN "TB_TEST"."CALL_MTHD_SP_VAL" IS '호출방법구분값';
COMMENT ON COLUMN "TB_TEST"."CALL_PARA_VAL" IS '호출매개변수값';
COMMENT ON COLUMN "TB_TEST"."EXEC_TME" IS '실행시간 (단위:m/s)';
COMMENT ON COLUMN "TB_TEST"."LOG_DESC" IS '로그설명';
COMMENT ON COLUMN "TB_TEST"."INPT_DM" IS '입력일시';

-- 테스트 데이터 INSERT 문
INSERT INTO "TB_TEST" (LOG_NO, SVC_NM, DB_NM, TBL_NM, CALL_URL, CALL_MTHD_SP_VAL, CALL_PARA_VAL, EXEC_TME, LOG_DESC) VALUES (TO_NUMBER(CONCAT(TO_CHAR(SYSDATE, 'YYYYMMDD'), LPAD(OOADM.LOG_NO_SEQ.NEXTVAL, 14, 0))), 'oyez', 'OYOOPRD1', 'TB_ST_STR_STK_SCRP_L', 'http://test.com/api/tests', 'POST', '{"productDisposalList":[{"strCd":"DB67","scrpYmd":"20200121","gdsCd":"3264680006302","strScrpRsnCd":"14","scrpQty":1,"scrpAmt":20700,"scrpGdsImgFileNo":741302,"regNm":"테스트보봉2222","scrpCostUprc":20700,"scrpSelprcUprc":35000,"vatSpCd":"G","gdsLclsCd":"01","gdsMclsCd":"0102","gdsSclsCd":"010201","bizplcTypCd":"S","dmngFcSpCd":"D","bizplcShapeCd":"DA","scrpApprvStatCd":"1","linkDatYn":"N","orgScrpQty":1},{"strCd":"DB67","scrpYmd":"20200120","gdsCd":"3264680006302","strScrpRsnCd":"14","scrpQty":1,"scrpAmt":20700,"scrpGdsImgFileNo":741302,"regNm":"테스트보봉","scrpCostUprc":20700,"scrpSelprcUprc":35000,"vatSpCd":"G","gdsLclsCd":"01","gdsMclsCd":"0102","gdsSclsCd":"010201","bizplcTypCd":"S","dmngFcSpCd":"D","bizplcShapeCd":"DA","scrpApprvStatCd":"1","linkDatYn":"N","orgScrpQty":1}],"gUserId":999999}', '1200', '등록');
INSERT INTO "TB_TEST" (LOG_NO, SVC_NM, DB_NM, PROCDR_NM, CALL_URL, CALL_MTHD_SP_VAL, CALL_PARA_VAL, EXEC_TME, LOG_DESC) VALUES (TO_NUMBER(CONCAT(TO_CHAR(SYSDATE, 'YYYYMMDD'), LPAD(OOADM.LOG_NO_SEQ.NEXTVAL, 14, 0))), 'oyez', 'OYOOPRD1', 'TB_ST_STR_STK_SCRP_L', 'http://test.com/api/tests', 'POST', '{"productDisposalList":[{"strCd":"DB67","scrpYmd":"20200121","gdsCd":"3264680006302","strScrpRsnCd":"14","scrpQty":1,"scrpAmt":20700,"scrpGdsImgFileNo":741302,"regNm":"테스트보봉2222","scrpCostUprc":20700,"scrpSelprcUprc":35000,"vatSpCd":"G","gdsLclsCd":"01","gdsMclsCd":"0102","gdsSclsCd":"010201","bizplcTypCd":"S","dmngFcSpCd":"D","bizplcShapeCd":"DA","scrpApprvStatCd":"1","linkDatYn":"N","orgScrpQty":1},{"strCd":"DB67","scrpYmd":"20200120","gdsCd":"3264680006302","strScrpRsnCd":"14","scrpQty":1,"scrpAmt":20700,"scrpGdsImgFileNo":741302,"regNm":"테스트보봉","scrpCostUprc":20700,"scrpSelprcUprc":35000,"vatSpCd":"G","gdsLclsCd":"01","gdsMclsCd":"0102","gdsSclsCd":"010201","bizplcTypCd":"S","dmngFcSpCd":"D","bizplcShapeCd":"DA","scrpApprvStatCd":"1","linkDatYn":"N","orgScrpQty":1}],"gUserId":999999}', '1200', '등록');

3. SYNONYM

-- SYNONYM 지정
-- TESTDB.TB_TEST 를 TB_TEST로만 호출이 가능하다.
CREATE SYNONYM TB_TEST FOR TESTDB.TB_TEST;

4. Primary Key

-- PK 생성
CREATE UNIQUE INDEX PK_CM_OP_L ON TB_CM_OP_L (LOG_NO)
LOGGING
TABLESPACE TEST_UNIQUE_INDEX;

CREATE INDEX IX_CM_OP_L_01 ON TB_CM_OP_L(INPT_DM)
LOGGING
TABLESPACE TEST_INDEX;

5. GRANT

-- 권한추가 필요
GRANT SELECT ON TB_CM_OP_L TO USER;
GRANT INSERT ON TB_CM_OP_L TO USER;
GRANT DELETE ON TB_CM_OP_L TO USER;

1년정도 지표처리 작업을 해오면서 느꼈던 나만의 로그 테이블 설계 원칙을 작성해본다.

1. LogDB에는 2차 가공 데이터는 없어야 한다.

-> DB에 박혀있는 값을 가공하여 DB에 INSERT 하는 것은 삼가야 한다.

-> 추가적인 가공이 필요한 데이터는 Tool 에서 진행하도록 한다.

2. 알아보기 쉽게. 간단하게 작성되어야 한다. 그렇지만 운영상에 필요한 정보는 반드시 존재하여야 한다.

-> 개발자가 LogDB를 SELECT 하는 것 만으로도 해당 로그가 어떠한 이유에서 발생한 것인지를 명확하게 알 수 있어야 한다.

-> 개발자가 생각하기에 비교적 필요없는 정보라고 할지라도, 운영상에 필요한 정보라면 반드시 남기도록 한다.

-> 특히나, 운영상에 필요한 정보가 Tool 에서 자주 요청되는 정보의 성격이라면 타협의 여지 없이 DB에 남겨야한다.

3. 공통 로그 포맷은 죄악이다.

-> 개발의 편의성을 위하여 모든 로그에 대해서 공통된 포맷을 사용하는 경우가 왕왕있다. 공통된 로그 포맷으로도 완벽히 제어할 수 있다면 문제는 없지만, 보통 그렇지 않다. 필요한 칼럼이 있으면 억지로 구겨넣지 말고 늘려야 한다.

4. 하나의 칼럼에는 하나의 정보만을 담는다.

-> 로그 시스템이 DW 이상급의 크기를 갖추지 않는다는 전제 하에, 하나의 칼럼에는 하나의 정보만을 담는 것이 좋다.

-> 개발자가 SELECT 하였을 때 WHERE 절로 바로 검색가능한 로그 포맷이어야 한다.