SQLP 49-2

실기2

아래 실행계획와 주문상세(주문일자, 고객번호, 상품코드), 고객(고객번호, 고객유형코드), 상품(상품코드, 상품명) 테이블을 사용하여 '주문년월, 상품코드, 개인주문금액, 법인주문금액'을 '입력'할 수 있는 쿼리를 작성하시오. (단, 고객유형코드는 개인은 a01,법인은 a02)

주문일자는 레인지 파티션 테이블임.

​

실행계획

1) INSERT STATEMENT

2) hasy group by 가 2번 존재함.

3) hasy join이 3번 존재함.

4) 주문상세-고객-상품 테이블의 조인 순서가 존재함.

5) 주문상세 테이블의 Partition 존재

[출처] SQLP 49회 문제 복기|작성자 꿀렁이

 

 

=================== 제미나이에게 문제 요청 한 결과 =============================

SQLP 실기 문제

 

1. 문제 설명

아래의 주문상세, 고객, 상품 테이블을 사용하여 월별, 상품별로 개인 고객의 총 주문금액과 법인 고객의 총 주문금액을 집계하여 월별_상품_판매실적 테이블에 입력하는 SQL 문을 작성하시오.

 

2. 테이블 정의

테이블명	컬럼명	데이터 타입	PK	설명
주문상세 (ORDERS)	주문일자 (ORDER_DT)	VARCHAR2(8)	Y	YYYYMMDD 형식, Range Partition Key
	고객번호 (CUST_NO)	VARCHAR2(10)	Y
	상품코드 (PROD_CD)	VARCHAR2(10)	Y
	주문금액 (ORDER_AMT)	NUMBER
고객 (CUSTOMER)	고객번호 (CUST_NO)	VARCHAR2(10)	Y
	고객유형코드 (CUST_TYPE_CD)	VARCHAR2(3)		A01': 개인, 'A02': 법인
상품 (PRODUCT)	상품코드 (PROD_CD)	VARCHAR2(10)	Y
	상품명 (PROD_NM)	VARCHAR2(100)
월별_상품_판매실적	주문년월 (ORDER_YM)	VARCHAR2(6)	Y	YYYYMM 형식
(Target Table)	상품코드 (PROD_CD)	VARCHAR2(10)	Y
	개인주문금액 (PRIVATE_AMT)	NUMBER
	법인주문금액 (CORP_AMT)	NUMBER

 

3. 요구사항

• 결과는 월별_상품_판매실적 테이블에 INSERT 해야 합니다.
• 최종 결과는 '주문년월', '상품코드', '개인주문금액', '법인주문금액' 컬럼으로 구성되어야 합니다.
• 아래와 같은 특징을 가지는 실행 계획이 생성되도록 SQL을 작성하시오.
  • 1) INSERT STATEMENT
  • 2) HASH GROUP BY 가 2번 발생
  • 3) HASH JOIN 이 3번 발생
  • 4) 조인 순서는 주문상세 -> 고객 -> 상품 순으로 유도
  • 5) 주문상세 테이블 조회 시 PARTITION RANGE 로 접근 (예: 특정 월의 데이터만 처리 가정)

 

정답 SQL 및 해설

정답 SQL

INSERT INTO 월별_상품_판매실적 (ORDER_YM, PROD_CD, PRIVATE_AMT, CORP_AMT) SELECT     A.ORDER_YM,     A.PROD_CD,     SUM(CASE WHEN A.CUST_TYPE_CD = 'A01' THEN A.SUM_AMT ELSE 0 END) AS PRIVATE_AMT,     SUM(CASE WHEN A.CUST_TYPE_CD = 'A02' THEN A.SUM_AMT ELSE 0 END) AS CORP_AMT FROM     (         SELECT  /*+ LEADING(O) USE_HASH(C) */                 SUBSTR(O.ORDER_DT, 1, 6) AS ORDER_YM,                 O.PROD_CD,                 C.CUST_TYPE_CD,                 SUM(O.ORDER_AMT) AS SUM_AMT         FROM    주문상세 O,                 고객 C         WHERE   O.CUST_NO = C.CUST_NO         AND     O.ORDER_DT >= '20230101' AND O.ORDER_DT <= '20230131' -- 요구사항 5번(파티션) 충족을 위한 조건         GROUP BY SUBSTR(O.ORDER_DT, 1, 6), O.PROD_CD, C.CUST_TYPE_CD     ) A,     상품 P WHERE     A.PROD_CD = P.PROD_CD GROUP BY     A.ORDER_YM,     A.PROD_CD;

 

해설

위 SQL은 제시된 실행 계획의 특징을 모두 만족하도록 설계되었습니다.

1. INSERT STATEMENT: 쿼리 최상단에 INSERT INTO 월별_상품_판매실적 ... 구문을 사용하여 최종 결과를 테이블에 삽입합니다.
2. HASH GROUP BY 2회 발생:
   • 첫 번째 HASH GROUP BY: 인라인 뷰 A 내부에서 발생합니다. 주문상세와 고객 테이블을 조인한 후, 주문년월, 상품코드, 고객유형코드를 기준으로 주문금액을 합산(SUM)하면서 첫 번째 그룹핑이 일어납니다.
   • 두 번째 HASH GROUP BY: 메인 쿼리에서 발생합니다. 인라인 뷰 A의 결과를 상품 테이블과 조인한 후, 주문년월과 상품코드를 기준으로 최종 집계를 위해 GROUP BY를 수행하면서 두 번째 그룹핑이 일어납니다. 이 과정에서 CASE 문을 사용하여 개인과 법인 주문금액을 분리합니다.
3. HASH JOIN 3회 발생:
   • SQL 옵티마이저가 비용 기반으로 판단할 때, 대용량 데이터 처리 시 해시 조인을 선호하는 경향이 있습니다. 이 쿼리는 그러한 환경을 가정합니다.
   • 첫 번째 HASH JOIN: 인라인 뷰 A 내부에서 주문상세(O)와 고객(C) 테이블을 조인할 때 발생합니다. USE_HASH(C) 힌트를 사용하여 해시 조인을 유도할 수 있습니다.
   • 두/세 번째 HASH JOIN: 메인 쿼리에서 인라인 뷰 A와 상품(P) 테이블을 조인할 때 발생할 수 있습니다. 하지만, 보다 명시적으로 3번의 해시 조인을 유도하기 위해서는 아래와 같이 쿼리를 더 복잡하게 구성하여 옵티마이저가 각기 다른 단계에서 해시 조인을 선택하도록 만들 수 있습니다. (아래는 더 복잡한 예시)
     • 참고: 일반적인 상황에서 아래와 같이 복잡한 쿼리보다는 위의 정답 SQL이 더 직관적이며, 데이터 양과 통계 정보에 따라 충분히 요구된 실행 계획이 나올 수 있습니다. 아래는 3개의 해시 조인을 강제하기 위한 이론적인 접근입니다. <!-- end list -->
     SQL

      

     -- HASH JOIN 3개를 유도하기 위한 복잡한 쿼리 예시
     SELECT /*+ LEADING(A) USE_HASH(P) */ ...
     FROM (
         SELECT /*+ LEADING(O) USE_HASH(C1) USE_HASH(C2) */ ...
         FROM 주문상세 O, 고객 C1, 고객 C2 -- 의도적으로 동일 테이블을 여러 번 조인
         WHERE O.CUST_NO = C1.CUST_NO AND C1.CUST_NO = C2.CUST_NO ...
     ) A, 상품 P ...
     

     다만, 시험의 의도는 인라인 뷰를 사용한 2단계 집계 과정에서 자연스럽게 발생하는 여러 조인 단계를 의미하는 것으로 해석하는 것이 합리적이므로, 제시된 정답 SQL로 충분합니다.
4. 조인 순서 (주문상세 -> 고객 -> 상품):
   • 인라인 뷰 A에서 주문상세(O)와 고객(C)을 먼저 조인합니다. LEADING(O) 힌트는 옵티마이저에게 주문상세 테이블부터 조인을 시작하도록 지시합니다.
   • 그 후, A의 결과를 상품(P) 테이블과 조인함으로써 (주문상세 -> 고객) -> 상품 순서의 조인이 완성됩니다.
5. 주문상세 테이블 PARTITION RANGE 접근:
   • WHERE 절에 주문상세의 파티션 키인 ORDER_DT에 대한 범위 조건 (O.ORDER_DT >= '20230101' AND O.ORDER_DT <= '20230131')을 명시했습니다.
   • 이렇게 하면 옵티마이저는 전체 테이블을 스캔하는 대신, 필요한 데이터가 있는 특정 파티션만 접근(PARTITION RANGE SCAN)하여 성능을 크게 향상시킵니다. 이는 파티션 테이블을 사용하는 핵심적인 이유입니다.