SQL BOOSTER - CARTESIAN_JOIN

 

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