[Oracle] 오라클 고급 JOIN 테크닉 - 최종 출력 건에 대해서만 JOIN하기

최종 출력 건에 대해서만 JOIN하기

-- 전체 게시판 데이터 수백만 건

-- 특정 게시판 유형(게시판유형 = :TYPE)에 속하는 데이터는 평균 10만 건

-- 게시판_X01 : 게시판유형 + 등록일자 DESC + 번호

SELECT *

  FROM (

SELECT ROWNUM NO, 등록일자, 번호, 제목, 회원명, 게시판유형명, 질문유형명, COUNT(*) OVER () CNT

  FROM (

SELECT A.등록일자, A.번호, A.제목, B.회원명, C.게시판유형명, D.질문유형명

  FROM 게시판 A, 회원 B, 게시판유형 C, 질문유형 D

     WHERE A.게시판유형 = :TYPE

   AND B.회원번호 = A.작성자번호

   AND C.게시판유형 = A.게시판유형

   AND D.질문유형 = A.질문유형

     ORDER BY A.등록일자 DESC, A.질문유형, A.번호

)

   WHERE ROWNUM <= 31

)

WHERE NO BETWEEN 21 AND 30;

Execution Plan

--------------------------------------------------------------------------------------------

0         SELECT STATEMENT Optimizer=ALL_ROWS

1     0     VIEW

2     1         WINDOW (BUFFER)

3     2             COUNT (STOPKEY)

4     3                 VIEW

5     4                     SORT (ORDER BY STOPKEY)

6     5                        NESTED LOOPS

7     6                           NESTED LOOPS

8     7                               NESTED LOOPS

9     8                                   TABLE ACCESS (BY LOCAL INDEX ROWID) OF '게시판' (TABLE)

10    9                                        INDEX (RANGE SCAN) OF '게시판_X01' (INDEX (UNIQUE))

11   10                                   TABLE ACCESS (BY INDEX ROWID) OF '회원' (TABLE)

12   11                                       INDEX (UNIQUE SCAN) OF '회원_PK' (INDEX (UNIQUE))

13    7                                TABLE ACCESS (BY INDEX ROWID) OF '게시판유형' (TABLE)

14   13                                  INDEX (UNIQUE SCAN) OF '게시판유형_PK' (INDEX (UNIQUE))

15    6                             TABLE ACCESS (BY INDEX ROWID) OF '질문유형' (TABLE) 

16   15 INDEX (UNIQUE SCAN) OF '질문유형_PK' (INDEX (UNIQUE))

⑩ 게시판유형 = :TYPE에 속하는 10만 건의 데이터를 읽어 회원 → 게시판유형 → 질문유형 순으로 NL JOIN

⑤ JOIN된 데이터를 sort하는 과정에서  stopkey 작동

* 10만 건의 데이터를 인덱스에서 읽어 table access 하고 세 개의 테이블과 JOIN한 다음 31 건만 취하고 버려버리는 비효율 발생

▼

-- 게시판_X01 : 게시판유형 + 등록일자 DESC + 번호 + 질문유형

SELECT ROWID ID

  FROM 게시판

WHERE 게시판유형 = :TYPE

ORDER BY 등록일자 DESC, 질문유형, 번호

-- 인덱스에 질문유형 컬럼을 추가했으므로 위 쿼리는 인덱스 내에서 해결 가능

▼

SELECT /*+ordered use_nl(A) use_nl(B) use_nl(C) use_nl(D) rowid(A) */

A.등록일자, A.번호, A.제목, B.회원명, C.게시판유형명, D.질문유형명, X.CNT

   FROM ( 

SELECT RID, ROWNUM NO, COUNT(*) OVER () CNT

  FROM (

SELECT ROWID RID

  FROM 게시판

WHERE 게시판유형 = :TYPE

ORDER BY 등록일자 DESC, 질문유형, 번호

)

WHERE ROWNUM <= 31           -- 인덱스만 읽음     

) X, 게시판 A, 회원 B, 게시판유형 C, 질문유형 D

 WHERE X.NO BETWEEN 21 AND 30

     AND A.ROWID = X.RID

     AND B.회원번호 = A.작성자번호

     AND C.게시판유형 = A.게시판유형

     AND D.질문유형 = A.질문유형;

Execution Plan

---------------------------------------------------------------------

0       SELECT STATEMENT Optimizer=ALL_ROWS

1    0      NESTED LOOPS

2    1        NESTED LOOPS

3    2          NESTED LOOPS

4    3             NESTED LOOPS

5    4               VIEW

6    5                 COUNT (STOPKEY)

7    6                    VIEW

8    7                      SORT (ORDER BY STOPKEY)

9    8                         INDEX (RANGE SCAN) OF '게시판_X01' (INDEX (UNIQUE))

10   4              TABLE ACCESS (BY USER ROWID) OF '게시판' (TABLE)            

11   3            TABLE ACCESS (BY INDEX ROWID) OF '회원' (TABLE)

12   11              INDEX (UNIQUE SCAN) OF '회원_PK' (INDEX (UNIQUE))

13    2           TABLE ACCESS (BY INDEX ROWID) OF '게시판유형' (TABLE)

14   13              INDEX (UNIQUE SCAN) OF '게시판유형_PK' (INDEX (UNIQUE))

15    1         TABLE ACCESS (BY INDEX ROWID) OF '질문유형' (TABLE)

16   15          INDEX (UNIQUE SCAN) OF '질문유형_PK' (INDEX (UNIQUE))

* 인덱스 내에서 31 건의 데이터만 get한 다음 User RowID를 이용해 게시판 table access

→ 게시판 테이블을 두 번 읽었지만 인덱스를 경유해 한 번만 테이블을 access하는 것과 같은 일량