소트 #2 - 조인 정렬 조건

ORDER BY 절에 여러 테이블의 열을 기술하면 인덱스로 소트를 제거할 수 없다.

 

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;

CREATE TABLE t1 (C1 NUMBER, C2 NUMBER, C3 VARCHAR2(1));
CREATE TABLE t2 (C4 NUMBER, C5 NUMBER, C6 VARCHAR2(1));
CREATE INDEX t1_x1 ON t1 (C1, C2);
CREATE INDEX t2_x1 ON t2 (C4, C5);

INSERT INTO t1 VALUES (1, 1, 'X');
INSERT INTO t1 VALUES (2, 1, 'X');
INSERT INTO t1 VALUES (3, 2, 'X');
INSERT INTO t1 VALUES (4, 2, 'X');
INSERT INTO t2 VALUES (1, 3, 'X');
INSERT INTO t2 VALUES (1, 4, 'X');
INSERT INTO t2 VALUES (2, 1, 'X');
INSERT INTO t2 VALUES (2, 2, 'X');
COMMIT;

 

아래 쿼리는 ORDER BY 절에 여러 테이블의 열이 기술했기 때문에 소트가 발생했다.

-- 2
SELECT   /*+ ORDERED USE_NL(B) INDEX(B) */
         a.c1, a.c2, b.c4, b.c5, a.c3, b.c6
    FROM t1 a
       , t2 b
   WHERE a.c1 >= 1
     AND b.c4 = a.c2
ORDER BY a.c1
       , a.c2
       , b.c5;

C1 C2 C4 C5 C5 C6
-- -- -- -- -- --
 1  1  1  3 X  X
 1  1  1  4 X  X
 2  1  1  3 X  X
 2  1  1  4 X  X
 3  2  2  1 X  X
 3  2  2  2 X  X
 4  2  2  1 X  X
 4  2  2  2 X  X

8 행이 선택되었습니다.

--------------------------------------------------------
| Id  | Operation                              | Name  |
--------------------------------------------------------
|   0 | SELECT STATEMENT                       |       |
|   1 |  SORT ORDER BY                         |       |
|   2 |   NESTED LOOPS                         |       |
|   3 |    NESTED LOOPS                        |       |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1    |
|*  5 |      INDEX RANGE SCAN                  | T1_X1 |
|*  6 |     INDEX RANGE SCAN                   | T2_X1 |
|   7 |    TABLE ACCESS BY INDEX ROWID         | T2    |
--------------------------------------------------------

 

위 쿼리는 ORDER BY 절을 제거하더라도 정렬된 결과가 반환된다. t1_x1 인덱스가 c1 + c2, t2_x1 인덱스가 c4 + c5로 구성되어 있고, t1.c2 열와 t2.c4 열이 등가 조인되었기 때문에 t1.c1, t1.c2, t2.c5로 정렬된 값이 반환되는 것이다. 정렬 조건과 조인 조건, 인덱스 구성이 맞아 떨어지는 특별한 케이스다.

-- 3
SELECT /*+ ORDERED USE_NL(B) INDEX(B) NO_NLJ_BATCHING(B)
           NO_BATCH_TABLE_ACCESS_BY_ROWID(A)
           NO_BATCH_TABLE_ACCESS_BY_ROWID(B) */
       a.c1, a.c2, b.c4, b.c5, a.c3, b.c6
  FROM t1 a
     , t2 b
 WHERE a.c1 >= 1
   AND b.c4 = a.c2;

C1 C2 C4 C5 C5 C6
-- -- -- -- -- --
 1  1  1  3 X  X
 1  1  1  4 X  X
 2  1  1  3 X  X
 2  1  1  4 X  X
 3  2  2  1 X  X
 3  2  2  2 X  X
 4  2  2  1 X  X
 4  2  2  2 X  X

8 행이 선택되었습니다.

-----------------------------------------------
| Id  | Operation                     | Name  |
-----------------------------------------------
|   0 | SELECT STATEMENT              |       |
|   1 |  TABLE ACCESS BY INDEX ROWID  | T2    |
|   2 |   NESTED LOOPS                |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    |
|*  4 |     INDEX RANGE SCAN          | T1_X1 |
|*  5 |    INDEX RANGE SCAN           | T2_X1 |
-----------------------------------------------

오라클 데이터베이스는 ORDER BY 절에 의한 정렬만 보장한다. 바람직하지는 않지만 성능 개선을 위해 조인 조건과 정렬 순서에 따라 인덱스를 설계하고, ORDER BY 절을 생략해야 하는 경우도 종종 있다.