# 아우터 조인 > [개발자를 위한 오라클 SQL 튜닝](https://www.hanbit.co.kr/store/books/look.php?p_code=E9267570814) 내용에서 참고한 내용입니다. ## Intro ## 아우터 조인 > **아우터 조인** * 테이블 A와 B가 있을 때 A 테이블을 기준으로 B 테이블이 조인에 성공하면 B 테이블의 데이터를 보여주고, 조인에 실패하면 B 테이블의 데이터를 보여주지 않는 조인 방식이다. > **아우터 조인의 2가지 방식** * **Left Outer Join** * 왼쪽에 명시한 테이블을 기준이 되는 Outer 조인 방식 * 오른쪽에 명시한 테이블은 조인에 성공한 데이터만 보여주는 방식 * **Right Outer Join** * 오른쪽에 명시한 테이블이 기준이 되는 Outer 조인 방식 * 왼쪽에 명시한 테이블은 조인에 성공한 데이터만 보여주는 방식 ## 아우터 조인 튜닝 * Outer 조인으로 구현된 SQL문의 성능을 극대화하는 일련의 모든 활동을 의미한다. > **아우터 조인을 이용하여 테이블 스캔 최소화** * 테이블 A, B, C이 있다고 가정 * A = B + C의 관계가 성립하는 경우 테이블 B와 C는 테이블 A에 대하여 배타적 관계에 있다고 할 수 있다. * 테이블 A를 기준으로 테이블 B와 C를 각각 조인할 때 UNION ALL문을 사용하여 두 개의 SELECT 문의 합집합을 구하게 된다. * 이러한 경우 UNION ALL 문을 아우터 조인으로 변환하여 성능을 개선할 수 있다. \ (가장 큰 테이블인 A를 단 한번만 스캔하는 것이 핵심) > **아우터 조인을 스칼라 서브쿼리로 변환** * 아우터 조인으로 구현된 SQL 문은 스칼라 서브쿼리로 변환할 수 있다. * 오라클에서는 스칼라 서브쿼리로 한 번 이상 호출된 Input/Ouput 값을 멀티 버퍼에 저장해 둔 후 동일한 Input으로 호출되면 기존에 가지고 있던 Output 값을 바로 리턴하는 스칼라 서브쿼리 캐싱 기능이 있다. * 이러한 오라클의 기능을 잘 활용하여 아우터 조인을 스칼라 서브쿼리로 변환하면 성능을 극대화할 수 있다. ## 아우터 조인을 위한 준비 * 테이블 생성 ``` CREATE TABLE TB_JOB_ORDER ( JOB_NO VARCHAR2(10), VISIT_PRE_DT VARCHAR2(8), WORKER_ID VARCHAR2(10), JOB_STATUS_CD VARCHAR2(2), JOB_GUBUN VARCHAR2(1), REQ_NO VARCHAR2(10), INST_DTM DATE, INST_ID VARCHAR2(50) ); CREATE TABLE TB_OPEN_REQ ( OPEN_REQ_NO VARCHAR2(10), OPEN_REQ_DT VARCHAR2(8), OPEN_HOPE_DT VARCHAR2(8), CUST_NO VARCHAR2(10), INST_DTM DATE, INST_ID VARCHAR2(50) ); CREATE TABLE TB_DISABLE_REQ ( DISABLE_REQ_NO VARCHAR2(10), DISABLE_REQ_DT VARCHAR2(8), DISABLE_DTM DATE, CUST_NO VARCHAR2(10), INST_DTM DATE, INST_ID VARCHAR2(50) ); ``` * TB\_OPEN\_REQ 더미 데이터 입력 ``` INSERT INTO TB_OPEN_REQ SELECT LPAD(TO_CHAR(ROWNUM), 10, '0'), RANDOM_DT, TO_CHAR(TO_DATE(RANDOM_DT, 'YYYYMMDD') + DBMS_RANDOM.VALUE(1, 30), 'YYYYMMDD'), LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1, 10000))), 10, '0'), SYSDATE, 'DBMSEXPERT' FROM ( SELECT TO_CHAR(SYSDATE - DBMS_RANDOM.VALUE(1, 3650), 'YYYYMMDD') RANDOM_DT FROM DUAL ) A, DUAL CONNECT BY LEVEL <= 1000000; COMMIT; ``` * TB\_DISABLE\_REQ 테이블 더미 데이터 입력 ``` INSERT INTO TB_DISABLE_REQ SELECT LPAD(TO_CHAR(ROWNUM), 10, '0'), RANDOM_DT, TO_DATE(RANDOM_DT || TO_CHAR(SYSDATE - 864000 / 24 / 60 / 60, 'HH24MISS'), 'YYYYMMDDHH24MISS'), LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1, 10000))), 10, '0'), SYSDATE, 'DBMSEXPERT' FROM ( SELECT TO_CHAR(SYSDATE - DBMS_RANDOM.VALUE(1, 3650), 'YYYYMMDD') RANDOM_DT FROM DUAL ) A, DUAL CONNECT BY LEVEL <= 1000000; COMMIT; ``` * TB\_JOB\_ORDER 테이블 더미 데이터 입력 ``` INSERT /*+ APPEND */ INTO TB_JOB_ORDER SELECT LPAD(TO_CHAR(ROWNUM), 10, '0'), TO_CHAR(SYSDATE - DBMS_RANDOM.VALUE(1, 3650), 'YYYYMMDD'), LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1, 10000))), 10, '0'), LPAD(TO_CHAR(TRUNC(MOD(DBMS_RANDOM.VALUE(1, 10000), 10))), 2, '0'), JOB_GUBUN, REQ_NO, SYSDATE, 'DBMSEXPERT' FROM DUAL, (SELECT '1' JOB_GUBUN, OPEN_REQ_NO REQ_NO FROM TB_OPEN_REQ UNION ALL SELECT '2' JOB_GUBUN, DISABLE_REQ_NO REQ_NO FROM TB_DISABLE_REQ ) A; COMMIT; ``` * 테이블 제약 조건 추가 ``` ALTER TABLE TB_JOB_ORDER ADD CONSTRAINT TB_JOB_ORDER_PK PRIMARY KEY (JOB_NO); ALTER TABLE TB_OPEN_REQ ADD CONSTRAINT TB_OPEN_REQ_PK PRIMARY KEY (OPEN_REQ_NO); ALTER TABLE TB_DISABLE_REQ ADD CONSTRAINT TB_DISABLE_REQ_PK PRIMARY KEY (DISABLE_REQ_NO); ``` * 통계 정보 생성 ``` ANALYZE TABLE TB_JOB_ORDER COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254; ANALYZE TABLE TB_OPEN_REQ COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254; ANALYZE TABLE TB_DISABLE_REQ COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254; ``` ### 아우터 조인으로 테이블 스캔을 최소화하여 성능 개선하기 > **튜닝 전** ``` SELECT A.JOB_NO, A.WORKER_ID, A.JOB_STATUS_CD, A.REQ_NO FROM TB_JOB_ORDER A, TB_OPEN_REQ B WHERE A.VISIT_PRE_DT = '20151102' AND A.JOB_GUBUN = '1' AND A.REQ_NO = B.OPEN_REQ_NO UNION ALL SELECT A.JOB_NO, A.WORKER_ID, A.JOB_STATUS_CD, A.REQ_NO FROM TB_JOB_ORDER A, TB_DISABLE_REQ B WHERE A.VISIT_PRE_DT = '20151102' AND A.JOB_GUBUN = '2' AND A.REQ_NO = B.DISABLE_REQ_NO; -- [2021-07-29 17:39:05] 239 rows retrieved starting from 1 in 173 ms (execution: 30 ms, fetching: 143 ms) ``` * **SQL 분석** * TB\_JOB\_ORDER 테이블과 TB\_OPEN\_REQ 테이블을 조인 * TB\_JOB\_ORDER 테이블과 TB\_DISABLE\_REQ 테이블을 조인 * 각각의 SELECT 문을 UNION ALL 하여 결과 집합을 도출 * **SQL 문제점** * 가장 큰 용량의 테이블인 TB\_JOB\_ORDER 테이블을 두 번이나 스캔하는 문제 * 해당 테이블을 단 한 번만 스캔하여 결과 집합을 도출해야함 * **실행 계획** * TB\_JOB\_ORDER 테이블을 풀 스캔(TABLE ACCESS FULL) * TB\_OPEN\_REQ\_PK 인덱스를 인덱스 유일 스캔(INDEX UNIQUE SCAN) * 위 동작을 중첩 루프 조인(NESTED LOOPS) * TB\_JOB\_ORDER 테이블을 풀 스캔(TABLE ACCESS FULL) * TB\_DISABLE\_REQ\_PK 인덱스를 인덱스 유일 스캔(INDEX UNIQUE SCAN) * 바로 위 작업을 중첩 루프 조인(NESTED LOOPS) * UNION ALL * SELECT문을 종료 ![아우터 조인 테이블 스캔 최소화 튜닝 전](/files/-Mflbj4G6uZyd0xBzY68) > **튜닝 후** ``` SELECT /*+ LEADING(A) USE_NL(B) USE_NL(C) */ A.JOB_NO, A.WORKER_ID, A.JOB_STATUS_CD, A.REQ_NO FROM TB_JOB_ORDER A, TB_OPEN_REQ B, TB_DISABLE_REQ C WHERE A.VISIT_PRE_DT = '20151102' AND DECODE(A.JOB_GUBUN, '1', A.REQ_NO) = B.OPEN_REQ_NO(+) AND DECODE(A.JOB_GUBUN, '2', A.REQ_NO) = C.DISABLE_REQ_NO(+); -- [2021-07-29 17:39:24] 239 rows retrieved starting from 1 in 122 ms (execution: 49 ms, fetching: 73 ms) ``` * **SQL 분석** * LEADING 힌트를 사용하여 TB\_JOB\_ORDER 테이블을 Outer테이블로 지정, USE\_NL 힌트를 이용하여 TB\_OPEN\_REQ 테이블을 Inner 테이블로 지정 * TB\_ORD\_ORDER 테이블과 TB\_OPEN\_REQ 테이블의 중첩 루프 조인 결과가 나오면 해당 결과를 Outer 테이블로 하고, TB\_DISABLE\_REQ 테이블을 Inner 테이블로 하여 중첩 루프 조인 * TB\_ORD\_ORDER 테이블의 JOB\_GUBUN의 값이 '1'인 경우 TB\_OPEN\_REQ 테이블과 LEFT OUTER JOIN * TB\_ORD\_ORDER 테이블의 JOB\_GUBUN의 값이 '2'인 경우 TB\_DISABLE\_REQ 테이블과 LEFT OUTER JOIN * **실행 계획** * TB\_JOB\_ORDER 테이블을 풀 스캔(TABLE ACCESS FULL) * TB\_OPEN\_REQ\_PK 인덱스를 인덱스 유일 스캔(INDEX UNIQUE SCAN) * 위 두 작업을 중첩 루프 아우터 조인(NESTED LOOPS OUTER) * TB\_DISABLE\_REQ\_PK 인덱스를 인덱스 유일 스캔(INDEX UNIQUE SCAN) * 바로 위 두 작업을 중첩 루프 아우터 조인(NESTED LOOPS OUTER) * SELECT 절의 연산 수행 ![아우터 조인으로 테이블 스캔을 최소화하여 성능 개선](/files/-Mflbj4IdpEP4dD_Eftu) ### 아우터 조인을 스칼라 서브쿼리 방식으로 변환하여 성능 극대화하기 * **테이블 설계** ``` CREATE TABLE TB_TRD_DAY ( TRD_DT VARCHAR2(8), --거래일자 INSU_CD VARCHAR2(4), --인수코드 INSU_DETAIL_CD VARCHAR2(6), --인수상세코드 TRD_CNT NUMBER, --거래건수 CNCL_CNT NUMBER, --취소건수 EXPORTER_NO VARCHAR2(10) --수출자번호 ); CREATE TABLE TB_EXPORTER ( EXPORTER_NO VARCHAR2(10), --수출자번호 EXPORTER_NM VARCHAR2(50) --수출자명 ); ``` * **데이터 입력** ``` INSERT INTO TB_EXPORTER SELECT LPAD(TO_CHAR(ROWNUM), 10, '0'), DBMS_RANDOM.STRING('U', 50) FROM DUAL CONNECT BY LEVEL <= 100000; COMMIT; ALTER TABLE TB_TRD_DAY NOLOGGING; INSERT /*+ APPEND */ INTO TB_TRD_DAY A -- APPEND 힌트 사용 SELECT TO_CHAR(SYSDATE - TRUNC(DBMS_RANDOM.VALUE(0, 3650)), 'YYYYMMDD'), LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1, 9999))), 4, '0'), LPAD(TO_CHAR(ROWNUM), 6, '0'), TRUNC(DBMS_RANDOM.VALUE(1, 999)), TRUNC(DBMS_RANDOM.VALUE(1, 999)), B.EXPORTER_NO FROM TB_EXPORTER B, (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 10); COMMIT; ``` * **테이블 제약 조건 추가** ``` ALTER TABLE TB_TRD_DAY ADD CONSTRAINT TB_TRD_DAY_PK PRIMARY KEY (TRD_DT, INSU_CD, INSU_DETAIL_CD); ALTER TABLE TB_EXPORTER ADD CONSTRAINT TB_EXPORTER_PK PRIMARY KEY (EXPORTER_NO); ``` * **통계 정보 생성** ``` ANALYZE TABLE TB_EXPORTER COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254; ANALYZE TABLE TB_TRD_DAY COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254; ``` > **튜닝 전** ``` SELECT /*+ LEADING(A) USE_NL(B) INDEX(A TB_TRD_DAY_PK) */ A.EXPORTER_NO, B.EXPORTER_NM, SUM(A.TRD_CNT - A.CNCL_CNT) TOT_CNT FROM TB_TRD_DAY A LEFT OUTER JOIN TB_EXPORTER B ON (A.EXPORTER_NO = B.EXPORTER_NO) AND A.TRD_DT BETWEEN '20100101' AND '20151231' GROUP BY A.EXPORTER_NO, B.EXPORTER_NM ORDER BY A.EXPORTER_NO; -- [2021-07-29 18:18:15] 199,678 rows retrieved starting from 1 in 12 s 522 ms (execution: 7 s 29 ms, fetching: 5 s 493 ms) ``` * **SQL 분석** * LEADING 힌트를 이용하여 TB\_TRD\_DAY 테이블 Outer 테이블로 지정, USE\_NL 힌트를 이용하여 TB\_EXPORTER 테이블을 Inner 테이블로 지정하고 중첩 루프 조인 처리가 되도록 유도 * INDEX 힌트를 이용하여 TB\_DRD\_DAY\_PK 인덱스를 사용 * TB\_TRD\_DAY 테이블을 기준으로 LEFT 아우터 조인 처리 * TB\_EXPORTER 테이블은 Outer 테이블로 설정 * **SQL 문제점** * Outer 테이블인 TB\_EXPORTER 테이블 내에 EXPORTER\_NM 컬럼만 SELECT절에 있다. * 이런 경우 TB\_EXPORTER 테이블을 반드시 아우터 조인할 필요가 없으므로 스칼라 서브쿼리로 변환하면 성능 향상을 기대할 수 있다. * **실행 계획** * TB\_TRD\_DAY\_PK 인덱스를 인덱스 풀 스캔(TABLE ACCESS FULL) * 위 작업을 통해 나온 ROWID를 이용하여 TB\_TRD\_DAY 테이블을 테이블 랜덤 액세스(TABLE ACCESS BY INDEX ROWID) * TB\_EXPORTER\_PK 인덱스를 인덱스 유일 스캔(INDEX UNIQUE SCAN) * 위 작업의 ROWID를 이용하여 TB\_EXPORTER 테이블을 테이블 랜덤 액세스(TABLE ACCESS BY INDEX ROWID) * 테이블 랜덤 액세스한 두 작업을 중첩 루프 아우터 조인(NESTED LOOPS OUTER) * SORT와 GROUP BY 연산을 수행 * SELECT 연산을 수행 ![스칼라 서브쿼리 방식으로 변환 전 쿼리](/files/-Mflbj4Jt6WJWyvwygYk) > **튜닝 후** ``` SELECT X.EXPORTER_NO, (SELECT B.EXPORTER_NM FROM TB_EXPORTER B WHERE B.EXPORTER_NO = X.EXPORTER_NO), TOT_CNT FROM (SELECT A.EXPORTER_NO, SUM(A.TRD_CNT - A.CNCL_CNT) TOT_CNT FROM TB_TRD_DAY A WHERE A.TRD_DT BETWEEN '20100101' AND '20151231' GROUP BY A.EXPORTER_NO ORDER BY A.EXPORTER_NO ) X; -- [2021-07-29 18:17:43] 99,704 rows retrieved starting from 1 in 4 s 313 ms (execution: 392 ms, fetching: 3 s 921 ms) ``` * **SQL 분석** * TB\_TRD\_DAY 테이블을 인라인 뷰를 이용하여 원하는 데이터를 조회 * 스칼라 서브쿼리를 이용하여 TB\_EXPORTER 테이블을 스캔하여 EXPORTER\_NM을 조회, 스칼라 서브쿼리의 캐싱 효과로 인하여 성능 향상 * **실행 계획** * TB\_TRD\_DAY 테이블을 테이블 풀 스캔(옵티마이저의 판단) * SORT와 GROUP BY 연산을 수행 * 해당 결과를 인라인 뷰(View)로 생성 * TB\_EXPORTER\_PK 인덱스를 인덱스 유일 스캔(INDEX UNIQUE SCAN) * EXPORTER\_NM 컬럼의 값을 가져오기 위해 테이블 랜덤 액세스(TABLE ACCESS BY INDEX ROWID) 수행 * SELECT 연산 수행 ![스칼라 서브쿼리 방식으로 변환하여 튜닝](/files/-Mflbj4Kf1tiJ4t42BGZ) --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://seokrae.gitbook.io/sr/book/tune/_11.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.