[SQL] 쿼리 변환에 대해서 알아보자 (Oracle/PostgreSQL/SQL Server/MySQL)

Overview

 

쿼리 최적화는 성능에 직접적인 영향을 미치는 요소이기 때문에 DBMS는 성능 최적화를 위해 다양한 기술을 적용하고 있다. 오늘은 더 똑똑하게 SQL을 작성하기 위해 알아 두어야 할 쿼리 변환(Query Transformation) 기법에 대해 소개한다.

 

💡 이런 주제를 다뤄요
   - 쿼리 변환이란? 
   - 대표적인 쿼리 변환 두 가지 : 서브쿼리 변환, 조건절 변환
   - 서브쿼리 언네스팅과 뷰 머징
   - 조건절 Pushing과 조건절 이행

 

 

---

1. 쿼리 변환

 

쿼리 변환(Query Transformation)은 사용자가 작성한 SQL을 더 효율적인 형태로 옵티마이저가 재작성는 작업을 말한다. DBMS는 사용자가 작성한 SQL을 그대로 실행하지만은 않는다. 동일한 결과를 반환할 수 있다면 다른 형태의 SQL로 변환하기도 한다. 이러한 쿼리 변환은 다양한 조인 기법이나 접근 방식을 선택할 수 있도록  돕기 때문에 최적의 실행 계획을 도출할 가능성을 높여준다. 실행 계획을 최적화하여 쿼리의 실행 속도를 개선하고 리소스 사용량을 줄일 수 있기 때문이다.

 

1.1 휴리스틱 변환 vs 비용 기반 변환

쿼리 변환 방식은 크게 Heuristic Transformation(휴리스틱 변환)과 Cost-based Transformation(비용 기반 변환)으로 나눌 수 있다.

• 휴리스틱 변환 : 정해진 규칙에 따라 항상 적용된다.
• 비용 기반 변환 : 실행 비용을 평가하여 성능 개선이 예상될 경우에만 적용된다.

 

1.2 DBMS별 쿼리 변환 작동 여부

각 DBMS 마다 쿼리 변환 기술이 상이하다. 가장 많이 사용되는 DBMS들인 Oracle, PostgreSQL, SQL Server, MySQL과 같은 주요 DBMS들은 어떤 쿼리 변환 기술을 지원하고 있는지 간략하게 알아보자.

구분	변환 유형	Oracle	SQL Server	PostgreSQL	MySQL
Heuristic 변환	조건절 Pushing	O	O	O	O
	조건절 이행	O	O	△ (부분적 지원)	△ (제한적)
	공통 표현식 제거	O	O	△ (부분적 지원)	△
	Outer → Inner 조인 변환	O	O	O	△
Cost-based 변환	서브쿼리 Unnesting	O	O	O	△
	뷰 Merging	O	O	O	△
	OR-Expansion	O	O	△ (제한적)	O
	실체화 뷰 쿼리 재작성	O	O (Indexed View)	△ (자동 적용 X)	X
	조인 제거	O	O	O	△
	조인 조건 Pushdown	O	O	O	△
	집합 연산을 조인으로 변환	O	O	X	△
	Star 변환	O	O	O	X

 

 

오라클은 가장 강력한 변환 기능을 제공하며, SQL Server도 유사한 수준의 최적화를 지원하고 있는 것으로 나타났다.

PostgreSQL도 최근 버전들에서 꾸준히 기능을 개선하며 그 격차를 줄여가고 있다. 

MySQL은 상대적으로 제한적인 기능을 제공하고 있지만, 8.0 버전 이후 지속적으로 발전하고 있다.

PostgreSQL와 MySQL은 일부 변환을 자동 적용하지만, 튜닝이 필요한 경우도 많아 사용자가 SQL 유형별 최적화 기법에 대해서 잘 알고 있어야 한다.

 

쿼리 변환 기능은 DBMS를 선택할 때 고려해야 할 중요한 요소 중 하나가 될 수 있다. 하지만 쿼리 변환이 무조건적으로 최적의 성능을 하지는 않는다는 점을 기억해야 한다. 때로는 힌트를 사용해 쿼리 변환을 방지하거나 쿼리를 수동으로 재작성하는 것이 더 나은 결과를 가져올 수도 있기 때문이다.

 

1.3 알아두면 유용한 쿼리변환 두 가지

 

쿼리 변환 특성을 이해하고 이를 효과적으로 활용하는 것이 데이터베이스 성능 최적화의 핵심이다.
대표적인 쿼리 변환만이라도 알고 있으면 좋다. 

가장 많이 발생하고, 알아두면 유용한 쿼리 변환은 두 가지로 정리할 수 있다.

 

(1) 서브쿼리 변환 

옵티마이저가 서브쿼리를 조인 형태로 변환하는 최적화 기법이다.
`EXISTS`나 `IN` 서브쿼리를 세미조인으로 변환하거나, 스칼라 서브쿼리를 외부 조인으로 변환하는 것이 대표적인 경우이다.
중첩 루프 방식의 실행을 피하고, 해시 조인이나 소트 머지 조인 같은 효율적인 조인 방식을 사용할 수 있게 된다.

대표적인 서브쿼리 변환:

• 서브쿼리 언네스트
• 뷰 머징

(2) 조건절 변환

`WHERE`절의 조건들을 조인 조건으로 변환하거나, 다른 쿼리 블록의 `WHERE`절로 이동시키는 최적화 기법이다.
아우터 조인을 이너 조인으로 변환하거나, `BETWEEN`, `IN`, `OR` 같은 복잡한 조건을 단순화하기도 한다.
인덱스를 더 효율적으로 활용하고 테이블 액세스를 줄이는 데 유용하다.

 

대표적인 조건절 변환:

• 조건절 Pushing
• 조건절 이행

 

다음 장에서는 알아두면 유용한 서브쿼리 변환과 조건절 변환에 대해서 상세하게 알아본다. 

 

---

 

 

❗ 여기에서부터는 오라클(Oracle)을 기준으로 설명한다. 다른 DBMS도 힌트 등 세부 제어 기법이 다를 뿐 비슷한 방식으로 작동한다.

 

---

2. 서브쿼리 변환

 

서브쿼리를 처리하는 방식에는 크게 네 가지가 있다.

• 필터링(Filtering) : 메인/서브쿼리 각각의 subplan으로 최적화
• 언네스팅(Unnesting) : 서브쿼리를 풀어내서 메인쿼리와의 조인 방식으로 변환
• 인라인 뷰(Inline View) : 서브쿼리를 먼저 처리하여 집합으로 생성
• 뷰 머징(View Merging) : 인라인 뷰를 메인 쿼리와 머지하여 조인 방식으로 변환

 

이번 글에서는 네스티드 서브쿼리를 언네스팅하는 경우와, 인라인 뷰를 머지하는 경우를 살펴 본다.

 

2.1 서브쿼리 언네스팅 (Subquery Unnesting)

 

이 중 첫번째인 언네스팅에 대해서 알아 보자. 서브쿼리 언네스팅은 서브쿼리를 풀어 메인쿼리와의 일반 조인문으로 변환하는 작업을 말한다.

서브쿼리를 언네스트하여 일반 조인문으로 변환하면, 조인 방식이나 인덱스 사용에 있어서 더 많은 경우의 수를 고려하여 실행계획을 수립될 수 있다.

 

서브쿼리 언네스팅은 `/*+ UNNEST */`  힌트를 통해 지시할 수 있다. 반대로 서브쿼리를 먼저 수행하는 게 유리한 경우에는  `NO_UNNEST` 힌트를 사용하여 언네스트를 방지할 수도 있다.

 

(1)  일반 조인문으로 변환 (Simple Join Transformation)

`WHERE col IN (SELECT ...)`에서 서브쿼리의 결과가 고유(Unique)한 경우, 서브쿼리가 일반적인 조인으로 변환된다.

예제와 함께 살펴보자.

 

원본 쿼리

한국 지역에 해당하는 고객을 SELECT하는 쿼리다.

SELECT c.customer_id, c.name
FROM customers c
WHERE c.customer_region_id IN (SELECT region_id FROM region r WHERE country = 'Korea');

이 쿼리에서는  `region` 테이블에서 `region_id`가  PK(Primary Key)라고 가정한다.

 

Unnest 되지 않은 경우 실행계획

 

쿼리 변환이 일어나지 않은 경우, 작성된 쿼리와 동일하게 `IN` 조건을 먼저 구하기 위해서 `region` 테이블에서 `region_id`를 먼저 가져온 뒤 이것으로 `WHERE` 조건을 필터링하는 실행 계획이 세워진다.

----------------------------------------------------------
|   0 | SELECT STATEMENT    |           |     |     |
|*  1 |  FILTER            |           |     |     |
|   2 |   TABLE ACCESS FULL | CUSTOMERS |     |     |
|*  3 |   TABLE ACCESS FULL | REGION    |     |     |
----------------------------------------------------------
Predicate Information (identified by operation id):
----------------------------------------------------------
   1 - filter( EXISTS (SELECT 0 FROM "REGION" "R" WHERE "R"."COUNTRY"='Korea' AND :B1="R"."REGION_ID"))
   3 - filter("COUNTRY"='Korea')

 

Unnest 된 실행계획 (쿼리 변환) 

 

쿼리가 변환된 경우, 다음과 같이 일반 조인에 해당하는 실행계획이 수립된다.

예시 실행계획에서는 NL조인이 일어났지만, 적절한 인덱스가 없다면 Hash 조인 등 다른 조인 방식으로 실행될 수 있다.

----------------------------------------------------------
|   0 | SELECT STATEMENT     |           |     |     |
|   1 |  NESTED LOOPS        |           |     |     |
|   2 |   TABLE ACCESS FULL  | CUSTOMERS |     |     |
|*  3 |   TABLE ACCESS BY    | REGION    |     |     |
|     |     INDEX ROWID      |           |     |     |
----------------------------------------------------------
Predicate Information (identified by operation id):
----------------------------------------------------------
   3 - filter("COUNTRY"='Korea')

어떻게 이런 실행 계획이 실행될 수 있었을까?

 

내부적으로 다음과 같이 쿼리 변환이 일어났기 때문이다. 일어나는 경우 서브쿼리가 제거되고, 일반적인 `INNER JOIN`으로 변환된다.

SELECT c.customer_id, c.name
FROM customers c
INNER JOIN region r 
ON c.customer_region_id = r.regtion_id
WHERE r.country = 'Korea';

 

(2) 세미조인으로 변환

 

`EXISTS` 또는 `IN`을 사용할 때, 중복 데이터를 고려하지 않고 빠르게 조인 수행하기 위해서 일어날 수 있다. NESTED LOOPS SEMI JOIN 또는 HASH SEMI JOIN이 발생한다.

 

원본 쿼리

 

`order` 테이블에서 주문 내역이 있는 고객을 추출하기 위한 쿼리다. 

SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

 

Unnest 되지 않은 경우 실행 계획

 

필터 형식으로 실행계획이 수립된다.

---------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |     |     |
|*  1 |  FILTER                       |           |     |     |
|   2 |   TABLE ACCESS FULL           | CUSTOMERS |     |     |
|*  3 |   TABLE ACCESS BY INDEX ROWID | ORDERS    |     |     |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------------------
   1 - filter( EXISTS (SELECT 0 FROM "ORDERS" "O" WHERE "O"."CUSTOMER_ID"=:B1))
   3 - access("O"."CUSTOMER_ID"=:B1)

 

Unnest 된 실행계획(쿼리 변환)

 

쿼리 변환이 일어나 서브쿼리가 언네스트 된 경우, 세미조인 방식으로 처리 된다. 한 건만 조인에 성공하면 다음 레코드로 넘어갈 수 있기 때문에 필터 방식보다 빠르게 수행될 수 있다.

----------------------------------------------------------
|   0 | SELECT STATEMENT    |           |     |     |
|*  1 |  HASH JOIN SEMI     |           |     |     |
|   2 |   TABLE ACCESS FULL | CUSTOMERS |     |     |
|   3 |   TABLE ACCESS FULL | ORDERS    |     |     |
----------------------------------------------------------
Predicate Information (identified by operation id):
----------------------------------------------------------
   1 - access("C"."CUSTOMER_ID"="O"."CUSTOMER_ID")

위 실행계획에서는 세미 해시 조인이 일어났지만, NESTED LOOP SEMI가 일어날 수도 있다. 옵티마이저가 인덱스나 테이블 통계정보에 따라서 적절한 조인 방식을 선택해서 실행계획을 수립한다.

 

2.2 뷰 머징(View Merging)

 

`FROM`절에 기입된 서브쿼리를 인라인 뷰(Inline View)라고 한다. 인라인 뷰의 경우 메인 쿼리에 머지하는 경우 일반적으로 더 효율적인 실행계획을 수립할 수 있다. 때문에 일반적으로 뷰 머징이 일어난다. 뷰가 매우 복잡하거나 여러 번 재사용되는 경우에는 머징하지 않는 것이 유리할 수 있다.

 

뷰 머징을 일어나도록 하려면 `/*+ MERGE(subquery_alias) */`  힌트를 통해 지시할 수 있다. 반대로 방지하려면 `NO_MERGE(subquery_alias)` 힌트를 사용하면 된다.

 

원본 쿼리

다음과 같이 2024년 이후에 주문을 한 고객의 주문일자별 합계가 10000 이상인 고객의 일자별 주문금액을 조회하고 있다.

SELECT c.customer_name, v.order_date, v.total_amount
FROM customers c
JOIN (
    SELECT customer_id, order_date, SUM(amount) as total_amount
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY customer_id, order_date
) v
ON c.customer_id = v.customer_id
WHERE v.total_amount > 10000;

`v`에 해당하는 서브쿼리가 인라인 뷰이다.

 

뷰 머징이 일어나지 않은 실행 계획

 

뷰 머징이 일어나지 않으면, 사용자가 기재한 SQL대로 먼저 인라인 뷰에 대한 연산을 먼저 처리하고, `customers` 테이블과 조인하는 형식의 실행계획이 나타난다.

----------------------------------------------------------
|   0 | SELECT STATEMENT     |           |     |     |
|*  1 |  HASH JOIN           |           |     |     |
|   2 |   TABLE ACCESS FULL  | CUSTOMERS |     |     |
|*  3 |   VIEW               |           |     |     |
|*  4 |    HASH GROUP BY     |           |     |     |
|*  5 |     TABLE ACCESS FULL| ORDERS    |     |     |
----------------------------------------------------------
Predicate Information (identified by operation id):
----------------------------------------------------------
   1 - access("C"."CUSTOMER_ID"="V"."CUSTOMER_ID")
   3 - filter("V"."TOTAL_AMOUNT">10000)
   4 - group by("CUSTOMER_ID", "ORDER_DATE")
   5 - filter("ORDER_DATE">=TO_DATE('2024-01-01'))

VIEW 연산자가 별도로 존재하는 걸 확인할 수 있다. 또한 인라인 뷰 내부에서 `GROUP BY`가 먼저 실행되고, 인라인 뷰의 결과를 저장한 후 조인이 일어난다.

 

뷰 머징된 실행계획(쿼리 변환)

----------------------------------------------------------
|   0 | SELECT STATEMENT     |           |     |     |
|*  1 |  HASH GROUP BY       |           |     |     |
|*  2 |   HASH JOIN          |           |     |     |
|   3 |    TABLE ACCESS FULL | CUSTOMERS |     |     |
|*  4 |    TABLE ACCESS FULL | ORDERS    |     |     |
----------------------------------------------------------
Predicate Information (identified by operation id):
----------------------------------------------------------
   1 - having("SUM(O.AMOUNT)">10000)
   2 - access("C"."CUSTOMER_ID"="O"."CUSTOMER_ID")
   4 - filter("O"."ORDER_DATE">=TO_DATE('2024-01-01'))

테이블끼리 해시 조인을 한 이후 `GROUP BY`를 실행하는 것을 볼 수 있다.

 

다음과 같이 쿼리가 변환되었기 때문이다.

SELECT c.customer_name, o.order_date, SUM(o.amount) as total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'
GROUP BY c.customer_name, c.customer_id, o.order_date
HAVING SUM(o.amount) > 10000;

인라인 뷰의 결과 집합이 적어지는 경우 `NO_UNNEST`를 통해 뷰 머징을 방지해주는 것이 더 유리하다.

 

이상으로 서브쿼리 변환의 대표적 케이스인 서브쿼리 언네스팅과 뷰 머징을 알아보았다.

 

---

 

3. 조건절 변환

 

조건절 변환에는 조건절 Pushing과 조건절 이행이 있다.

• 조건절 Pushing : 메인쿼리 조건을 서브쿼리로 밀어 넣거나, 그 반대로 서브쿼리의 조건을 메인쿼리로 당겨오는 것
• 조건절 이행 : 옵티마이저가 쿼리의 조건들을 분석하여 추가적인 조건을 생성

 

3.1 조건절 Pushing

 

조건절 Pushing(Predicate Pushdown)은 뷰나 인라인 뷰, 조인 등에서 조건절을 최대한 데이터 액세스 초기 단계에 처리할 수 있도록 하는 쿼리 변환 기법이다.

 

변환 전 쿼리

 

실무에서는 다음과 같이 인라인 뷰로 `FROM`절에 조회 대상이 되는 테이블들을 만들어놓고, 메인 쿼리에 조건절을 기입 하는 경우가 흔하다. 

이렇게 작성하면 쿼리의 가독성은 좋을 수 있지만, `FROM`절의 조인 대상을 줄이지 않고 모든 레코드를 조인한 뒤에 메인 쿼리에서 필터링하는 비효율이 있을 수 있다.

SELECT *
FROM (
    SELECT e.employee_id, e.first_name, 
           d.department_name, 
           l.city,
           e.salary,
           e.hire_date
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    JOIN locations l ON d.location_id = l.location_id
) v
WHERE v.salary > 5000
AND v.city = 'Seattle'
AND v.hire_date >= DATE '2023-01-01';

-- 변환 전 실행계획
----------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     |     |
|*  1 |  VIEW                 |            |     |     |
|   2 |   HASH JOIN           |            |     |     |
|   3 |    HASH JOIN          |            |     |     |
|   4 |     TABLE ACCESS FULL | EMPLOYEES  |     |     |
|   5 |     TABLE ACCESS FULL | DEPARTMENTS|     |     |
|   6 |    TABLE ACCESS FULL  | LOCATIONS  |     |     |

 

위 실행계획을 보면, `employees` 테이블, `departments` 테이블, `locations` 테이블을 모두 읽은 뒤 모든 레코드끼리 조인을 한다.

세 개 테이블의 전체 레코드를 대상으로 조인을 한 뒤에 마지막 단계에서 조건에 맞지 않는(급여, 도시, 채용일자) 레코드를 골라내고 있다.

애초부터 조건에 맞지 않는(급여, 도시, 채용일자) 레코드를 골라낸 뒤 조인을 하면 일량이 훨씬 줄어들텐데도 말이다.

 

조건절 Pushing 쿼리 변환

 

다행히도 똑똑한 DBMS는 아래와 같이 자동으로 쿼리 변환을 해준다.

SELECT *
FROM (
    SELECT e.employee_id, e.first_name, 
           d.department_name, 
           l.city,
           e.salary,
           e.hire_date
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    JOIN locations l ON d.location_id = l.location_id
    WHERE e.salary > 5000              -- employees 테이블 조건
    AND l.city = 'Seattle'             -- locations 테이블 조건
    AND e.hire_date >= DATE '2023-01-01'  -- employees 테이블 조건
) v;

-- 변환 후 실행계획
-----------------------------------------------------------
|   0 | SELECT STATEMENT         |            |     |     |
|   1 |  HASH JOIN               |            |     |     |
|   2 |   HASH JOIN              |            |     |     |
|*  3 |    TABLE ACCESS BY INDEX | EMPLOYEES  |     |     |
|   4 |    TABLE ACCESS FULL     | DEPARTMENTS|     |     |
|*  5 |   TABLE ACCESS BY INDEX  | LOCATIONS  |     |     |

위와 같이 쿼리를 변환하면, 다음과 같은 이점이 있다.

• 조인 전에 필터링이 수행되어 중간 결과집합 크기 감소
  • `locations` 테이블에서 'Seattle' 조건을 먼저 적용하여 조인할 레코드 감소
  • `employees` 테이블에서 salary와 hire_date 조건을 먼저 적용 하여 조인할 레코드 감소
• city, salary, hire_date 각각의 인덱스를 활용 가능

 

3.2 조건절 이행

조건절 이행(Transitive Predicate Generation)은 옵티마이저가 쿼리의 조건들을 분석하여 추가적인 조건을 생성하는 최적화 기법이다.

`A=B`와 `B=C`라는 조건이 주어졌을 때, 우리는 `A=B`이고 `B=C`라면 `A=C`라는 걸 알 수 있다. 옵티마이저도 이러한 추론을 통해서 쿼리를 최적화해준다.

꼭 equi 조건(`=`)이 아니더라도 `A<B`, `B<C`를 통해 `A<C`라는 점도 추론해서 적용해낼 수 있다.

 

예시를 통해 확인해보자.

 

변환 전 쿼리

 

`WHERE` 조건에서 비효율을 찾아보자.

SELECT * FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND e.department_id = 10

 

조건절 이행 쿼리 변환

 

옵티마이저는 아래와 같이 쿼리를 변환한다.

주어진 조건식을 통해서 `d.department_id = 10`라는 조건을 추론한 것이다.

SELECT * FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND e.department_id = 10
AND d.department_id = 10; -- 옵티마이저가 내부적으로 추가하는 조건
                          -- e.department_id = d.department_id AND e.department_id = 10 조건으로부터 추론

이렇게 조건식을 추가하면 `departments` 테이블에서 `d.department_id = 10`인 레코드만 읽어올 수 있으며, 이 과정에서 적절한 인덱스를 사용할 수도 있게 된다.

 

4. 마치며

알아두면 유용한 쿼리 변환 유형인 서브쿼리 변환과 조건절 변환에 대해서 알아보았다. DBMS 옵티마이저가 쿼리를 최적화하기 위해서 사용하는 쿼리 변환은 이보다 더 다양하다.

 

사용자가 이러한 쿼리 변환 방식과 이에 따른 성능 차이에 대해서 이해하고 있다면, 쿼리를 작성하면서 좀 더 현명한 방식으로 작성할 수 있게 된다. 뿐만 아니라 예상보다 처리 시간이 많이 걸리는 쿼리가 발견되면 실행계획을 확인하고 명시적인 힌트나 쿼리 리팩토링을 통해 최적화해볼 수도 있을 것이다. 각 DBMS의 최적화 방식의 차이를 이해하고, 필요에 따라 쿼리 튜닝 전략을 수립하자.

 

---

참고 자료

1. 조시형, 오라클 성능 고도화 원리와 해법 2, 디비안, https://product.kyobobook.co.kr/detail/S000061696048

2. Claude Sonet 3.5 (본문 요약)

3. Chat-GPT (DBMS별 쿼리 변환 비교 및 예제 쿼리 생성)