Nested Loop

오라클/Tuning

Nested Loop

빵순이^^ 2010. 5. 8. 16:06

1. 기본 사항

Nested Loop은 index의 유무와 대상 table의 크기에 따라 driving table이 결정된다.

Nested Loop에서 driving table의 선정은 아래와 같이 4가지 조건으로 분류할 수 있다.

select *
from emp e, dept d
  where e.deptno = d.deptno

index          X             X                --> driving 테이블 : 작은 테이블 (optimizer_stats로 판단)
index          X             O                --> driving 테이블 : index가 없는 쪽 (항상 index가 없는 쪽에서 먼저 실행된다.)
index          O             X                --> driving 테이블 : index가 있는 쪽 (항상 index가 없는 쪽에서 먼저 실행된다.)
index          O             O                --> driving 테이블 : 작은 테이블 (optimizer_stats로 판단)

2. 예제를 이용하여 기본사항을 확인

drop table dept1 purge;
drop table dmp1 purge;

create table dept1 as select * from dept;
create table emp1 as select * from emp;

alter table dept1 add constraint dept1_deptno_pk primary key(deptno);
alter table emp1 add constraint emp1_deptno_fk foreign key(deptno) references dept1(deptno);

2-1. driving table : 작은 테이블

양쪽 테이블 모두 index가 없을 경우 optimizer_stats로 판단한 작은 테이블이 driving table이 된다.

select /*+ use_nl(e d) */ *
from emp1 e, dept1 d
where e.deptno = d.deptno+0; -- +0을 붙이면 index를 타지않게 된다.

index X X --> driving 테이블 : 작은 테이블

----------------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |    19 | 2223 |     8   (0)| 00:00:01 |
|   1 | NESTED LOOPS      |       |    19 | 2223 |     8   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT1 |     4 |   120 |     3   (0)| 00:00:01 |
|* 3 |   TABLE ACCESS FULL| EMP1 |     5 |   435 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

index가 존재하지 않기 때문에 driving table인 DEPT1 부터 EMP1로 table full scan을 하게 된다.
실행계획의 순서는 2 - 3 이다.

2-2. driving table : index가 없는 쪽

한쪽의 테이블의 컬럼만 index가 존재할 경우에는 index가 없는 쪽에서 실행순서가 정해진다.
아래 예제의 경우에는 EMP1이 driving table이 된다.

select *
from emp1 e, dept1 d
where e.deptno = d.deptno;

index X O --> driving 테이블 : index가 없는 쪽

------------------------------------------------------------------------------------------------
| Id | Operation                    | Name            | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |    14 | 1638 |     4   (0)| 00:00:01 |
|   1 | NESTED LOOPS                |                 |    14 | 1638 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | EMP1            |    14 | 1218 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT1           |     1 |    30 |     1   (0)| 00:00:01 |
|* 4 |    INDEX UNIQUE SCAN         | DEPT1_DEPTNO_PK |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

driving table인 EMP1 table 부터 시작되어 DEPT1 테이블의 deptno에 index가 존재하기 때문에 DEPT1_DEPTNP_PK index를 거쳐 DEPT1 table로 접속하게 된다.
실행계획의 순서는 2 - 4 - 3 이다.

2-3. driving table : index가 없는 쪽

create index emp1_deptno_idx on emp1(deptno);

select *
from emp1 e, dept1 d
where e.deptno = d.deptno+0; -- +0을 붙이면 index를 타지 않게 된다.

index O X --> driving 테이블 : index가 없는 쪽

-----------------------------------------------------------------------------------------------
| Id | Operation                   | Name            | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |    19 | 2223 |     4   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| EMP1            |     5 |   435 |     1   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |                 |    19 | 2223 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL        | DEPT1           |     4 |   120 |     3   (0)| 00:00:01 |
|* 4 |    INDEX RANGE SCAN         | EMP1_DEPTNO_IDX |     5 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

이번 예제에서는 DEPT1 테이블의 deptno 컬럼에는 index가 존재하지 않는 것으로 가정되었기 때문에 DEPT1 테이블이 driving table이 된다.
실행계획의 순서는 3 - 4 - 1 이다.

2-4 : driving table : 작은 테이블

양쪽모두 index가 존재할 경우에는 양쪽 모두 index가 없는 경우와 마찬가지로 optimizer_stats로 판단하여 작은 테이블이 driving table이 된다.

select *
from emp1 e, dept1 d
where e.deptno = d.deptno;

index O O --> driving 테이블 : 작은 테이블

-----------------------------------------------------------------------------------------------
| Id | Operation                   | Name            | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |    14 | 1638 |     4   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| EMP1            |     4 |   348 |     1   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |                 |    14 | 1638 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL        | DEPT1           |     4 |   120 |     3   (0)| 00:00:01 |
|* 4 |    INDEX RANGE SCAN         | EMP1_DEPTNO_IDX |     5 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

DEPT1 테이블의 크가가 더 작기 때문에 driving table이 되었다.
실행계획의 순서는 3 - 4 - 1 이다.

그런데 위 실행계획에서 DEPT1의 deptno 컬럼에 index가 있음에도 불구하고 table full scan이 되었다.
이는 무엇때문에 그럴까?

3. 실행계획 읽기 연습

    select /*+ use_nl(e d) */ *
    from emp1 e, dept1 d
    where e.deptno = d.deptno;

-----------------------------------------------------------------------------------------------
| Id | Operation                   | Name            | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |    14 | 1638 |     4   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| EMP1            |     4 |   348 |     1   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |                 |    14 | 1638 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL        | DEPT1           |     4 |   120 |     3   (0)| 00:00:01 |
|* 4 |    INDEX RANGE SCAN         | EMP1_DEPTNO_IDX |     5 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

참고자료 :

oracle_data_join_method.pdf

저작자표시