[SQL 튜닝 실습 2-2] 중첩 루프 조인 튜닝

실습 준비

 

테이블 생성 : TB_CUST

CREATE TABLE TB_CUST			
(			
    CUST_ID VARCHAR2(10), --고객ID			
    CUST_NM VARCHAR2(50), --고객명			
    BIRTH_DT VARCHAR2(8), --생일			
    SEX VARCHAR2(2),  --성별			
    PHONE_NO VARCHAR2(11), --폰번호			
    JOIN_DT VARCHAR2(8), --가입일자			
    INST_DTM DATE, --입력일시			
    INST_ID VARCHAR2(50), --입력자ID			
    UPDT_DTM DATE, --수정일시			
    UPDT_ID VARCHAR2(50) -- 수정자ID			
);			

 

테이블 생성 : TB_ORD

CREATE TABLE TB_ORD			
(			
    ORD_NO VARCHAR2(10), --주문번호			
    ORD_DT VARCHAR2(8), --주문일자			
    ORD_NM VARCHAR2(150), --주문이름			
    ORD_AMT NUMBER(15), --주문금액			
    PRDT_CD VARCHAR2(6), --상품코드			
    SALE_GB VARCHAR2(2), --판매구분			
    PAY_GB VARCHAR2(2), --결제구분			
    CUST_ID VARCHAR2(10), --고객ID			
    INST_DTM DATE, --입력시간			
    INST_ID VARCHAR2(50), --입력자ID			
    UPDT_DTM DATE, --수정시간			
    UPDT_ID VARCHAR2(5) --수정자ID			
);			

 

데이터 입력 : TB_ORD, 10만 건의 데이터

INSERT INTO TB_CUST							
SELECT							
LPAD(TO_CHAR(ROWNUM), 10, '0'),							
DBMS_RANDOM.STRING('U', 50),							
TO_CHAR(SYSDATE - TRUNC(DBMS_RANDOM.VALUE(3650, 36500)), 'YYYYMMDD'),		
LPAD(MOD(ROWNUM, 2), 2, '0'),							
LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(3650, 36500))), 11, '0'),
TO_CHAR(SYSDATE - TRUNC(DBMS_RANDOM.VALUE(1, 365*3)), 'YYYYMMDD'),
SYSDATE,							
'KSKY',							
NULL,							
NULL							
FROM DUAL CONNECT BY LEVEL <= 100000;							
							
COMMIT;							

 

테이블 생성 : DUAL_10 : 데이터 복제에 이용

CREATE TABLE DUAL_10		
(		
    DUMMY VARCHAR2(1)		
);		

 

데이터 삽입 : DUAL_10

INSERT INTO DUAL_10			
SELECT DUMMY 			
FROM DUAL CONNECT BY LEVEL <= 10;			
			
COMMIT;			

 

NOLOGGING 모드 설정 : TB_ORD

ALTER TABLE TB_ORD NOLOGGING;

 

데이터 삽입 : TB_ORD : 100만 건

INSERT /*+ APPEND */  INTO TB_ORD --APPEND 힌트 사용							
SELECT							
    LPAD(TO_CHAR(ROWNUM), 10, '0'),							
    TO_CHAR(SYSDATE-TRUNC(DBMS_RANDOM.VALUE(1, 3650)), 'YYYYMMDD'),
    DBMS_RANDOM.STRING('U', 150),							
    TRUNC(DBMS_RANDOM.VALUE(1000, 100000)),							
    LPAD(TO_CHAR(MOD(TRUNC(DBMS_RANDOM.VALUE(1, 1000)), 50)), 6, '0'),
    LPAD(TO_CHAR(MOD(TRUNC(DBMS_RANDOM.VALUE(1, 1000)), 3)), 2, '0'),
    LPAD(TO_CHAR(MOD(TRUNC(DBMS_RANDOM.VALUE(1, 1000)), 10)), 2, '0'),
  CUST_ID,							
    SYSDATE,							
    'KSKY',							
    NULL,							
    NULL							
FROM TB_CUST, DUAL_10;							
							
COMMIT;							

 

기본키 생성

ALTER TABLE TB_ORD			
ADD CONSTRAINT TB_ORD_PK 			
PRIMARY KEY(ORD_NO);			
			
ALTER TABLE TB_CUST			
ADD CONSTRAINT TB_CUST_PK 			
PRIMARY KEY(CUST_ID);			

 

외래키 생성

ALTER TABLE TB_ORD					
ADD CONSTRAINT TB_ORD_FK					
FOREIGN KEY (CUST_ID) REFERENCES TB_CUST(CUST_ID);					

* 한 명의 고객은 0개 혹은 1개 이상의 주문을 가질 수 있다.

* 하나의 주문은 반드시 한 명의 고객을 가진다 　

 

인덱스 구성 : TB_ORD (ORD_DT + ORD_NM)

CREATE INDEX TB_ORD_IDX01 ON TB_ORD(ORD_DT, ORD_NM);					

 

통계정보 생성 : 테이블과 인덱스에 대한 통계정보 생성

ANALYZE TABLE TB_CUST COMPUTE STATISTICS						
FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254;						
						
ANALYZE TABLE TB_ORD COMPUTE STATISTICS						
FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254;						

 

---

튜닝 전 상황

 

튜닝 전 SQL문

1	SELECT			
2	/*+ LEADING(A) USE_NL(B) */			
3	*			
4	FROM TB_CUST A, TB_ORD B			
5	WHERE A.CUST_NM LIKE 'L%'			
6	AND A.CUST_ID = B.CUST_ID			
7	AND B.ORD_DT BETWEEN 			
8	TO_CHAR(SYSDATE-365, 'YYYYMMDD') 			
9	AND TO_CHAR(SYSDATE, 'YYYYMMDD');			

수행 시간 : 4.5초

 

[2]

LEADING 힌트를 이용해서 TB_CUST 테이블의 Outer테이블로 설정,

USE_NL 힌트를 이용해서 TB_ORD 테이블과 중첩 루프 조인

 

[6]

TB_CUST 테이블과 TB_ORD 테이블을 CUST_ID로 조인함
TB_ORD 테이블에 CUST_ID를 선두 컬럼으로 한 인덱스가 존재하지 않으므로
TB_ORD 테이블은 테이블 풀 스캔으로 처리된다

 

SQL 문제점

TB_ORD 테이블에 CUST_ID를 선두 컬럼으로 구성된 인덱스가 존재하지 않는다.

이러한 상황에서는 TB_CUST 테이블의 결과 집합의 건수만큼 TB_ORD 테이블을 테이블 풀 스캔하게 된다

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

3 : TB_CUST 테이블을 테이블 풀 스캔
4 : TB_ORD 테이블을 테이블 풀 스캔
2 : TB_CUST 테이블과 TB_ORD 테이블을 중첩 루프 조인(NL), 즉, TB_CUST의 결과 집합의 건수만큼 TB_ORD 테이블을 테이블 풀 스캔한다
1 : 조건 절에 조건에 따른 필터링 처리를 한다
0 : SELECT 절의 연산 수행

 

---

튜닝

 

인덱스 구성

CREATE INDEX TB_CUST_IDX01 ON TB_CUST(CUST_NM);					
CREATE INDEX TB_ORD_IDX02 ON TB_ORD(CUST_ID, ORD_DT);					

* Outer 테이블인 TB_CUST 테이블의 효율적인 스캔을 위해 CUST_NM 컬럼으로 구성된 인덱스를 추가함. 
* Inner 테이블의 효율적인 스캔을 위해 CUST_ID + ORD_DT 컬럼으로 구성된 복합 인덱스를 생성함. 

 

 

통계정보 생성

신규로 생성한 인덱스에 대한 통계정보 생성

ANALYZE INDEX TB_CUST_IDX01 COMPUTE STATISTICS;
ANALYZE INDEX TB_ORD_IDX02 COMPUTE STATISTICS;

 

튜닝 후 SQL 문

1	SELECT				
2	/*+ LEADING(A) INDEX(A TB_CUST_IDX01) 				
3	       USE_NL(B) INDEX(B TB_ORD_IDX02)  */				
4	*				
5	FROM TB_CUST A, TB_ORD B				
6	WHERE A.CUST_NM LIKE 'L%'				
7	AND A.CUST_ID = B.CUST_ID				
8	AND B.ORD_DT BETWEEN 				
9	TO_CHAR(SYSDATE-365, 'YYYYMMDD') 				
10	AND TO_CHAR(SYSDATE, 'YYYYMMDD');				

수행시간 : 0.006 초

 

[2, 3]

LEADING 힌트를 이용해서 TB_CUST 테이블을 Outer 테이블로 지정
INDEX 힌트를 사용해서 TB_CUST_IDX01 인덱스를 스캔하도록 지정
해당 인덱스는 ORG_NM컬럼으로 이루어져 효율적인 인덱스 스캔 가능.

 

[7]

USE_NL 힌트를 이용함으로써 중첩 루프 조인 유도
TB_ORD 테이블을 INNER 테이블로 지정
INDEX 힌트를 사용해서 TB_ORD_IDX02 인덱스 스캔하도록 유도
해당 인덱스는 CUST_ID + ORD_DT 컬럼으로 이뤄진 복합 인덱스
해당 인덱스를 사용함으로써 Outer 테이블인 TB_CUST 테이블의 스캔 건수만큼 
Inner 테이블인 TB_ORD 테이블 스캔 시 효율적인 인덱스 스캔으로 극적인 성능 향상이 이뤄짐.

 

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

5 : TB_CUST_IDX01 을 인덱스 범위 스캔 한다

4 : 5번에서 스캔한 ROWID를 기준으로 TB_CUST 테이블에 테이블 랜덤 액세스를 수행함

6 : TB_ORD_IDX02 를 인덱스 범위 스캔한다

3 : 4번과 6번에 대한 중첩 루프 조인 연산을 수행한다

7 : 6번에서 스캔한 ROWID를 기준으로 TB_ORD 테이블에 테이블 랜덤 액세스를 수행한다

2 : 3번과 6번에 대한 중첩 루프 조인 연산을 수행

1 : 조인 결과에 대해 조건에 따른 필터링 처리

0 : SELECT 절의 연산 수행