효율적인 Nested Loop Join을 위한 부분 처리

Nested Loop Join은 데이터베이스에서 자주 사용되는 Join 알고리즘 중 하나로, 두 개의 테이블을 조인할 때 사용됩니다. Nested Loop Join은 외부 테이블의 각 행에 대해 내부 테이블을 전체 스캔하여 조건을 만족하는 결과를 찾아냅니다. 이때, 조인에 사용되는 테이블이 크고 데이터가 많은 경우 디스크 I/O가 많이 발생하여 성능 저하가 발생할 수 있습니다.

이러한 경우에 Nested Loop Join의 성능을 향상시키기 위해 부분처리 기법을 사용할 수 있습니다. 부분처리란, 전체 데이터를 한 번에 가져오는 것이 아니라 일부 데이터만 먼저 가져와서 처리하는 것을 말합니다. 이를 통해 불필요한 디스크 I/O를 줄이고, 성능을 개선할 수 있습니다. 아래는 예시 쿼리를 사용하여 효율적인 Nested Loop Join을 위한 부분처리에 대한 설명입니다.

 

예시 쿼리:

SELECT *
FROM (
    SELECT *
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.order_date >= '2023-01-01'
) WHERE rownum <= 20;

 

위 쿼리는 orders 테이블과 order_items 테이블을 Nested Loop Join으로 조인하면서, 주문 날짜(order_date)가 '2023-01-01' 이후인 데이터만을 검색하고 최종 결과로 20건 이하의 데이터를 반환합니다. 이때, 부분처리 기법을 적용하여 성능을 개선할 수 있습니다.

 

하지만, 위 쿼리에 ORDER BY 절이 들어간다면 전체 데이터를 정렬한 다음에 rownum <= 20 조건이 적용되므로 부분처리의 효과가 없을 수도 있습니다.

 

예시 쿼리:

SELECT *
FROM (
    SELECT /*+ NO_MERGE(o) */ *
    FROM (
        SELECT *
        FROM orders
        WHERE order_date >= '2023-01-01'
        ORDER BY order_date DESC
    ) o JOIN order_items oi ON o.order_id = oi.order_id
) WHERE rownum <= 20;

 

위 쿼리는 인라인 뷰에 NO_MERGE 힌트를 추가하여 두 번째 인라인 뷰에서 첫 번째 인라인 뷰와 병합되는 것을 방지합니다. 이로 인해 orders 테이블을 ORDER BY 절까지 수행한 다음에 order_items 테이블을 조인합니다.

ORDER BY 때문에 orders 테이블의 전체 조회는 피할 수 없지만 order_items 테이블과의 조인은 최대 20건으로 제한할 수 있습니다.

이렇게 하여, orders 테이블의 데이터를 전체적으로 조회하는 부분은 어쩔 수 없지만, order_items 테이블과의 조인을 최대 20건으로 제한하여 성능을 향상시킬 수 있습니다. 이는 부분처리와 관련하여 Nested Loop Join의 성능을 최적화하는 좋은 예시입니다.

 

CREATE INDEX idx_order_date ON orders (order_date);

SELECT *
FROM (
    SELECT /*+ NO_MERGE(o) INDEX_DESC(o.orders INDEX idx_order_date) */ *
    FROM (
        SELECT *
        FROM orders
        WHERE order_date >= '2023-01-01'
        ORDER BY order_date DESC
    ) o JOIN order_items oi ON o.order_id = oi.order_id
) WHERE rownum <= 20;

 

위의 SQL 쿼리에서 INDEX_DESC(o.orders INDEX idx_order_date) 힌트를 사용하여 order_date 컬럼에 이미 생성된 인덱스 idx_order_date를 역순으로 사용하도록 지정하였습니다. 이렇게 하면 order_date 컬럼을 내림차순으로 정렬한 인덱스를 활용하여 ORDER BY 절을 처리할 수 있게 됩니다.

 

orders 테이블에서 order_date 인덱스를 사용하여 '2023-01-01' 이후의 데이터만을 가져오고, rownum 조건을 통해 20건으로 제한하게 됩니다. 따라서 '2023-01-01' 이후의 데이터가 20건을 초과하더라도 인덱스를 사용하여 20건만 조회해오게 됩니다. 그리고 order_items 테이블과의 조인도 최대 20건으로 제한되므로 최종 결과로는 20건 이하의 데이터만 반환됩니다.

 

아래는 주어진 쿼리의 실행계획 예시입니다:

------------------------------------------------------------------------
| Id  | Operation                 | Name          | Rows  | Cost (%CPU)|
------------------------------------------------------------------------
|  0  | SELECT STATEMENT          |               |    20 |  1000 (10) |
|* 1  |  COUNT STOPKEY            |               |       |            |
|  2  |   VIEW                    |               |    20 |  1000 (10) |
|* 3  |    SORT ORDER BY STOPKEY  |               |    20 |  1000 (10) |
|  4  |     NESTED LOOPS          |               |    20 |  1000 (10) |
|  5  |      VIEW                 | VW_SQ_1       |    20 |  1000 (10) |
|* 6  |       COUNT STOPKEY       |               |       |            |
|  7  |        VIEW               |               |    20 |  1000 (10) |
|  8  |         SORT ORDER BY STOPKEY|            |    20 |  1000 (10) |
|  9  |          INDEX RANGE SCAN | IDX_ORDER_DATE|    20 |  1000 (10) |
| 10  |      INDEX UNIQUE SCAN    | PK_ORDER_ITEMS|     1 |     0 (0)  |
-----------------------------------------------------------------------

 

위의 실행계획에서 "Stopkey" 연산은 ID 1과 ID 6에서 확인할 수 있습니다. 이를 통해 부분적인 범위 내에서 검색을 멈추고 최대 20개의 행만 반환하는 것을 알 수 있습니다. "Rows"에는 20으로 표시되어 있으며, 이는 해당 단계에서 최대 20개의 행이 반환되는 것을 의미합니다.

 

 

목차