카테고리 없음

SQL BOOSTER - CARTESIAN_JOIN

now0204 2024. 6. 21. 11:09

 

1. CARTESIAN-JOIN 이해하기

  • 조인 조건이 없어 묻지마 조인을 처리한다. 
  • FROM 절에 테이블 A와 B가 있을 때 조인 조건을 주지 않으면 카테시안 조인이다. A의 첫번째 데이터는 B의 모든 데이터와 조인을 하게 된다. 두 번째 데이터도 마찬가지다. 
SELECT T1.CUS_GD, T2.ITM_TP
FROM (SELECT DISTINCT A.CUS_GD FROM M_CUS A) T1, (SELECT DISTINCT A.ITM_TP FROM M_ITM A) T2
  • CUS_GD의 데이터 집합 
  • ITEM_TP의 데이터 집합 
  • 두 조인의 조건이 없으니 카테시안 조인을 수행      

SQL BOOSTER

  • 카테시안 조인이 사용되는 경우는 매우 드물다.. 
  • BI 환셩에서 분석차원 집합을 만들거나 시스템 오픈 전에 대량의 데스트 데이터 만들기 위해 일회성으로 사용한다. 

2. CARTESIAN-JOIN의 위험성

 

카테시안 조인은 매우 위험한 조인이다. 실수로 카테시안-조인이 발생하면, 시스템 장애가 일어날 수도 있다.

아래는 실수로 조인 조건을 주지 않은 예다 .

SELECT COUNT(*)
FROM T_ORD T1, T_ORD_DET T2;

 

  • T_ORD에는 3.047, T_ORD_DET에는 3,224건의 데이터가 있다. 두 테이블간 카테시안 조인을 수행하면 결과는 9백8십만건의 결과가 나온다.
  • 이는 WAS에 장애를 발생시키에 충분하다. 
SELECT COUNT(*)
FROM T_ORD T1, T_ORD_DET T2
WHERE T1.ORD_SEQ = T1.ORD_SEQ;

 

4번 라인의 조인 조건을 보면, T1테이블의 ORD_SEQ만 사용되고 있는데, 별칭 실수로 카테시안 조인을 수행해버린다.

따라서, 자신이 만든 SQL을 실행하기 전에 조인 조건을 한 번 더 확인하는 습관을 갖는 것이 좋다. 

 

 

3. 분석마스터 만들기 

 

3.1 카테시안 조인 활용 1

 

카테시안-조인은 분석 마스터를 만들 때 유용하다.

 

특정 고객 두 명의 2월 3월 4월 실적을 조회하는 SQL을 구현해 보자.

 

SELECT T1.CUS_ID, T1.CUS_NM, T2.ORD_YM, T2.ORD_CNT
FROM M_CUS T1, 
(SELECT A.CUS_ID, TO_CHAR(A.ORD_DT,'YYYYMM')ORD_YM, COUNT(*) ORD_CNT
FROM T_ORD A
WHERE A.CUS_ID IN('CUS_0003','CUS_0004')
AND A.ORD_DT >= TO_DATE('20170201','YYYYMMDD')
AND A.ORD_DT < TO_DATE('20170501','YYYYMMDD')
GROUP BY A.CUS_ID, TO_CHAR(A.ORD_DT,'YYYYMM')) T2
WHERE T1.CUS_ID IN ('CUS_0003','CUS_0004') 
AND T1.CUS_ID = T2.CUS_ID(+)
ORDER BY T1.CUS_ID, T2.ORD_YM

 

위 SQL을 실행하면, 고객별로 주문이 존재하는 월만 조회된다. 

출처 : SQLBOOSTER

 

만약 주문이 없는 월도 주문 건수를 0으로 보여주고 싶다면, 카테시안 조인을 수행하자 

 

SELECT T0.CUS_ID, T0.CUS_NM, T0.BASE_YM, NVL(T2.ORD_CNT,0) ORD_CNT
FROM 
 (SELECT T1.CUS_ID, T1.CUS_NM, T4.BASE_YM
   FROM M_CUS T1, 
    (
      SELECT 
      TO_CHAR(ADD_MONTHS(TO_DATE('20170201','YYYYMMDD'),ROWNUM-1),'YYYYMM') 
      BASE_YM
      FROM DUAL
      CONNECT BY ROWNUM <=3 
     )T4
  WHERE T1.CUS_ID IN('CUS_0003','CUS_0004')
   ) T0
, (
  SELECT A.CUS_ID, TO_CHAR(A.ORD_DT,'YYYYMM') ORD_YM, COUNT(*) ORD_CNT
  FROM T_ORD A
  WHERE A.CUS_ID IN ('CUS_0003','CUS_0004')
  AND A.ORD_DT >= TO_DATE('20170201','YYYYMMDD')
  AND A.ORD_DT < TO_DATE ('20170501','YYYYMMDD')
  GROUP BY A.CUS_ID, TO_CHAR(A.ORD_DT,'YYYYMM'))T2
WHERE T0.CUS_ID = T2.CUS_ID(+)
AND T0.BAE_YM = T2.ORD_YM(+)
ORDER BY T0.CUS_ID, T0.BASE_YM;

 

SQL BOOSTER

SQL을 실행하면 위와 같은 과정을 통해 주문이 없는 월도 0건으로 조회할 수 있다. 

 

1. M_CUS에서 두 명의 고객만 선택 (SQL11번)

2. CONNECT BY ROUNUM을 이용 3개월 치의 데이터를 생성 

3. 1번과 2번을 카테시안 조인 처리 고객별로 3개월씩 총 6건의 마스터가 만들어진다.

4. T_ORD에서 특정 고객들의 3개월간 주문에 대해 고객+주문년월별 건수를 집계 

5. 3번을 기준 데이터 집합으로 4번과 아우터 조인 수행 

 

3. 2 카테시안 조인 활용 2

 

SELECT A.CUS_Gd, D.ITM_TP, SUM(C.ORD_QTY) ORD_QTY
FROM M_CUS A, T_ORD B, T_ORD_DET C, M_ITM D
WHERE A.CUS_ID = B.CUS_ID
AND C.ORD_SEQ = B.ORD_SEQ
AND D.ITM_ID = C.ITM_ID
AND B.ORD_ST = 'COMP'
GROUP BY A.CUS_GD, D.ITM_TP
ORDER BY A.CUS_GD, D.ITM_TP;

 

위 SQL을 실행하면, 고객등급별, 아이템유형별 주문량을 구할 수 있다. 

 

하지만, 아이템 유형 중 주문량이 없는 아이템에 대해서는 나오지 않는다. 

이때 주문이 없는 아이템을 포함 시키기 위해 카테시안 조인을 활용할 수 있다. 

 

SELECT T0.CUS_GD,T0.ITM_TP, NVL(T3.ORD_QTY,0) ORD_QTY
FROM ( 
        SELECT T1.CUS_GD,T2.ITM_TP
        FROM (SELECT A.BAS_CD CUS_GD FROM C_BAS_CD A WHERE A.BAS_CD_DV='CUS_GD') T1
             ,(SELECT A.BASE_CD ITM_TP FROM C_BAS_CD A WHERE A.BASE_CD_D ='ITM_TP') T2
      )T0
      ,( SELECT A.CUS_GD, D.ITM_TP, SUM(C.ORD_QTY) ORD_QTY
        FROM M_CUS A, T_ORD B, T_ORD_DET C, M_ITM D
        WHERE A.CUS_ID = B.CUS_ID
        AND C.ORD_SEQ = B.ORD_SEQ
        AND D.ITM_ID = C.ITM_ID
        AND B.ORD_ST ='COMP'
        GROUP BY A.CUS_GD, D.ITM_TP
      )T3
 WHERE T0.CUS_GD = T3.CUS_GD(+)
 AND T.ITM_TP = T3.ITM_TP(+)
 ORDER BY T0.CUS_GD, T0.ITM_TP;

 

- 2~4번 라인을 확인해보면, 카테시안 조인을 이용해 고객등급과 아이템 유형의 조합 가능한 모든 데이터를 만들고 있다.

기준 코드 테이블에 입력된 고객등급과 아이템 유형을 사용했다. 

 

4. 테스트 데이터 만들기 

 

대량의 테스트 데이터를 만들기 위해 카테시안-조인을 활용할 수 있다. 

 

SELECT ROWNUM ORD_NO, T1.CUS_ID, T2.ORD_ST, T3.PAY_TP, T4.ORD_DT
FROM M.CUS_T1,
(SELECT 'WAIT' ORD_ST FROM DUAL UNION ALL SELECT 'COMP' ORD_ST FROM DUAL) T2
,(SELECT 'CARD' PAY_TP FROM DUAL UNION ALL SELECT 'BANK' PAY_TP FROM DUAL UNION ALL 
  SELECT NULL PAY_TP FROM DUAL) T3,
(SELECT TO_DATE('20170101','YYYYMMDD') + (ROWNUM-1) ORD_DT 
FROM DUAL 
CONNECT BY ROWNUM <= 365) T4;

 

고객 테이블과 3,4번 라인의 주문상태 집합을 카테시안-조인하면 고객별로 모든 주문상태가 만들어진다.

여기에 5~7번 라인의 지불유형 집합까지 카테시안-조인을 하면, 고객-주문상태-지불유형별로 조합 가능한 모든 데이터가 만들어진다. 마지막으로 9~11번 라인의 1년 치의 일자 데이터를 카테시안 조인하면 테스트 데이터를 만들 수 있다. 

 

부하 테스트나 성능 테스트를 위해 더 많은 데이터가 필요하면, 아래처럼 의미 없는 숫자 집합을 

인라인-뷰로 만들어서 카테시안-조인을 추가하면 된다. 아래 SQL은 1부터 10까지의 숫자 결과를 만든다

즉 열 배의 테스트 데이터를 만들어 낼 수 있다. CONNECT BY ROWNUM의 숫자 조건 값만 조정하면 더 많은 데이터를 만들 수 있다. 

 

SELECT ROWNUM RNO FROM DUAL A CONNECT BY ROWNUM <=10;

 

테스트 데이터를 만들 때, 테이블별로 분포도를 조정해야 할 때가 있다. 예를 들어 주문 상태가 WAIT인 것은 전체 주문 중 2/5를 차지하고, 주문상태가 COMP인 것은 전체 주문 중 3/5를 차지하도록 테스트 데이터를 구성하는 것이다. 

 

SELECT 'WAIT' ORD_ST FROM DUAL CONNECT BY ROWNUM <= 2 UNION ALL 
SELECT 'COMP' ORD_ST FROM DUAL CONNECT BY ROWNUM <=3

 

카테시안-조인은 적재적소에 활용하면 매우 유용할 수 있다. 

 

참고자료 

https://www.yes24.com/Product/Goods/82818767

 

SQL BOOSTER - 예스24

SQL BOOSTER는 프로젝트 성공을 위한 SQL 필독서다. 이 책은 마치 프로젝트를 진행하는 순서처럼 구성되어 있다. 프로젝트 투입을 위해 필요한 SQL 기술을 설명하고, 성능 테스트를 위해 필요한 기술

www.yes24.com