오라클 SQL 성능 개선하기: 스칼라 서브쿼리 vs 인라인 뷰

개요

---

최근 운영하고 있는 서비스에서 각 회원별 입출금 내역을 바탕으로 월단위 유형별 입출금 내역을 조회해야 할 일이 생겼다. 또한 해당 내역에 더해 매 월 초에 배치로 기록하는 회원별 충전금 보유량도 함께 보여주어야 했는데, 이를 위해 사용한 서브쿼리가 성능에 큰 영향을 주어 쿼리를 개선해야만 했다. 이번 포스트에서는 쿼리를 개선하는 과정에 대해 정리한다.

 

기존 쿼리 - 스칼라 서브쿼리

---

아래는 맨 처음 요구사항을 만족하기 위해 작성한 쿼리이다.

SELECT user_id,
       transaction_month,
       (SELECT balance
        FROM bill_stat bs
        WHERE bs.user_id = ba.user_id
          AND bs.month = ba.transaction_month) AS monthly_balance,
       SUM(CASE WHEN transaction_type = '출금' THEN amount ELSE 0 END) AS total_withdrawal,
       SUM(CASE WHEN transaction_type = '입금' THEN amount ELSE 0 END) AS total_deposit
FROM bill_account ba
GROUP BY user_id, transaction_month;

각 회원별 입출 내역을 조회함과 동시에 회원별로 통계 정보를 서브쿼리로 조회해 와서 한번에 보여주려는 의도로 작성되었다. 그런데 문제는 bill_account 테이블은 매 월 100만건 이상의 데이터가 쌓이는 테이블이었고 이로 인해 서브쿼리 수행 횟수가 너무 많아져 부하가 심하다는 DBA 의견을 받게 되었다.

 

개선 쿼리 - 인라인 뷰

---

 

문제는 매 회원별로 조회할 때마다 서브쿼리를 계속해서 수행한다는 점에 있었는데, 이를 개선하기 위해 인라인 뷰(조인) 방식을 활용하여, 맨 처음 조인할 때에 한 번에 조회하여 해당 결과값을 가지고 결과를 조회할 수 있도록 하였다. 즉, 매 회원별로 반복해서 호출되던 서브 쿼리를 제거하고, 조인된 결과로 별도 DB 추가 조회 없이 필요한 데이터를 조회할 수 있게 하여 데이터 양이 많아도 효율적으로 실행될 수 있도록 개선하였다.

SELECT ba.user_id,
       ba.transaction_month,
       bs.monthly_balance,
       SUM(CASE WHEN ba.transaction_type = '출금' THEN ba.amount ELSE 0 END) AS total_withdrawal,
       SUM(CASE WHEN ba.transaction_type = '입금' THEN ba.amount ELSE 0 END) AS total_deposit
FROM bill_account ba,
     (SELECT user_id, month, balance AS monthly_balance
      FROM bill_stat) bs
WHERE ba.user_id = bs.user_id(+)
AND ba.transaction_month = bs.month(+)
GROUP BY ba.user_id, ba.transaction_month, bs.monthly_balance;

 

결론

---

스칼라 서브쿼리를 사용하면 쿼리를 작성할 때에는 단순하고 쉽게 쿼리를 작성할 수 있지만, 데이터량에 따라 성능에 심각한 영향을 줄 수 있다. 따라서 수백만 건에 달하는 대량의 데이터를 대상으로 조회할 때에는 인라인 뷰를 활용하여 쿼리를 작성한다면 보다 쿼리를 최적화 할 수 있다. 이 외에 실행 계획을 추가로 확인하여 쿼리에 힌트를 추가하는 등의 방식을 사용한다면 보다 효율적인 쿼리를 작성할 수 있을것 같다.