Post List

2015년 1월 9일 금요일

대용량 데이터베이스 솔루션 2권 #08 NON-EQUAL JOIN

Non-Equal JOIN (비동치 JOIN)



위 ER-D를 보면 어음수불란에 어음시작번호와 종료번호가 있다.
어음수불에서 지급된 어음은 별도 Table로 관리가 되는데, 어음번호를 Key로 하고 있다.
그런데 ER-D를 보면 어음수불 Table과 지급어금 Table 사이에 Relationship이 있다.
저게 왜 필요할까 ? 굳이 Foreign Key로 연결하지 않아도 이미 의미적으로 Relationship이 존재한다.
그런데 저렇게 설계한 이유가 뭘까 ?
바로 JOIN 그러면 EQUAL JOIN 밖에 없다고 생각을 하기 때문이다.
EQ JOIN 관계로 하면 필요없는 Foreign Key 관련하여 2개의 Column만큼 낭비하게 된다.
위의 경우에는 BETWEEN을 이용하여 처리하면 된다.

A.어음번호 BETWEEN B.시작번호 AND B.종료번호

BETWEEN 활용
Column_name BETWEEN :start_value AND :end_value  -- 한 Column의 범위처리

:value BETWEEN Start_Column AND End_Column -- 두 Column의 범위처리

Start_Column <= :value AND End_Column >= :value
위의 3가지 모양으로 표현하여 사용한다.
하지만 대부분 사람들이 BETWEEN을 사용하면 기존의 EQUAL JOIN보다 더 느리다고 얘기를 한다.
그건 사용법을 잘 모르기 때문이다.
사용 용도에 따라 다르겠지만, 이런 종류의 범위처리는 주로 이력관리 등에서 시간정보를 활용하는데 사용한다.
그럴 경우 대부분의 경우 최근 Data를 Access 하는 경우가 많다.



그런데 만약 INDEX를 시작컬럼-종료컬럼 으로 생성하였을 경우 어떻게 될까 ? BETWEEN 연산이기 때문에 만약 :value 값이 10이라면 시작컬럼을 2번만에 찾은 뒤 종료컬럼도 바로 찾았다. 하지만 :value가 520이라면 Table의 처음부터 Scan하여 501까지 찾아간다. 그런뒤 종료값 600을 찾고서 종료되었다. 반대로 INDEX를 종료컬럼-시작컬럼으로 잡은 경우는 최근값일수록 더 빨리 찾고 예전값일수록 더 느리게 찾는다.

- 앞부분 Data Access에는 시작컬럼 + 종료컬럼이 유리
- 뒷부분 Data Access에는 종료컬럼 + 시작컬럼이 유리
- rownum = 1 조건을 추가하는게 좋음
- 종료 Data는 Default에서 최고값으로 지정 ('99991231'). 만약 NULL이면 INDEX를 사용하지 않게됨

* 예제

과거 KT의 대리점 전화번호 할당 시스템이다.
그 당시 전화번호를 대리점에서 할당하기 위해서는
국번 123. 시작번호 1000, 끝번호 2000 을 선택 후 할당시작을 하면
TRANSACTION BEGIN - 1000번 할당 - 1001번 할당 - ... 2000번 할당 - COMMIT
가 되면 성공적으로 이루어 지지만
TRANSACTION BEGIN - 1000번 할당 - 1001번 할당 - ... 1956번 할당 실패 - ROLLBACK
가 되어버리면 하나도 할당받지 못하게 된다.
그래서 대리점마다 연속된 번호를 많이 할당받지 못하고, 노련한 대리점에서는 이상한 번호를 시작 끝으로 했었다.
496 ~ 513까지, 389 ~ 421까지 이런식으로 말이다.
그러면 다른 사람과 거의 동시에 시작했더라도 400 ~ 500 까지 할당받으려던 사람과 496 ~ 513으로 할당받으려던 사람 기준으로는
400 ~ 500은 실패가 떠서 하나도 할당받지 못하고, 496 ~ 513으로 할당받으려던 사람은 성공했었다.
왜 이런식의  시스템이 되었냐 ? 바로 모든 JOIN은 EQUAL로만 했었기 때문이다.
그러기 때문에 전화번호를 하나하나씩 할당을 하는 시스템이었다.



이것을 개선하는 작업을 하면서 BETWEEN 으로 비교하는 JOIN을 활용하였다.

번호할당 Table에 시작번호, 끝번호, 상태(사용/미사용) 의 Column정보를 두었다.
시작 2001, 종료 2100, 상태 미사용 에서 2001 ~ 2050번을 할당 신청을 한경우
먼저 해당 Record를 시작 2001, 종료 2050, 상태 사용 으로 UPDATE 한 후
시작 2051, 종료 2100, 상태 사용 을 INSERT 하는 방법을 사용하였다.

Cartesian 곱을 이용한 JOIN

1. 나열된 Column을 Record로 생성

SUM(DECODE)를 이용하여 Record 들을 Column 방향으로 나열하는 방법을 소개하였다.
그럼 반대로 Column Data들을 Record들로 분리하려면 어떻게 해야 할까 ?
Cartesian 곱을 이용하면 된다.

* 예제
한 통신업체의 계약Table에 반환금, 위약금, 기기철거비 가 하나의 Record에 Column정보로 각각 들어가 있다.
이것을 전표 Table에 INSERT 해야하는데, 위 3가지 정보들이 각각 다른 Record로 입력해야한다.
CREATE TABLE COPY_T (NO, NO2) as
  SELECT rownum,
         SUBSTR(TO_CHAR(rownum,'09'),2,2)
    FROM any_table
   WHERE rownum <= 31;

INSERT INTO 전표TABLE
       (생성일자, 계정과목, 금액)
SELECT TO_CHAR(sysdate,'YYYYMMDD'),
       DECODE(Y.NO,  1, '1234',
                     2, '5678',
                     3, '9876'),
       DECODE(Y.NO2, 1, 반환금,
                     2, 위약금,
                     3, 기기철거비)
  FROM 계약TABLE X, COPY_T Y 
 WHERE X.해약일 = :input
   AND Y.NO <= 3;
-- 묻지마 JOIN을 이용한 방법으로
-- COPY_T TABLE을 이용하여 금액이 있는 Column만큼 Row를 복제한 후
-- 각 Row마다 필요한 값을 지정하여 INSERT

INSERT INTO 전표TABLE
       (생성일자, 계정과목, 금액)
SELECT TO_CHAR(sysdate,'YYYYMMDD'),
       DECODE(Y.NO,  1, '1234',
                     2, '5678',
                     3, '9876'),
       DECODE(Y.NO2, 1, 반환금,
                     2, 위약금,
                     3, 기기철거비)
  FROM 계약TABLE X, COPY_T Y 
 WHERE X.해약일 = :input
   AND Y.NOT IN (DECODE(반환금,0,NULL,1),
                DECODE(위약금,0,NULL,2),
                DECODE(기기철거비,0,NULL,3));
-- 물어보고 JOIN을 이용한 방법으로
-- COPY_T TABLE을 이용하여 복제할 때 복제원 ROW 값에 따라
-- 복제할 양을 유동적으로 결정

2. 첨자 LOOP 형 처리

일정기간동안 해야 할 업무에 대해서 선급금으로 받을 경우 그 선급금을 받았을 당시로 모두 매출로 잡을 수는 없다. 실제 업무기간에 맞게 선급금을 나누어서 매출로 처리해야 한다.



위 그림에서 1101 계약에 대해서는 5월20일 에서 9월 25일까지 진행되는 업무이므로, 월별매출은 아래와 같이 계산되어야 한다.
이것을 Logic을 이용한 Program 말고, SQL로 해결이 가능하나 ?
물론 된다. 바로 월별로 계산할 것이 아니라 Cartesian JOIN을 이용하여 일별로 나눈다음 이것을 월별로 계산하면 된다.
이게 집합적인 사고의 통분 능력이다. 원하는 정보를 뽑아내기 위해서 Data를 어떻게 인수분해 한 다음에 합할 것인가.
INSERT INTO 월별매출
    (일련번호, 적용월, 매출금액)
SELECT 일련번호, SUBSTR(:작업월, 1, 4)||Y.NO2,
       (DECODE(Y.NO2, TO_CHAR(종료일, 'mm'), TO_CHAR(종료일, 'dd'), TO_CHAR(LAST_DAY(종료일)),'dd')) -
        DECODE(Y.NO2, TO_CHAR(시작일, 'mm'), TO_CHAR(시작일, 'dd'), '01') + 1) *
       (선급액/(종료일-시작일)+1))
  FROM 선급금 X, COPY_T Y
 WHERE X.발생일 LIKE :작업월||'%'
   AND Y.NO2 BETWEEN TO_CHAR(시작일,'mm') AND TO_CHAR(종료일,'mm');

-- COPY_T Table을 이용하여 필요한 개수만큼 Row를 복제함으로써
-- 마치 각 Row마다 n번의 LOOP가 수행되는 것처럼 사용
 3. 관계가 없는 Table들 간의 JOIN
SELECT 고객명, 부서, 호봉, 편성금액,
  INTO :cust_name, :dept, :salgrade, :budget_amt,
  FROM CUSTOMER, EMPLOYEE, BUDGET
 WHERE 고객번호 = :cust_no
   AND 사번 = :emp_no
   AND 부서(+) = :dept_no||SUBSTR(사번,0,0)
   AND 계정과목(+) = '1101'
   AND 예산년도(+) = TO_CHAR(sysdate,'yyyy');
 1 * 1 * 1 = 1 이므로 각각의 Table이 PK로 Access된다면 하나의 SQL로 JOIN 가능.
만약 존재하지 않는 경우가 우려된다면 OUTER JOIN

4. 처리결과를 고정양식에
SELECT Y.계정명,
       SUM(DECODE(X.부문, '원사', 금액)),
       SUM(DECODE(X.부문, '제직', 금액))
  FROM (SELECT SUBSTR(계정,1,2 항목,
               SUM(금액) 금액
          FROM 전표TABLE
         WHERE 공장CODE = '1공장'
           AND 일자 LIKE :작업월||'%'
           AND 계정 BETWEEN '1234' AND '6543'
         GROUP BY SUBSTR(계정,1,2)) X,
       계정TABLE Y
 WHERE Y.계정 BETWEEN '1200' AND '6500'
   AND Y.분류 = '1'
   AND X.항목(+) = SUBSTR(Y.계정,1,2);
GROUP BY 한 결과가 없는 항목도 추출되도록 모든 추출항목이 있는 집합과 OUTER JOIN
(Sort Merge 나 Hash JOIN으로 수행시킬것)

JOIN을 이용한 소계처리

이런 처리는 이미 앞에서 많이 해봐서 이젠 다들 잘 알 것이라 생각은 되지만 한번 더 보고 넘어가자.

A : 100
B : 100
C:   50
일 경우 합계는 250이다.
어떻게 하나의 SQL로 할까 ? 당연 묻지마 JOIN을 이용해서 Data들을 2개로 복사.
복사하면서 묻지마 TABLE의 NO를 각각 1,2로 했을 경우
1은 원래대로 출력 (A, B, C)하고 2인 경우는 '합계' 로 출력하여
GROUP BY SUM을 하면 '합계'인 것들은 다 합쳐지게 된다.


SELECT item,
       DECODE(NO1, 1, grage, '소계'),
       SUM(m_qty), SUM(m_amt), SUM(s_qty), SUM(s_amt)
  FROM (SELECT item, grade,
               SUM(m_qty) m_qty, SUM(m_amt) m_amt, SUM(s_qty) s_qty, SUM(s_amt) s_amt
          FROM TAB1
         WHERE yymm = :in_days
           AND saup = :saup
         GROUP BY item, grade) X,
       COPY_T Y
 WHERE Y.NO <= 2
 GROUP BY item, NO, DECODE(NO,1,grade,'소계')
 잴 마지막 GROUP BY를 DECODE(NO,1,'소계',grade) 로 바꾸면 소계가 먼저 나오고, 그 아래에 내역이 나오는 형식으로 출력할 수 있다.

작가
이화식
출판
엔코아컨설팅
발매
1999.11.20
평점
블로거의 오늘의 책에 참여한 포스트 입니다

댓글 없음:

댓글 쓰기