Notice
Recent Posts
Recent Comments
| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 6 | |
| 7 | 8 | 9 | 10 | 11 | 12 | 13 |
| 14 | 15 | 16 | 17 | 18 | 19 | 20 |
| 21 | 22 | 23 | 24 | 25 | 26 | 27 |
| 28 | 29 | 30 |
Tags
- 파블애드
- Windows 7
- jQuery
- VMware
- Ajax
- VMwareTools
- USB 레지스트리
- php
- PDA
- 이클립스 설정
- IE 8
- 오좀니아
- 한메일 pop
- IE8
- 이클립스
- HP 신제품
- XP 설치
- 이클립스 플러그인
- ddos
- 삼성 메모리
- DDos 전용 백신
- Live Mesh
- Internet Explorer 8
- 오즈 옴니아
- HP
- 파워블로그
- 한메일 smtp
- 한메일
- 옴니아2
- 윈도우 7
Archives
- Today
- Total
엉망진창
조인 순서의 중요성 본문
04 조인 순서의 중요성
- 조인 순서에 따라 쿼리 수행 성능이 달라질 수 있다.
- 이 장에서는 NL조인에 있어서 어떤 테이블을 드라이빙하는것이 유리한지를 예제를 통해 확인한다.
예시자료
- 10만 고객 테이블 생성
CREATE TABLE 고객 AS SELECT LEVEL 고객번호 , MOD(LEVEL, 3) + 1 납입방법코드 , DECODE(MOD(LEVEL, 10) , 1, '서울', 2, '대전', 3, '대구', 4, '부산', 5, '찍고' , 6, '아하', 7, '경기', 8, '충청', 9, '전라', 0, '경상' ) 거주지역 FROM dual CONNECT BY LEVEL <= 100000 ; ALTER TABLE 고객 ADD CONSTRAINT pk_고객 PRIMARY KEY(고객번호); CREATE INDEX idx_1 ON 고객(거주지역); CREATE INDEX idx_2 ON 고객(납입방법코드); EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, '고객', CASCADE => TRUE); EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, '납입방법', CASCADE => TRUE);
- 납입방법 코드테이블 생성
CREATE TABLE 납입방법 AS SELECT 1 납입방법코드, '신용카드' 납입방법 FROM dual UNION ALL SELECT 2, '자동이체' FROM dual UNION ALL SELECT 3, '지로' FROM dual ; ALTER TABLE 납입방법 ADD CONSTRAINT pk_납입방법 PRIMARY KEY(납입방법코드) ;
- 조인
SET AUTOT TRACE; SET LINESIZE 200; SELECT a.납입방법명, b.* FROM 납입방법 a, 고객 b WHERE b.납입방법코드 = a.납입방법코드 ; 100000 개의 행이 선택되었습니다. Execution Plan ---------------------------------------------------------- Plan hash value: 734007503 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 99491 | 2720K| 89 (7)| 00:00:02 | |* 1 | HASH JOIN | | 99491 | 2720K| 89 (7)| 00:00:02 | | 2 | TABLE ACCESS FULL| 납입 | 3 | 42 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| 고객 | 99491 | 1360K| 83 (4)| 00:00:02 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("B"."납입방법코드"="A"."납입방법코드") Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 6931 consistent gets 0 physical reads 0 redo size 3556195 bytes sent via SQL*Net to client 73710 bytes received via SQL*Net from client 6668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 100000 rows processed
필터조건이 없을 때
- 고객테이블 드라이빙
SQL> SELECT /*+ LEADING(b) USE_NL(a) */ 2 a.납입방법명, b.* 3 FROM 납입방법 a, 고객 b 4 WHERE b.납입방법코드 = a.납입방법코드 5 ; 100000 개의 행이 선택되었습니다. Execution Plan ---------------------------------------------------------- Plan hash value: 1676947890 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 99491 | 2720K| 223 (64)| 00:00:03 | | 1 | NESTED LOOPS | | 99491 | 2720K| 223 (64)| 00:00:03 | | 2 | TABLE ACCESS FULL | 고객 | 99491 | 1360K| 83 (4)| 00:00:02 | | 3 | TABLE ACCESS BY INDEX ROWID| 납입방법| 1 | 14 | 1 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | PK_납입 | 1 | | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("B"."납입방법코드"="A"."납입방법코드") Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 113596 consistent gets 1 physical reads 0 redo size 3556195 bytes sent via SQL*Net to client 73710 bytes received via SQL*Net from client 6668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 100000 rows processed
- 납입방법 테이블 드라이빙
SQL> SELECT /*+ LEADING(a) USE_NL(b) */ 2 a.납입방법명, b.* 3 FROM 납입방법 a, 고객 b 4 WHERE b.납입방법코드 = a.납입방법코드 5 ; 100000 개의 행이 선택되었습니다. Execution Plan ---------------------------------------------------------- Plan hash value: 920073752 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 99491 | 2720K| 250 (4)| 00:00:04 | | 1 | NESTED LOOPS | | 99491 | 2720K| 250 (4)| 00:00:04 | | 2 | TABLE ACCESS FULL| 납입 | 3 | 42 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| 고객 | 33164 | 453K| 82 (4)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("B"."납입방법코드"="A"."납입방법코드") Statistics ---------------------------------------------------------- 213 recursive calls 0 db block gets 7537 consistent gets 0 physical reads 0 redo size 2536173 bytes sent via SQL*Net to client 73710 bytes received via SQL*Net from client 6668 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 100000 rows processed
- 납입방법 테이블 드라이빙 - Index 힌트
SQL> SELECT /*+ LEADING(a) USE_NL(b) INDEX(b idx_2) */ 2 a.납입방법명, b.* 3 FROM 납입방법 a, 고객 b 4 WHERE b.납입방법코드 = a.납입방법코드 5 ; 100000 개의 행이 선택되었습니다. Execution Plan ---------------------------------------------------------- Plan hash value: 1301791307 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 99491 | 2720K| 1034 (1)| 00:00:13 | | 1 | TABLE ACCESS BY INDEX ROWID| 고객 | 33164 | 453K| 344 (1)| 00:00:05 | | 2 | NESTED LOOPS | | 99491 | 2720K| 1034 (1)| 00:00:13 | | 3 | TABLE ACCESS FULL | 납입방| 3 | 42 | 3 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | IDX_2 | 33333 | | 66 (2)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("B"."납입방법코드"="A"."납입방법코드") Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 14313 consistent gets 196 physical reads 0 redo size 2536173 bytes sent via SQL*Net to client 73710 bytes received via SQL*Net from client 6668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 100000 rows processed
 |
정리 1. 고객 드라이빙 (고객테이블 =10만=> 납입방법코드IDX =10만=> 납입방법테이블, 총 20만번 엑세스)
|
필터조건이 있을 때
- 필터조건 : 거주지역 = '부산' (전체 자료중 10% 차지)
- 고객테이블 드라이빙
SQL> SELECT /*+ LEADING(b) USE_NL(a) */ 2 a.납입방법명, b.* 3 FROM 납입방법 a, 고객 b 4 WHERE b.납입방법코드 = a.납입방법코드 5 AND b.거주지역 = '부산' 6 ; 10000 개의 행이 선택되었습니다. Execution Plan ---------------------------------------------------------- Plan hash value: 1676947890 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9949 | 272K| 99 (19)| 00:00:02 | | 1 | NESTED LOOPS | | 9949 | 272K| 99 (19)| 00:00:02 | |* 2 | TABLE ACCESS FULL | 고객 | 9949 | 136K| 84 (5)| 00:00:02 | | 3 | TABLE ACCESS BY INDEX ROWID| 납입방법| 1 | 14 | 1 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | PK_납입 | 1 | | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("B"."거주지역"='부산') 4 - access("B"."납입방법코드"="A"."납입방법코드") Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 11613 consistent gets 0 physical reads 0 redo size 324238 bytes sent via SQL*Net to client 7710 bytes received via SQL*Net from client 668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10000 rows processed
- 고객테이블 드라이빙 - Index 힌트
SQL> SELECT /*+ LEADING(b) USE_NL(a) INDEX(b idx_1) */ 2 a.납입방법명, b.* 3 FROM 납입방법 a, 고객 b 4 WHERE b.납입방법코드 = a.납입방법코드 5 AND b.거주지역 = '부산' 6 ; 10000 개의 행이 선택되었습니다. Execution Plan ---------------------------------------------------------- Plan hash value: 1945849362 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9949 | 272K| 318 (5)| 00:00:04 | | 1 | NESTED LOOPS | | 9949 | 272K| 318 (5)| 00:00:04 | | 2 | TABLE ACCESS BY INDEX ROWID| 고객 | 9949 | 136K| 303 (1)| 00:00:04 | |* 3 | INDEX RANGE SCAN | IDX_1 | 10000 | | 26 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| 납입방법| 1 | 14 | 1 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | PK_납입 | 1 | | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("B"."거주지역"='부산') 5 - access("B"."납입방법코드"="A"."납입방법코드") Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 12288 consistent gets 32 physical reads 0 redo size 324238 bytes sent via SQL*Net to client 7710 bytes received via SQL*Net from client 668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10000 rows processed
- 납입방법 테이블 드라이빙
SQL> SELECT /*+ LEADING(a) USE_NL(b) */ 2 a.납입방법명, b.* 3 FROM 납입방법 a, 고객 b 4 WHERE b.납입방법코드 = a.납입방법코드 5 AND b.거주지역 = '부산' 6 ; 10000 개의 행이 선택되었습니다. Execution Plan ---------------------------------------------------------- Plan hash value: 920073752 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9949 | 272K| 253 (6)| 00:00:04 | | 1 | NESTED LOOPS | | 9949 | 272K| 253 (6)| 00:00:04 | | 2 | TABLE ACCESS FULL| 납입 | 3 | 42 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| 고객 | 3316 | 46424 | 83 (5)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("B"."거주지역"='부산' AND "B"."납입방법코드"="A"."납입방법코드") Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 1510 consistent gets 0 physical reads 0 redo size 184278 bytes sent via SQL*Net to client 7710 bytes received via SQL*Net from client 668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10000 rows processed
- 납입방법 테이블 드라이빙 - Index 힌트
SQL> SELECT /*+ LEADING(a) USE_NL(b) INDEX(b idx_2) */ 2 a.납입방법명, b.* 3 FROM 납입방법 a, 고객 b 4 WHERE b.납입방법코드 = a.납입방법코드 5 AND b.거주지역 = '부산' 6 ; 10000 개의 행이 선택되었습니다. Execution Plan ---------------------------------------------------------- Plan hash value: 1301791307 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9949 | 272K| 1035 (1)| 00:00:13 | |* 1 | TABLE ACCESS BY INDEX ROWID| 고객 | 3316 | 46424 | 344 (1)| 00:00:05 | | 2 | NESTED LOOPS | | 9949 | 272K| 1035 (1)| 00:00:13 | | 3 | TABLE ACCESS FULL | 납입방| 3 | 42 | 3 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | IDX_2 | 33333 | | 66 (2)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("B"."거주지역"='부산') 4 - access("B"."납입방법코드"="A"."납입방법코드") Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 2361 consistent gets 0 physical reads 0 redo size 184278 bytes sent via SQL*Net to client 7710 bytes received via SQL*Net from client 668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10000 rows processed
- 납입방법 테이블 드라이빙 - Index 추가(납입방법 + 거주지역)
SQL> CREATE INDEX idx_3 ON 고객(납입방법코드, 거주지역); 인덱스가 생성되었습니다. SQL> SELECT /*+ LEADING(a) USE_NL(b) */ 2 a.납입방법명, b.* 3 FROM 납입방법 a, 고객 b 4 WHERE b.납입방법코드 = a.납입방법코드 5 AND b.거주지역 = '부산' 6 ; 10000 개의 행이 선택되었습니다. Execution Plan ---------------------------------------------------------- Plan hash value: 920073752 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9949 | 272K| 253 (6)| 00:00:04 | | 1 | NESTED LOOPS | | 9949 | 272K| 253 (6)| 00:00:04 | | 2 | TABLE ACCESS FULL| 납입 | 3 | 42 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| 고객 | 3316 | 46424 | 83 (5)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("B"."거주지역"='부산' AND "B"."납입방법코드"="A"."납입방법코드") Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 1510 consistent gets 0 physical reads 0 redo size 184278 bytes sent via SQL*Net to client 7710 bytes received via SQL*Net from client 668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10000 rows processed SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, '고객', CASCADE => TRUE); PL/SQL 처리가 정상적으로 완료되었습니다. SQL> SELECT /*+ LEADING(a) USE_NL(b) */ 2 a.납입방법명, b.* 3 FROM 납입방법 a, 고객 b 4 WHERE b.납입방법코드 = a.납입방법코드 5 AND b.거주지역 = '부산' 6 ; 10000 개의 행이 선택되었습니다. Execution Plan ---------------------------------------------------------- Plan hash value: 920073752 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10491 | 286K| 253 (6)| 00:00:04 | | 1 | NESTED LOOPS | | 10491 | 286K| 253 (6)| 00:00:04 | | 2 | TABLE ACCESS FULL| 납입 | 3 | 42 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| 고객 | 3497 | 48958 | 83 (5)| 00:00:02 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("B"."거주지역"='부산' AND "B"."납입방법코드"="A"."납입방법코드") Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1510 consistent gets 0 physical reads 0 redo size 184278 bytes sent via SQL*Net to client 7710 bytes received via SQL*Net from client 668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10000 rows processed SQL> SELECT /*+ LEADING(a) USE_NL(b) INDEX(b idx_3) */ 2 a.납입방법명, b.* 3 FROM 납입방법 a, 고객 b 4 WHERE b.납입방법코드 = a.납입방법코드 5 AND b.거주지역 = '부산' 6 ; 10000 개의 행이 선택되었습니다. Execution Plan ---------------------------------------------------------- Plan hash value: 2190748210 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10491 | 286K| 902 (1)| 00:00:11 | | 1 | TABLE ACCESS BY INDEX ROWID| 고객 | 3497 | 48958 | 300 (1)| 00:00:04 | | 2 | NESTED LOOPS | | 10491 | 286K| 902 (1)| 00:00:11 | | 3 | TABLE ACCESS FULL | 납입방| 3 | 42 | 3 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | IDX_3 | 3497 | | 9 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("B"."납입방법코드"="A"."납입방법코드" AND "B"."거주지역"='부산') Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 2145 consistent gets 0 physical reads 0 redo size 184278 bytes sent via SQL*Net to client 7710 bytes received via SQL*Net from client 668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10000 rows processed
|
정리 1. 고객 드라이빙 (거주지역IDX =1만=> 고객테이블 =1만=> 납입방법코드IDX =1만=> 납입방법테이블, 총 3만번 엑세스)
|
소트머지조인
- 소트머지 조인의 성능은 NL조인과 달리 렌덤엑세스 부하보다는 소트부하에 의해 발생한다.
- 디스크 소트가 발생할정도로 큰 테이블이 포함된 경우 큰 테이블 드라이빙이 유리
- 메모리 소트만으로 해결괴는 경우 작은 테이블 드라이빙이 유리
해시조인
- Hash Area에 Build Input을 모두 채울 수 있느냐가 관건
- 작은 테이블 드라이빙이 유리
 |
3개 이상 테이블 조인시 유의사항
|