[SQL 튜닝 실습 2-5] 해시 조인 튜닝 2

인라인 뷰를 이용한 해시 조인으로 성능 극대화하기

 

실습 준비

 

테이블 생성 : TB_PRDT 생성

CREATE TABLE TB_PRDT				
(				
	PRDT_CD VARCHAR2(6), --상품코드			
	PRDT_NM VARCHAR2(50), --상품명			
	INST_DTM DATE, --입력일시			
	INST_ID VARCHAR2(50) --입력자			
);				

 

테이블 생성 : TB_PRDT_SALE_DAY

CREATE TABLE TB_PRDT_SALE_DAY				
(				
	SALE_NO VARCHAR2(10), --판매번호			
	SALE_DT VARCHAR2(8), --판매일자			
	SALE_TM VARCHAR2(6), --판매시간			
	SALE_CNT NUMBER(9), --판매수			
	SALE_AMT NUMBER(9), --판매금액			
	PRDT_CD VARCHAR2(6), --제품코드			
	INST_DTM DATE, --입력일시			
	INST_ID VARCHAR2(50) --입력자			
);				

 

데이터 입력 (10만건) : TB_PRDT테이블

INSERT INTO TB_PRDT				
SELECT				
	LPAD(TO_CHAR(ROWNUM), 6, '0'),			
	DBMS_RANDOM.STRING('U', 50),			
	SYSDATE,			
	'DBMSEXPERT'			
FROM DUAL CONNECT BY LEVEL <= 100000;				
				
COMMIT;				

 

NOLOGGING 모드 설정 : TB_PRDT_SALE_DAY 테이블

ALTER TABLE TB_PRDT_SALE_DAY NOLOGGING;

 

데이터 입력 (100만 건) : TB_PRDT_SALE_DAY 테이블

INSERT /*+ APPEND */  INTO TB_PRDT_SALE_DAY --APPEND 힌트 사용
SELECT
	LPAD(TO_CHAR(ROWNUM), 10, '0'),
	TO_CHAR(SYSDATE - DBMS_RANDOM.VALUE(1, 3650), 'YYYYMMDD'),
  TO_CHAR(SYSDATE - (DBMS_RANDOM.VALUE(1, 86400) / 24 / 60 / 60), 'HH24MISS'),	
  TRUNC(DBMS_RANDOM.VALUE(1, 10)),							
  TRUNC(DBMS_RANDOM.VALUE(1000, 100000), -3),							
  PRDT_CD,
  	SYSDATE,
	'DBMSEXPERT'
FROM TB_PRDT, (SELECT DUMMY FROM DUAL CONNECT BY LEVEL <= 10);
							
COMMIT;

 

기본키 설정

ALTER TABLE TB_PRDT				
ADD CONSTRAINT TB_PRDT_PK				
PRIMARY KEY (PRDT_CD);				
				
ALTER TABLE TB_PRDT_SALE_DAY				
ADD CONSTRAINT TB_PRDT_SALE_DAY_PK				
PRIMARY KEY (SALE_NO);				

 

외래키 설정

ALTER TABLE TB_PRDT_SALE_DAY	
ADD CONSTRAINT TB_PRDT_SALE_DAY_FK	
FOREIGN KEY (PRDT_CD) REFERENCES TB_PRDT(PRDT_CD);	

* 한 개의 제품은 0개 혹은 1개 이상의 판매내역을 가질 수 있다

* 하나의 판매내역은 반드시 1개의 제품을 가진다

 

통계정보 생성

ANALYZE TABLE TB_PRDT COMPUTE STATISTICS 						
FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254;						
						
ANALYZE TABLE TB_PRDT_SALE_DAY COMPUTE STATISTICS 						
FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254;						

 

---

튜닝 전 상황

 

튜닝 전 SQL문

1	SELECT 					
2	  B.PRDT_CD, 					
3	MIN(B.PRDT_NM),					
4	SUM(A.SALE_CNT), 					
5	SUM(A.SALE_AMT)					
6	FROM					
7	  TB_PRDT_SALE_DAY A, TB_PRDT B					
8	WHERE					
9	  A.SALE_DT BETWEEN '20120101' AND '20131231' AND					
10	  A.PRDT_CD = B.PRDT_CD					
11	GROUP BY B.PRDT_CD;					

실행속도 : 0.2초

 

[7]

TB_PRDT_SALE_DAY 테이블과 TB_PRDT 테이블을 조인한다.
조인 방식은 옵티마이저의 선택에 따른다

 

SQL문의 문제점

TB_PRDT 테이블과 TB_PRDT_SALE_DAY 테이블은 1:M 관계이다. 
1쪽 집합인 TB_PRDT 테이블의 PRDT_CD 기준으로 GROUP BY 해서 집계결과를 계산하고 있다.
이러한 SQL문은 인라인 뷰를 이용해서 M쪽 집합인 TB_PRDT_SALE_DAY 테이블을 먼저 GROUP BY 한 후
TB_PRDT 테이블과 1:1 관계를 만든 다음 조인 연산을 수행한다면 최적의 성능을 낼 수 있다

 

실행 계획

수행 순서 : 3 > 4 > 2 > 1 > 0

3 : TB_PRDT_SALE_DAY 테이블을 풀 스캔한다

4 : TB_PRDT 테이블을 풀 스캔한다

2 : 3번과 4번을 해시 조인한다

1 : GROUP BY 연산 수행한다

0 : SELECT 절의 연산 수행한다

 

---

튜닝

 

튜닝 후 SQL문

1	SELECT  /*+ NO_MERGE(A) */						
2	  B.PRDT_CD,						
3	  B.PRDT_NM,						
4	  A.SALE_CNT_SUM,						
5	  A.SALE_AMT_SUM						
6	FROM						
7	  (						
8		  	SELECT 				
9		     A.PRDT_CD,					
10		     SUM(A.SALE_CNT) SALE_CNT_SUM,					
11		     SUM(A.SALE_AMT) SALE_AMT_SUM					
12		   FROM TB_PRDT_SALE_DAY A					
13		   WHERE					
14		     A.SALE_DT BETWEEN '20120101' AND '20131231'					
15		   GROUP BY A.PRDT_CD					
16	   ) A,						
17	   TB_PRDT B						
18	WHERE						
19	  A.PRDT_CD = B.PRDT_CD;						

실행 속도 : 0.1초

 

[1]

NO_MERGE 힌트를 이용해서 인라인 뷰 A가 
메인쿼리와 같은 레벨로 View Merging 이 안되게 한다.

 

[7~16]

TB_PRDT_SALE_DAY 테이블을 인라인 뷰 내에서 읽는다.
PRDT_CD 기준으로 GROUP BY 해서 
TB_PRDT 테이블과 조인 연산을 최소화한다

 

수행 순서 : 4 > 3 > 2 > 5 > 1 > 0

4 : TB_PRDT_SALE_DAY 테이블을 풀 스캔한다

3 : GROUP BY 연산을 한다

2 : 4번과 3번의 연산을 인라인 뷰로 구성한다

5 : TB_PRDT 테이블을 테이블 풀 스캔한다

1 : 2번과 5번을 해쉬 조인한다

0 : SELECT 절의 연산을 수행한다