티스토리 툴바



2011/05/16 17:10

실전 데이터모델링(비트컬럼으로 컬럼레벨 데이터통합) 기법 및 오라클 비트연산 가이드




비트컬럼으로 속성레벨의 데이터 통합

이번 포스트에서는 비트값으로 구성된 컬럼으로 컬럼 수준의 데이터 통합에 대한 가이드 및 비트 컬럼을 SQL로 조작하는 방안에 대해 살펴볼까 합니다.

먼저, 일반적인 샘플 ERD를 하나 보겠습니다.


좌측은 Logical 엔터티이며 우측은 Physical 테이블 설계안 입니다.

지극히 간단한 테이블입니다. 웹 화면을 구성하는 메뉴가 있고, 메뉴의 속성으로는 ID, 명, 그리고 화면을 제어하는 4개의 여부 컬럼이 있습니다. 간단하지만 전혀 문제가 없습니다. 요구사항도 명확히 반영했다고 한다면 정말 문제가 없는 모델링 입니다.

프로젝트팀이 프로젝트를 완료하고 난 후 운영팀에서 운영중에 새로운 고객 요구사항들이 나오기시작합니다. 메뉴ID를 화면에 무조건 보여주지 말고 사용자가 보여줄지 말지를 선택하게 해달라는 군요. 그러면 컬럼을 추가하면 됩니다 “메뉴ID디스플레이여부” 란 컬럼이 추가됩니다.

또 다른 고객이 이와 유사한 요구를 해 옵니다.
얼마 지나지 않아 “~여부” 컬럼만 6개나 추가되었군요. 그래도 컬럼만 추가하고 프로그램 코드를 수정하면 요구사항을 반영하는 데에는 큰 문제는 없습니다.

하지만 일 하기 싫은 DBA가 고민합니다.(바로 저 입니다.)
“이런 식의 고객 요구사항이 들어와도 컬럼 수정, 테이블 수정 없이 처리할 순 없을까?”

방법은 아래 그림과 같이 모든 여부 컬럼을 비트값의 합으로 표현하여 하나의 컬럼으로 통합해버리면 됩니다.


4개의 여부 컬럼을 “디스플레이비트값” NUMBER 하나의 컬럼으로 통합했습니다. 기존 모델의 각각의 컬럼은 2진수로 표현했을 때 하나의 비트값에 대응됩니다.

예를 들어
“배너디스플레이여부” 컬럼은 2^0 = 1로 설정하면 되며,
“메뉴이미지사용여부” 컬럼은 2^1 = 2,
“컨텐츠표시여부” 2^2 = 4,
“Hybrid메뉴여부” 2^3 = 8 이 됩니다.

그리고 실제 디스플레이비트값은 각각의 값을 BITWISE OR 연산을 하여 저장을 하게 됩니다.

예를 들어
“배너디스플레이여부” = “Y” ,
“메뉴이미지사용여부”= “N”,
“컨텐츠표시여부” = “Y”,
“Hybrid메뉴여부” = “Y” 값을 갖는 다면 실제 디스플레이비트값 컬럼에 저장되는 값은
1(2^0) + 4(2^2) + 8(2^3) = 13이 됩니다.

각각의 통합전 컬럼에 해당 되는 비트값을 부여할 때 2의 승수로만 부여하기 때문에 실제 bitwise or 연산의 결과나 단순 더하기 결과나 결과는 동일해 집니다.

0001(1) + 0010(2) + 1000(8) = 1011(13)

이 모델의 장점은 고객의 새로운 요구사항이 있을 때 컬럼의 추가 없이 프로그램만 수정하면 됩니다. 좀 있어보이는 말로는 “시스템의 유연성을 높이는 설계 방법” 이라고 하죠. 쉽게 말해 DBA가 편해지는 군요. 제가 바라는 바입니다.

제가 운영하는 시스템의 사례를 얘기할까 합니다.

제가 운영하는 시스템은 마케팅 사이트로 Global 시스템이지만 국내 한 서버에서 전세계 사이트를 모두 서비스 합니다. 시스템 사양에 비해서 Request가 많기 때문에 서버는 항상 바쁩니다. 글로벌 사이트라서 고객은 일년의 단 10분도 시스템이 정지되는걸 원하지 않습니다.

그럼에도 불구하고 위의 사례처럼 자잘 하지만 “~여부” 같은 컬럼을 추가해야만 처리될 수 있는 요구를 합니다. 컬럼 추가하고 개발하면 되겠죠. 하지만 문제가 있습니다. 대부분의 테이블은 문제가 없지만 시스템의 Main Entity인 2~3개의 테이블은 거의 모든 SQL이 집중되고, 수많은 PL/SQL이 참조하고 있어서 컬럼을 추가하는 순간 library cache pin 및 lock 등의 waiting이 걸리면서 오라클이 처리를 빨리 하지 못하게 되며, WAS에서는 DB 요청에 대한 리턴을 빠르게 받지 못하게 되니 계속계속 새로운 요청마다 새로운 Oracle Connection을 맺다가 Connection Pool Max size를 넘어서게 될 때 시스템이 거의 죽어버린답니다.

이런 상황에서는 컬럼 추가 작업 자체가 online에서 쉽게 alter table 명령어로 처리할 수 없으며, 컬럼을 추가하기 위해서는 수많은 결재와 보고를 해야 하고 휴일에 특정 시간대에 시스템을 내리고 컬럼 추가하고 다시 시스템을 기동하는 식으로 처리를 합니다.

어휴~ 컬럼 추가 작업이 장난이 아닙니다. 그러니, 제가 컬럼 추가 안하고 설계할 수 있는 방안을 고민하지 않을 수 없습니다.

하지만 이 모델의 단점도 만만치 않습니다.
단점은 첫째, 가독성이 떨어집니다. DA#으로 모델링을 하면 통합전 컬럼의 비트값을 서브타입으로 표현할 수 있어서 그나마 낫지만 Erwin 같은 툴을 쓰면 속성설명 정도에만 표현할 수 밖에 없어 모델의 가독성이 떨어져 문제가 됩니다.
두번째 단점은 SQL이 복잡해 집니다. 초급 개발자들은 설명을 해줘도 잘 이해를 못하더군요.

간단하지만 위 두 모델에 대해 무엇이 좋고 나쁘다라고는 말할 수 없을 것 같습니다. 다만, 비트값으로 속성을 표현하여 통합할 수 있는 방법도 있다라는 것을 알고, 시스템의 상황에 맞게 가장 적합한 방법을 사용하시면 됩니다. 저 같은 경우는 위의 컬럼 추가가 문제가 되는 테이블, 그리고 자주 업무 요건이 변경되는 테이블은 비트값으로 컬럼을 통합하는 방식을 많이 사용하며, 그렇지 않는 테이블은 사용하지 않습니다.

비트연산 기초

비트값 형식으로 속성을 모델링 했을 때 화면 UI와 이 UI를 처리하는 DML에 대해 살펴보도록 하겠습니다. 그전에 기초로 돌아가서 비트 연산에 대해 알아보겠습니다. 요즘 개발자 분들이 처음부터 웹 개발을 하신 분들은 비트 연산을 할 일이 없어서인지 비트연산을 헷갈려 하더군요.

비트 연산(Bitwise operation)은 한 개 혹은 두 개의 이진수에 대해 비트 단위로 적용되는 연산입니다.

NOT : NOT 연산은 각 자릿수의 값을 반대로 바꾸는 연산입니다.

NOT 0111
= 1000

OR : OR 연산은 두 값의 각 자릿수를 비교해, 둘 중 하나라도 1이 있다면 1을, 아니면 0으로 계산됩니다.

0101
OR 0011
= 0111

XOR : XOR 연산은 두 값의 각 자릿수를 비교해, 값이 같으면 0, 다르면 1으로 계산 합니다.

0101
XOR 0011
= 0110

AND : AND 연산은 두 값의 각 자릿수를 비교해, 두 값 모두에 1이 있을 때에만 1을, 나머지 경우에는 0으로 계산합니다

0101
AND 0011
= 0001


예전에 공부했던 게 기억이 나십니까?

비트속성 컬럼값 DML 가이드

화면 UI에서 비트컬럼값을 처리하는 DML 가이드입니다.



다음과 같은 화면이 있다고 가정합니다. 위 메뉴 테이블을 관리하는 화면입니다.

각 비트값에 해당되는 항목마다 라디오 버튼을 가진 하나의 항목으로 UI는 구성될 것입니다. 물론 다른 방식으로도 가능하겠지만요. 일단 이렇게 된다고 가정합니다.

이 화면의 특징은 한 화면에서 비트컬럼값이 사용하는 모든 비트값을 다 관리를 한다는게 특징입니다.

이런 케이스에 대해서는 처리 로직은 간단합니다.

먼저, 화면을 구성하기 위한 SELECT 문을 보겠습니다.

SELECT MENU_ID
            ,MENU_NM
            ,DECODE(BITAND(DISPLAY_BV,1),1,'Y','N') AS "배너디스플레이여부"
            ,DECODE(BITAND(DISPLAY_BV,2),2,'Y','N') AS "메뉴이미지사용여부"
            ,DECODE(BITAND(DISPLAY_BV,4),4,'Y','N') AS "컨텐츠표시여부"
            ,DECODE(BITAND(DISPLAY_BV,8),8,'Y','N') AS "Hybrid메뉴여부"
FROM MENU2;

오라클에서 제공하는 BITAND 함수를 가지고 해당비트값이 1인지를 판단할 수 있습니다.

이 경우 값을 INSERT, UPDATE 할 때는 각각의 항목에 대한 값(Y/N)을 판단해서 아래와 같이 처리할 수 있습니다.

UPDATE MENU2
      SET DISPLAY_BV = DECODE('배너디스플레이여부','Y',1,0)
                                + DECODE('메뉴이미지사용여부','Y',2,0)
                                + DECODE('컨텐츠표시여부','Y',4,0)
                                + DECODE('Hybrid메뉴여부','Y',8,0)
 WHERE MENU_ID = :MENU_ID;

하지만, 위의 경우처럼 Y/N으로 INPUT TYPE=”RADIO”의 값을 구성하는 것 보다는 VALUE로 각각의 컬럼이 갖는 비트값으로 바로 지정하는게 효율적일 수 있습니다. 웹으로 구성한다면 value >0 면 checked를 지정하면 될 것 같군요.


SELECT MENU_ID
            ,MENU_NM
            ,DECODE(BITAND(DISPLAY_BV,1),1,1,0) AS "배너디스플레이여부"
            ,DECODE(BITAND(DISPLAY_BV,2),2,2,0) AS "메뉴이미지사용여부"
            ,DECODE(BITAND(DISPLAY_BV,4),4,4,0) AS "컨텐츠표시여부"
            ,DECODE(BITAND(DISPLAY_BV,8),8,8,0) AS "Hybrid메뉴여부"
  FROM MENU2;

INSERT, UPDATE문은 각각의 항목값을 모두 더해서 처리하면 됩니다.


UPDATE MENU2
      SET DISPLAY_BV = 1 + 4 + 0 + 8
 WHERE MENU_ID = :MENU_ID;

이번에는 좀 더 난이도가 높은 UI를 보겠습니다.

원칙적으로는 비트속성값 모두를 한 화면에서 핸들링 해야 하지만, 업무요건상 그렇게 될 수 없다고 가정합니다. 아래 그림을 보시면 메뉴관리 화면에서는 Menu Image Use 항목과 Is Hybrid menu 항목은 메뉴관리 화면에서 관리하고, 나머지 2개의 컬럼은 다른 화면에서 관리한다고 가정합니다.



이 경우에는 2^1(2) 과 2^3(8)만 화면에서 관리하므로 다른 비트값 2^0(1), 2^2(4) 값은 이 화면에서 핸들링하면 안됩니다.

SELECT문과 INSERT문은 이렇게 화면을 구성하더라도 기존과 동일하게 처리하면 된다. 하지만 UPDATE문은 다른쪽 화면에서 나머지 값에 대한 핸들링을 하므로 영향을 받지 않도록 다르게 처리해야 합니다.

현재 DIPLAY_BV 값을 7 (2^0(1) + 2^1(2) + 2^2(4))이라고 가정하겠습니다.

즉, 배너디스플레이여부=Y, 메뉴이미지사용여부=Y, 컨텐츠표시여부=Y, Hybrid메뉴여부=N인 상태입니다.
위의 메뉴화면에서 사용자가 Save 버튼 클릭시 값이 메뉴이미지사용여부=N, Hybrid메뉴여부=Y로 상태값이 변경되었다고 하면


현재값:                                      0111 (7)
화면에서 관리안하는 비트값의 합 : 0101 (5)
----------------------------------------------
두값의 BITAND 값                     : 0101 (5)

현재 설정값(비관리항목은0으로)  : 1000 (8)
----------------------------------------------
두값의 BITOR                           : 1101 (13)

먼저 현재값과 화면에서 관리하지 않는 항목에 해당되는 비트값의 BITWISE OR 값 (단순 더하기)을 구해서 두 값을 BITAND 합니다.
즉, 현재값인 7인 값과 화면에서 관리하지 않는 항목의 값 (배너디스플레이여부 2^0(1) + 컨텐츠표시여부 2^2(4))을 더한 값과 서로 BITAND 연산을 합니다. 결과가 0101 (5)가 나오는군요.
이 값과 현재화면에서 설정값(단, 관리안하는 UI항목에 대해서는 전부 0으로 비트값처리, 메뉴이미지사용여부=N이므로 0, Hybrid메뉴여부=Y이므로 8)의 합을 서로 BITOR 연산합니다. 5 BITWISE OR 8 = 13가 나오는군요.
13값을 저장하면 됩니다.

다음과 같은 UPDATE문이 나올 수 있습니다.


UPDATE MENU2
   SET DISPLAY_BV = PKG_UTILS.BITOR(BITAND(DISPLAY_BV,5),DECODE('메뉴이미지사용여부','Y',2,0) + DECODE('Hybrid메뉴여부','Y',8,0))
 WHERE MENU_ID = 1;

* DECODE 부분은 UI 값을 대응되는 비트값으로 구성했다면 DECODE없이 바로 더하면 됩니다.

 BITOR 함수는 오라클에서는 DEFAULT로 제공하지 않습니다. 그래서 아래와 같은 PL/SQL 패키지에서 BIT관련 함수를 구현했습니다.

그리고 패키지 주석을 보시면 이해하시겠지만, 특정 비트자리수의 값을 0으로 혹은 1로 설정할 수 있는 BIT_SET 함수도 만들어 두었습니다.
이 함수를 위 사례에 적용해서 만일 현재값과 상관없이 Hybrid메뉴여부(2^3=8) 항목을 CHECKED로 만들려고 하면 BIT_SET(컬럼값, 8, 1)로 함수를 호출하면 됩니다. 마지막 인자값은 두번째 인자값에 해당되는 비트자리수의 값을 1 혹은 0으로 설정하도록 하는 값(1/0)이 인자로 넘어갑니다.

좀 전에 사용했던 UPDATE문을 동일하게 BIT_SET 함수로 처리할 수 있습니다.

UPDATE MENU2
      SET DISPLAY_BV = PKG_UTILS.BIT_SET(PKG_UTILS.BIT_SET(DISPLAY_BV,2,0), 8,1)
 WHERE MENU_ID = 1;


BITWISE 관련 오라클 유저 함수 (패키지)

다음의 패키지(스크립트)는 오라클에서 BITAND 함수만을 제공하기 때문에 원할한 BIT연산을 위해서 관련 함수를 구현한 코드입니다. 참고하시기 바랍니다.


CREATE OR REPLACE PACKAGE PKG_UTILS AS
/******************************************************************************
   NAME        : PKG_UTILS
   PURPOSE     : 시스템 공통으로 사용되는 함수, 프로시저 등을 제공하는 패키지
   DESCRIPTION : UTILITY OPERATION PACKAGE
   REVISIONS   : 1.0
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2011-04-01  Jinook,lee       1. Created this package.
******************************************************************************/
 
    -------------------------------------------------------
    -- DESC : X, Y 값을 서로 BIT OR 연산한다.
    -- OR 연산은 두 값의 각 자릿수를 비교해, 둘 중 하나라도 1이 있다면 1을, 아니면 0을 계산한다
    -- x, y : 비트 연산할 숫자
    -- return   : bit or 연산한 결과값
    --------------------------------------------------------
    FUNCTION BITOR(x NUMBER, y NUMBER)
    RETURN NUMBER DETERMINISTIC;
   
    -------------------------------------------------------
    -- DESC : X, Y 값을 서로 BIT XOR 연산한다.
    -- XOR 연산은 두 값의 각 자릿수를 비교해, 값이 같으면 0, 다르면 1을 계산한다
    -- x, y : 비트 연산할 숫자
    -- return   : BIT XOR 연산한 결과값
    --------------------------------------------------------
    FUNCTION BITXOR(x NUMBER, y NUMBER)
    RETURN NUMBER DETERMINISTIC;
   
    -------------------------------------------------------
    -- DESC : X 값을 BIT NOT 연산한다.
    -- NOT 연산은 각 자릿수의 값을 반대로 바꾸는 연산이다.
    -- x : 비트 연산할 숫자
    -- return   : BIT NOT 연산한 결과값
    --------------------------------------------------------
    FUNCTION BITNOT(x NUMBER)
    RETURN NUMBER DETERMINISTIC;
   
    -------------------------------------------------------
    -- DESC : 닷컴의 비트 컬럼값의 특정비트를 0으로 만드는 함수이다.
    -- 내부적으로 X & ^Y 로 연산한다.
    -- x, y : 비트 연산할 숫자
    -- return   : BIT 마스크 연산한 결과값
    --------------------------------------------------------
    FUNCTION BIT_MASK(x NUMBER, y NUMBER)
    RETURN NUMBER DETERMINISTIC;
   
    -------------------------------------------------------
    -- DESC : 닷컴의 비트 컬럼값의 특정비트를 주어진 값으로 만드는 함수이다.
    -- x,y : 비트 연산할 숫자
    -- z : x값의 y자리수를 값을 설정할 값 (0 or 1)
    -- return   : BIT SET 연산한 결과값
    --------------------------------------------------------
    FUNCTION BIT_SET(x NUMBER, y NUMBER, Z NUMBER)
    RETURN NUMBER DETERMINISTIC;
   
END PKG_UTILS;
/


CREATE OR REPLACE PACKAGE BODY PKG_UTILS AS
/******************************************************************************
   NAME        : PKG_UTILS
   PURPOSE     : 시스템 공통으로 사용되는 함수, 프로시저 등을 제공하는 패키지
   DESCRIPTION : UTILITY OPERATION PACKAGE
   REVISIONS   : 1.0
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2011-04-01  Jinook,lee       1. Created this package.
******************************************************************************/
 
    -------------------------------------------------------
    -- DESC : X, Y 값을 서로 BIT OR 연산한다.
    -- OR 연산은 두 값의 각 자릿수를 비교해, 둘 중 하나라도 1이 있다면 1을, 아니면 0을 계산한다
    -- x, y : 비트 연산할 숫자
    -- return   : bit or 연산한 결과값
    --------------------------------------------------------
    FUNCTION BITOR(x NUMBER, y NUMBER)
    RETURN NUMBER DETERMINISTIC
    IS
    BEGIN
        RETURN x + y - BITAND(x, y);
    END;
   
    -------------------------------------------------------
    -- DESC : X, Y 값을 서로 BIT XOR 연산한다.
    -- XOR 연산은 두 값의 각 자릿수를 비교해, 값이 같으면 0, 다르면 1을 계산한다
    -- x, y : 비트 연산할 숫자
    -- return   : BIT XOR 연산한 결과값
    --------------------------------------------------------
    FUNCTION BITXOR(x NUMBER, y NUMBER)
    RETURN NUMBER DETERMINISTIC
    IS
    BEGIN
        RETURN BITOR(x,y) - BITAND(x,y);
    END;
   
    -------------------------------------------------------
    -- DESC : X 값을 BIT NOT 연산한다.
    -- NOT 연산은 각 자릿수의 값을 반대로 바꾸는 연산이다.
    -- x : 비트 연산할 숫자
    -- return   : BIT NOT 연산한 결과값
    --------------------------------------------------------
    FUNCTION BITNOT(x NUMBER)
    RETURN NUMBER DETERMINISTIC
    IS
    BEGIN
        RETURN (0 - x) - 1;
    END;
   
    -------------------------------------------------------
    -- DESC : 닷컴의 비트 컬럼값의 특정비트를 0으로 만드는 함수이다.
    -- 내부적으로 X & ^Y 로 연산한다.
    -- x, y : 비트 연산할 숫자
    -- return   : BIT 마스크 연산한 결과값
    --------------------------------------------------------
    FUNCTION BIT_MASK(x NUMBER, y NUMBER)
    RETURN NUMBER DETERMINISTIC
    IS
    BEGIN
        RETURN BITAND(x, BITNOT(y));
    END;
   
   
    -------------------------------------------------------
    -- DESC : 닷컴의 비트 컬럼값의 특정비트를 주어진 값으로 만드는 함수이다.
    -- x,y : 비트 연산할 숫자
    -- z : x값의 y자리수를 값을 설정할 값 (0 or 1)
    -- return   : BIT SET 연산한 결과값
    --------------------------------------------------------
    FUNCTION BIT_SET(x NUMBER, y NUMBER, z NUMBER)
    RETURN NUMBER DETERMINISTIC
    IS
    BEGIN
        IF z = 1 THEN
            RETURN BITOR(x, y);
        ELSIF z = 0 THEN
            RETURN BITAND(x, BITNOT(y));
        ELSE
            RAISE PKG_GLOBAL.ERR_WRONG_PARAMETER;
        END IF;
    END;
END PKG_UTILS;
/

* BIT_SET 함수에서 RAISE PKG_GLOBAL.ERR_WRONG_PARAMETER; 부분은 제가 자주 사용하는 ERROR를 별도의 GLOBAL 패키지에 선언해두고 참조해서 사용할 수 있도록 구성한 부분입니다.  해당 부분 삭제하시고 패키지 컴파일 하시거나 일반적인 PL/SQL ERROR RAISE 하는 구문으로 대체하시면 됩니다.


저작자 표시 비영리 변경 금지
Trackback 0 Comment 2
  1. 신.. 2011/09/09 01:09 address edit & del reply

    참기발한 방법이군요.
    여부컬럼하면 11100111 혹은 YYYNNYYY 만 알고 있는데 이런 방법도 있군요.
    잘 봅니ㄴ다..

  2. 땅콩맨 2012/05/14 15:36 address edit & del reply

    안녕하세요
    MSSQL에서 SQLite로 쿼리를 바꾸려고 하는데
    sql = "declare @num245(4), @num345 char(4) select @num245=autogubun from staff.dbo.ONR_TV_Station_tbl where num=245 select @num345=autogubun from staff.dbo.ONR_TV_Station_tbl where num=345 거든요. 원래 쿼리가...

    바꾸려고 하니깐 declare도 그렇고 select 문 2개 이어서 쓰는방식도 그렇고
    바꾸는 방법을 못찾겠는데, 혹시 이것에 대해서 답변좀 부탁드릴께요~

    이럴줄 알았으면 SQLite 공부좀 해둘껄 그랬어요... 홀홀홀...