쿼리 변환 - 뷰 머징·조건절 푸싱·OR 확장
이 장에서 배우는 것
온라인 서점의 주문 검색 화면에 고객 번호와 행사 번호를 함께 입력하는 기능이 추가되었다. 검색 대상은 많지 않은데도 주문 테이블 전체를 읽는다. SQL에는 공통 조건을 모은 인라인 뷰와 고객별 집계가 겹쳐 있다. 개발자는 뷰를 제거해야 한다고 생각하지만, 실행계획을 보면 뷰의 존재와 읽기량이 반드시 함께 움직이지는 않는다.
이 장에서는 쿼리 변환(Query Transformation)이 검색 조건과 테이블 접근의 관계를 어떻게 바꾸는지 살펴본다. 앞 장에서 다룬 서브쿼리의 조인 변환을 확장하지 않고, FROM 절의 뷰 경계와 OR 조건에 집중한다. 실습은 하나의 주문 검색 화면을 대상으로 변환별 진단 SQL을 실행한 뒤, 결과와 읽기량을 함께 비교하는 방식이다.
- 뷰 머징(View Merging)이 가능한 구조와 의미 보존 때문에 제약을 받는 구조를 구분한다.
- 조건절 푸싱(Predicate Pushing)이 뷰를 남겨 둔 채 읽는 범위를 줄이는 과정을 확인한다.
- OR 확장(OR Expansion)을 직접 작성할 때 중복 행과 NULL을 올바르게 처리한다.
- 힌트의 의도, 실제 실행계획, 논리 읽기량을 분리해서 검증한다.
문제 상황
운영자가 고객 문의를 처리하면서 “이 고객의 주문 또는 이 행사에 속한 주문”을 조회한다. 화면에는 일치하는 주문 건수와 결제금액 합계가 표시된다. 고객 번호만 입력하는 경로에는 공통 인라인 뷰가 있고, 고객별 요약 경로에는 GROUP BY가 있다. 복합 검색 경로에는 과거에 대량 조회를 안정화하려고 넣었던 FULL 힌트가 남아 있다.
고객과 행사 조건은 각각 선택적이다. 그러나 기존 복합 검색 SQL은 FULL 힌트 때문에 전체 읽기를 유지한다. 이 사례의 개선 대상은 OR 문법 자체가 아니라, 각 조건이 사용할 수 있는 접근 경로를 열어 주지 못하는 SQL 구조와 기존 힌트다. 실제 수정 후보에는 기존 힌트만 제거한 OR SQL도 포함해야 한다.
실습 데이터는 주문 120,000건이다. 앞의 10,000건은 비회원 주문으로 고객 번호가 NULL이다. 고객 42의 결제 완료 주문은 11건이고, 행사 42의 결제 완료 주문은 120건이다. 고객 조건에 맞는 11건은 모두 행사 조건에도 맞는다. 따라서 두 조건을 OR로 연결한 결과는 131건이 아니라 120건이다. 행사 조건에 맞는 비회원 주문 10건도 빠뜨리면 안 된다.
실습은 전용 스키마에서 실행한다. macOS와 Linux는 SQL*Plus 클라이언트를 실행하는 환경이며, 접속 대상은 Oracle 19c 서버다. 운영 테이블을 복제하거나 변경할 필요는 없다. 아래 프로그램은 QT06으로 시작하는 실습 객체만 생성하며, 같은 이름이 이미 있으면 중단한다.
뷰 경계가 없어지는 것과 조건이 들어가는 것은 다르다
인라인 뷰는 SQL을 읽기 좋게 나누는 표현이다. 괄호 안에 적었다고 해서 반드시 그 결과를 모두 만든 뒤 바깥 SQL을 실행하지는 않는다. 단순한 조회와 필터로 이루어진 뷰는 바깥 쿼리 블록과 합쳐질 수 있다. 이때 옵티마이저는 합쳐진 범위에서 접근 경로를 검토한다.
반대로 실행계획에 VIEW가 남아 있다고 해서 전체 결과가 임시 공간에 저장된다는 뜻도 아니다. VIEW는 행을 전달하는 경계로 남을 수 있다. 결과 저장 여부는 임시 테이블 변환과 관련 연산 등 추가 근거로 판단해야 한다.
머징과 조건 이동은 서로 다른 관찰 항목이다. 뷰를 합치지 않아도 바깥의 고객 조건이 안쪽 테이블 접근에 반영될 수 있다. NO_MERGE를 붙였는데도 인덱스로 소수의 주문만 읽는다면 모순이 아니다. 따라서 “뷰가 사라졌는가”와 “조건이 어디에서 적용되는가”를 따로 확인한다.
실습의 M0는 머징을 억제하고 M1은 머징을 요청한다. 두 SQL의 읽기량이 같아도 정상이다. M0에서 이미 고객 번호가 인덱스 접근 조건으로 사용되었다면, 머징만으로 추가로 줄일 읽기량이 없기 때문이다. 이 비교는 개선 효과를 과장하지 않기 위한 대조 실험이다.
| 뷰 내부 구조 | 머징 판단 | 확인할 조건 |
|---|---|---|
| 단순 조회와 필터 | 머징 후보가 된다 | 테이블 접근 조건까지 내려갔는가 |
| GROUP BY 또는 DISTINCT | 복합 변환의 적용 가능성과 비용을 따진다 | 집계 전후 의미가 유지되는가 |
| UNION ALL | 단순 뷰 머징과 구분한다 | 조건이 각 분기에 반영되는가 |
| ROWNUM 또는 행 제한 | 이동으로 선택 행이 달라질 수 있다 | 필터와 행 제한의 선후 관계가 유지되는가 |
GROUP BY가 있다는 이유만으로 Oracle에서 모든 머징이 막힌다고 단정하면 안 된다. 단순 머징과 집계가 포함된 복합 머징은 적용 조건이 다르다. MERGE 힌트도 결과를 바꾸는 변환을 허용하는 명령은 아니다. 변환의 일반 원리와 보안 관련 제약은 Oracle 19c 쿼리 변환 문서에서 확인할 수 있다.
집계 전에 옮겨도 되는 조건을 찾는다
고객별 집계 뷰에서 customer_id = 42는 특정 그룹을 고르는 조건이다. 고객 42 이외의 행을 먼저 제외해도 고객 42의 건수와 합계는 달라지지 않는다. 따라서 이 조건은 집계 전 WHERE 절에 둘 수 있다. 옵티마이저가 자동으로 같은 이동을 수행한다면, SQL을 고쳐도 실행계획과 읽기량은 같을 수 있다.
select v.customer_id, v.total_amount
from (
select customer_id, sum(amount) total_amount
from qt06_orders
where status = 'PAID'
group by customer_id
) v
where v.customer_id = 42;반면 total_amount > 1000은 집계 결과를 고르는 조건이다. 이를 amount > 1000으로 바꾸면 개별 주문을 제거하게 된다. 같은 컬럼 계열을 비교하더라도 조건의 대상이 그룹인지 행인지에 따라 이동 가능성이 달라진다. 집계값 조건을 안쪽에 표현하려면 HAVING을 사용한다.
실습의 P0는 고객 조건을 집계 뷰 바깥에 적고, P1은 원본 테이블의 WHERE 절에 적는다. 두 SQL 모두 NO_MERGE로 뷰 경계를 유지하도록 요청한다. P0의 인덱스 접근 조건에 고객 번호가 보이면 머징 없이 조건이 이동한 것이다. P0가 이미 효율적이라면 P1의 효과는 실행 비용 감소보다 의도의 명시화에 있다.
조건을 일찍 적는 습관에도 전제가 있다. 집계 키 조건처럼 동치성이 분명한 경우에만 옮긴다. 행 제한, 분석 함수, 외부 조인이 포함되면 같은 이동이 결과를 바꿀 수 있다. SQL의 들여쓰기 위치를 기준으로 판단하지 말고, 이동 전후 어느 행 집합을 대상으로 연산하는지 비교한다.
NO_PUSH_PRED를 모든 조건 이동을 막는 스위치로 사용하는 것도 피한다. 이 힌트는 뷰로 들어가는 조인 조건 푸싱과 관련된다. 단순 상수 필터의 이동을 억제하는 실험과 혼동하면, 힌트가 무시되었다고 잘못 판단하기 쉽다.
OR을 나눌 때는 중복과 NULL을 함께 처리한다
OR 조건은 두 검색 집합의 합집합을 만든다. 옵티마이저는 비용이 유리하다고 판단하면 이를 여러 분기로 바꿀 수 있다. 직접 UNION ALL로 작성하면 분기마다 다른 인덱스를 사용할 여지가 생기지만, 두 조건에 모두 맞는 주문을 한 번만 반환하도록 작성자가 보장해야 한다.
첫 분기는 고객 조건을 담당한다. 둘째 분기는 행사 조건에 맞으면서 첫 분기에 포함되지 않은 주문을 담당한다. Oracle의 LNNVL은 인수 조건이 거짓이거나 UNKNOWN이면 참이 된다. 따라서 LNNVL(customer_id = 42)는 다른 고객뿐 아니라 고객 번호가 NULL인 주문도 둘째 분기에 남긴다.
customer_id <> 42는 NULL인 행을 통과시키지 않는다. NOT(customer_id = 42)도 같은 문제가 있다. 일반적인 등가 표현은 customer_id <> 42 OR customer_id IS NULL이다. 단, 비교 대상 자체가 NULL일 수 있는 바인드라면 그 경우까지 포함해 논리를 다시 검토해야 한다.
UNION으로 바꾸는 것도 일반적인 해결책은 아니다. UNION은 출력 컬럼 값이 같은 행을 제거한다. 서로 다른 두 주문의 결제금액이 같으면, 결제금액만 조회하는 SQL에서 두 주문이 하나로 줄어들 수 있다. 원래 OR 조회가 유지하던 행의 개수를 보존하려면 분기를 배타적으로 나누는 편이 명확하다.
| 항목 | Oracle 19c | MySQL 8.0 |
|---|---|---|
| 집계 뷰 | 조건을 만족하면 복합 머징을 검토한다 | 집계와 GROUP BY는 파생 테이블 머징을 막는 요소다 |
| 비머징 뷰의 조건 이동 | 뷰 경계를 유지하면서 조건을 이동할 수 있다 | 파생 조건 푸싱은 8.0.22부터 지원하며 제약을 확인한다 |
| UNION의 조건 이동 | 각 분기의 조건과 접근 경로를 확인한다 | 8.0.29부터 지원 범위가 확대되었다 |
| 실습 문법과 측정 | LNNVL, DBMS_XPLAN, 커서별 읽기 통계를 사용한다 | NULL을 명시한 조건으로 바꾸며 측정 코드는 별도로 작성한다 |
MySQL에서도 OR SQL이 하나의 접근 방식으로 고정되는 것은 아니다. 인덱스 병합 등 다른 후보가 있으므로 UNION ALL이 항상 유리하다고 볼 수 없다. 세부 버전과 적용 제한은 파생 테이블 최적화 문서와 파생 조건 푸싱 문서를 근거로 확인한다.
완성 코드
다음 내용을 qt06.sql로 저장한다. CREATE TABLE 권한과 테이블스페이스 할당량이 필요하다. 측정용 계정은 V_$SQL, V_$SQL_PLAN, V_$SQL_PLAN_STATISTICS_ALL, V_$SESSION을 조회할 수 있어야 하며 DBMS_STATS와 DBMS_XPLAN을 실행할 수 있어야 한다. 권한은 접속 대상 PDB에서 준비한다. 운영 계정에 광범위한 사전 조회 권한을 추가하는 방식으로 실습하지 않는다.
프로그램은 각 SQL을 한 번 예열한 다음 한 번 더 실행한다. 두 실행 사이의 해당 자식 커서 BUFFER_GETS 차이를 저장하고, 두 번째 실행의 계획을 함께 보관한다. 예열 실행과 결과 저장 SQL의 읽기량은 차이에 포함되지 않는다. 동일 계정에서 같은 실습 SQL을 동시에 실행하지 않는 조건이다.
qt06.sql
set echo off
set verify off
set feedback off
set heading off
set serveroutput on size unlimited
set linesize 200
set pagesize 0
set trimspool on
whenever oserror exit failure
whenever sqlerror exit failure rollback
create table qt06_orders (
order_id number not null,
customer_id number,
promo_id number,
status varchar2(10) not null,
amount number(12,2) not null,
constraint qt06_orders_pk primary key (order_id)
);
insert into qt06_orders
select level,
case when level <= 10000 then null
else mod(level, 10000) + 1 end,
case when mod(level, 20) = 0 then null
else mod(level, 1000) + 1 end,
case when mod(level, 4) = 0 then 'CANCELLED'
else 'PAID' end,
100
from dual
connect by level <= 120000;
create index qt06_cust_ix on qt06_orders(customer_id);
create index qt06_promo_ix on qt06_orders(promo_id);
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname => 'QT06_ORDERS',
estimate_percent => 100,
method_opt => 'FOR ALL COLUMNS SIZE 1',
cascade => true
);
end;
/
create table qt06_result (
test_name varchar2(2) primary key,
order_count number not null,
total_amount number not null,
buffer_gets number not null,
sql_id varchar2(13) not null,
child_number number not null
);
create table qt06_plan (
test_name varchar2(2) not null,
line_no number not null,
plan_line varchar2(4000),
constraint qt06_plan_pk primary key (test_name, line_no)
);
declare
procedure run_case(
p_name varchar2,
p_sql varchar2,
p_count number
) is
l_count number;
l_total number;
l_sql_id varchar2(13);
l_child number;
l_gets_before number;
l_gets_after number;
l_exec_before number;
l_exec_after number;
l_line number := 0;
begin
-- 01: Warm up and identify the executed child cursor.
execute immediate p_sql into l_count, l_total;
select sql_id, child_number, buffer_gets, executions
into l_sql_id, l_child, l_gets_before, l_exec_before
from v$sql
where sql_text = p_sql
and parsing_schema_name = user
order by last_active_time desc, child_number desc
fetch first 1 row only;
-- 02: Measure one complete execution.
execute immediate p_sql into l_count, l_total;
select buffer_gets, executions
into l_gets_after, l_exec_after
from v$sql
where sql_id = l_sql_id
and child_number = l_child;
if l_exec_after - l_exec_before != 1 then
raise_application_error(-20001, 'Cursor execution changed');
end if;
-- 03: Check deterministic business results.
if l_count != p_count or l_total != p_count * 100 then
raise_application_error(-20002, 'Result mismatch: ' || p_name);
end if;
insert into qt06_result
values (
p_name, l_count, l_total,
l_gets_after - l_gets_before, l_sql_id, l_child
);
-- 04: Preserve the last execution plan before another test.
for r in (
select plan_table_output
from table(dbms_xplan.display_cursor(
l_sql_id, l_child,
'ALLSTATS LAST +PREDICATE +ALIAS +OUTLINE'
))
) loop
l_line := l_line + 1;
insert into qt06_plan
values (p_name, l_line, r.plan_table_output);
end loop;
dbms_output.put_line(
p_name || ': count=' ||
to_char(l_count, 'FM9999990') || ', total=' ||
to_char(l_total, 'FM9999990')
);
end;
begin
-- 05: Keep the view, then request merging.
run_case('M0', q'~
select /*+ gather_plan_statistics no_merge(v) */ /* QT06:M0 */
count(*), nvl(sum(v.amount), 0)
from (
select order_id, customer_id, amount
from qt06_orders
where status = 'PAID'
) v
where v.customer_id = 42
~', 11);
run_case('M1', q'~
select /*+ gather_plan_statistics merge(v) */ /* QT06:M1 */
count(*), nvl(sum(v.amount), 0)
from (
select order_id, customer_id, amount
from qt06_orders
where status = 'PAID'
) v
where v.customer_id = 42
~', 11);
-- 06: Compare an outer group-key filter with an explicit one.
run_case('P0', q'~
select /*+ gather_plan_statistics no_merge(v) */ /* QT06:P0 */
nvl(sum(v.order_count), 0), nvl(sum(v.total_amount), 0)
from (
select customer_id, count(*) order_count,
sum(amount) total_amount
from qt06_orders
where status = 'PAID'
group by customer_id
) v
where v.customer_id = 42
~', 11);
run_case('P1', q'~
select /*+ gather_plan_statistics no_merge(v) */ /* QT06:P1 */
nvl(sum(v.order_count), 0), nvl(sum(v.total_amount), 0)
from (
select customer_id, count(*) order_count,
sum(amount) total_amount
from qt06_orders
where status = 'PAID'
and customer_id = 42
group by customer_id
) v
~', 11);
-- 07: Reproduce the old forced full scan.
run_case('O0', q'~
select /*+ gather_plan_statistics full(o) no_expand */ /* QT06:O0 */
count(*), nvl(sum(o.amount), 0)
from qt06_orders o
where o.status = 'PAID'
and (o.customer_id = 42 or o.promo_id = 42)
~', 120);
-- 08: Split the search into mutually exclusive branches.
run_case('O1', q'~
select /*+ gather_plan_statistics */ /* QT06:O1 */
count(*), nvl(sum(v.amount), 0)
from (
select o.amount
from qt06_orders o
where o.status = 'PAID'
and o.customer_id = 42
union all
select o.amount
from qt06_orders o
where o.status = 'PAID'
and o.promo_id = 42
and lnnvl(o.customer_id = 42)
) v
~', 120);
commit;
dbms_output.put_line('CHECKS PASSED');
end;
/
exit success計測용 익명 블록에는 예외를 삼키는 처리나 미구현 부분을 두지 않았다. 다만 이 원고에서 Oracle 서버에 접속해 컴파일과 실행을 검증한 것은 아니다. 아래 확정 출력은 데이터 생성식과 검증식으로 결정되는 값이며, 실행계획과 읽기량은 실제 접속 환경에서 수집해야 한다.
줄별 해설
테이블 정의의 order_id는 주문의 식별자다. 고객 번호를 NULL 허용으로 선언한 것은 비회원 주문을 의도적으로 시험하기 위해서다. 모든 주문 금액을 100으로 고정했으므로 결과 건수와 합계를 사람이 함께 검산할 수 있다. 고객 번호와 행사 번호에는 각각 인덱스를 만들어 두 조건의 독립적인 접근 가능성을 마련한다.
데이터 생성식에서 행사 42는 order_id를 1,000으로 나눈 나머지가 41인 행이다. 이러한 행은 120개이고 모두 결제 완료다. 고객 42는 나머지가 41인 10,000 간격의 주문 중 앞의 비회원 구간을 제외한 11개다. 따라서 중복 제거와 NULL 보존을 한 데이터 집합으로 검증할 수 있다.
주석 01은 첫 실행 후 SQL 본문이 같은 커서를 찾는다. SQL_ID만 저장하지 않고 CHILD_NUMBER도 저장하는 이유는 같은 SQL에 여러 자식 커서가 있을 수 있기 때문이다. 파싱 스키마도 제한한다. 실습 SQL은 짧아서 V$SQL.SQL_TEXT와 전체 문자열을 직접 비교할 수 있다.
주석 02는 두 번째 실행 전후의 누적 읽기량 차이를 계산한다. GATHER_PLAN_STATISTICS는 계획별 실제 실행 통계를 요청한다. 두 번째 실행이 같은 자식 커서에서 정확히 한 번 증가하지 않으면 측정을 중단한다. 커서 교체나 동시 실행을 조용히 정상 측정으로 취급하지 않기 위해서다.
주석 03은 각 실험의 건수와 금액을 검사한다. 단순 건수와 합계만으로 임의의 운영 SQL의 동치성이 증명되지는 않는다. 이 데이터는 금액이 고정되고 기대 집합이 명확한 실습이다. 운영 적용에서는 주문 식별자와 중복 횟수까지 비교해야 한다.
주석 04는 방금 실행한 커서의 계획을 행 순서대로 보관한다. DBMS_XPLAN에 SQL_ID와 자식 번호를 명시하므로 중간에 실행한 사전 조회 SQL의 계획을 잘못 출력하지 않는다. 전체 SQL의 읽기량은 QT06_RESULT에서 비교하고, 연산별 위치는 QT06_PLAN으로 설명한다.
주석 05와 06은 뷰 경계와 조건 위치를 분리해서 관찰하는 대조군이다. 주석 07의 FULL과 NO_EXPAND는 기존의 전체 읽기 경로를 재현하기 위한 실험 제약이다. 따라서 O0와 O1의 차이를 모두 수동 OR 확장의 독립적인 효과라고 해석해서는 안 된다. 기존 FULL을 제거한 효과도 함께 포함된다.
주석 08의 둘째 분기는 행사 인덱스로 찾은 후보 중 고객 조건이 참인 주문만 제외한다. 공통 결제 상태 조건은 양쪽 분기에 모두 필요하다. 최상위 집계는 분기를 합친 뒤 한 번 적용하므로 화면에 표시되는 집계의 범위도 유지된다.
실행 결과
다음 명령은 접속 비밀번호를 대화식으로 받는다. 접속 주소와 서비스 이름은 준비한 Oracle 19c 환경에 맞게 바꾼다. 처음 실행하며 필요한 권한과 할당량이 준비되어 있다면, SQL*Plus의 성공 출력은 다음과 같다.
sqlplus -s qt_lab@//localhost:1521/ORCLPDB1 @qt06.sqlEnter password:
M0: count=11, total=1100
M1: count=11, total=1100
P0: count=11, total=1100
P1: count=11, total=1100
O0: count=120, total=12000
O1: count=120, total=12000
CHECKS PASSED측정값과 계획은 같은 계정으로 다시 접속해 다음 SQL로 읽는다. 이 조회의 출력은 서버의 블록 크기, 저장 상태, 통계와 옵티마이저 설정에 따라 달라지므로 고정된 예상값을 제시하지 않는다. CHECKS PASSED는 업무 결과 검증의 성공이며 특정 계획이나 성능 향상을 보장하는 문구가 아니다.
select test_name, order_count, total_amount, buffer_gets
from qt06_result
order by test_name;
select plan_line
from qt06_plan
where test_name = 'O1'
order by line_no;아래 표는 읽기량 비교법을 설명하기 위한 가상 관측값이다. 실제 측정 결과가 아니며 프로그램의 예상 출력도 아니다. 실습에서는 저장된 수치로 대체한다. M0와 M1, P0와 P1이 각각 같다는 사실도 유효한 결과다.
| 실험 | 실행계획의 핵심 | 논리 읽기 예시 | 해석 |
|---|---|---|---|
| M0 → M1 | VIEW 유지 → 제거, 고객 인덱스 접근 유지 | 14 → 14 | 머징만으로 추가 감소가 없다 |
| P0 → P1 | 양쪽 모두 고객 조건을 집계 전에 적용 | 14 → 14 | 자동 조건 이동과 명시적 작성이 같은 결과를 낸다 |
| O0 → O1 | 전체 읽기 → 두 인덱스 분기 | 520 → 142 | 불필요한 주문 블록 접근이 줄어든다 |
다음은 O0와 O1에서 확인할 연산 관계를 줄인 도식이다. DBMS_XPLAN의 실제 출력이나 고정된 연산 번호를 옮긴 것이 아니다. O1에서도 비용 판단에 따라 다른 접근 경로가 나올 수 있다.
O0
SORT AGGREGATE
TABLE ACCESS FULL QT06_ORDERS
filter: status and (customer_id or promo_id)
O1
SORT AGGREGATE
VIEW
UNION-ALL
TABLE ACCESS BY INDEX ROWID QT06_ORDERS
INDEX RANGE SCAN QT06_CUST_IX
TABLE ACCESS BY INDEX ROWID QT06_ORDERS
INDEX RANGE SCAN QT06_PROMO_IX
filter at table: status and LNNVL(customer_id = 42)실제 계획에서는 고객 번호와 행사 번호가 각각 access 조건인지 확인한다. 행사 분기의 인덱스 후보는 120건이지만, 고객 분기와 겹치는 11건을 제외한 반환 행은 109건이다. 비회원 10건이 이 109건에 포함되어야 한다. 예상과 다른 A-Rows가 나오면 성능보다 먼저 분기 조건을 점검한다.
논리 읽기는 물리 디스크 읽기와 다르다. 캐시에 있는 블록도 반복해서 방문하면 논리 읽기가 증가한다. 또한 계획의 부모와 자식 Buffers를 모두 더하면 중복 계산할 수 있다. 전체 비교에는 커서별 증가량을 사용하고, 계획 수치는 읽기 발생 위치를 찾는 데 사용한다.
가상 값 520과 142를 사용하면 감소율은 약 72.7%다. 이 비율을 운영 효과로 인용해서는 안 된다. 실제 배포 판단에는 기본 OR에서 기존 힌트만 제거한 후보도 추가하고, 선택도가 다른 입력값으로 같은 측정을 반복해야 한다. 행사 대상이 대부분의 주문을 차지한다면 두 분기를 읽는 비용이 전체 읽기보다 커질 수 있다.
실무에서 자주 틀리는 것
머징을 막으면 모든 주문을 먼저 읽는다고 생각한다
다음 코드는 NO_MERGE를 전체 읽기의 보장으로 해석한 잘못된 실험이다. SQL 결과가 잘못된 것은 아니다.
select /*+ no_merge(v) */ count(*)
from (
select customer_id from qt06_orders
) v
where v.customer_id = 42;전체 읽기 대조군이 필요하다면 실험 목적을 접근 경로로 직접 표현한다. 이 코드는 운영 권장안이 아니라 비교용 기준이다.
select /*+ gather_plan_statistics full(o) */ count(*)
from qt06_orders o
where o.customer_id = 42;집계 결과 조건을 원본 행 조건으로 바꾼다
고객별 총액이 1,000을 넘는 고객을 구하면서 개별 주문 금액을 먼저 제한하면 의미가 달라진다.
select customer_id, sum(amount)
from qt06_orders
where amount > 1000
group by customer_id;집계 결과에 대한 조건은 HAVING에 둔다. 고객 번호처럼 그룹 자체를 선택하는 조건만 별도로 WHERE에 둔다.
select customer_id, sum(amount)
from qt06_orders
where customer_id = 42
group by customer_id
having sum(amount) > 1000;OR을 나누면서 겹치는 주문을 두 번 센다
다음 SQL은 양쪽 조건에 맞는 주문을 두 번 반환한다.
select order_id from qt06_orders where customer_id = 42
union all
select order_id from qt06_orders where promo_id = 42;둘째 분기에서 첫 분기에 포함된 행을 제외한다. 두 분기에 공통 조건이 있다면 함께 유지한다.
select order_id from qt06_orders where customer_id = 42
union all
select order_id from qt06_orders
where promo_id = 42
and lnnvl(customer_id = 42);중복 제외 조건에서 비회원 주문을 잃는다
다음 조건은 행사에 속한 비회원 주문까지 제외한다.
where promo_id = 42
and customer_id <> 42NULL도 통과시키는 조건으로 고친다. 이 표현은 비교값이 42로 확정된 경우 LNNVL을 쓰지 않는 대안이다.
where promo_id = 42
and (customer_id <> 42 or customer_id is null)한눈에 보기
| 관찰 대상 | 확인할 근거 | 잘못된 결론 | 다음 행동 |
|---|---|---|---|
| 뷰 머징 | 뷰 경계와 쿼리 블록의 변화 | VIEW가 없어지면 반드시 빨라진다 | 조건 위치와 읽기량을 비교한다 |
| 조건 이동 | access, filter와 실제 입력 행 | 바깥 조건은 항상 마지막에 적용된다 | 집계 전 적용 여부를 확인한다 |
| OR 확장 | 분기별 접근과 중복 제외 | UNION ALL만 붙이면 동치다 | 겹치는 행과 NULL을 검증한다 |
| 효과 측정 | 같은 결과와 커서별 읽기 증가량 | 계획 비용 감소가 실측 개선이다 | 실제 값과 입력 분포를 비교한다 |
SQL을 읽기 쉽게 나눈 구조와 옵티마이저가 실행하는 구조는 일치하지 않을 수 있다. 작성자는 결과 의미를 유지하면서 선택적인 조건을 분명하게 표현하고, 옵티마이저가 이미 수행한 변환은 실제 계획으로 확인한다. 다음 장에서는 검색 결과를 정렬하는 비용과 필요한 행 수만큼 처리하는 구조를 살펴본다.
연습 문제
- M0에는 VIEW가 남고 M1에는 VIEW가 없다. 두 실행 모두 고객 인덱스에서 11건을 찾고 논리 읽기가 같다. M1의 성능 개선을 주장할 수 있는지 설명하라.
- P0의 바깥 조건을 total_amount > 1000으로 바꾸었다. 이를 안쪽 WHERE amount > 1000으로 옮길 수 있는지 설명하고 올바른 위치를 제시하라.
- O1의 둘째 분기에서 LNNVL 조건을 제거한 경우와 customer_id <> 42로 바꾼 경우 각각의 건수와 금액 합계를 계산하라.
- O0가 520, O1이 142의 논리 읽기를 기록했다고 가정한다. 감소율을 계산하고, 이 결과만으로 수동 OR 확장이 최선이라고 결론 내릴 수 없는 이유를 두 가지 제시하라.
정답과 해설
- 읽기량 관점의 개선은 확인되지 않았다. M0에서도 고객 조건이 안쪽 접근에 반영되었을 가능성이 높다. 뷰 경계 제거는 변환이 일어났다는 근거이며 성능 개선의 직접 근거는 아니다. 다른 차이를 주장하려면 그 항목을 추가로 측정해야 한다.
- 옮길 수 없다. 총액 조건은 여러 주문을 합친 결과를 검사하고 개별 금액 조건은 합계에 참여할 행을 제거한다. 바깥 조건을 유지하거나 집계 뷰 안쪽에 HAVING SUM(amount) > 1000으로 표현한다.
- LNNVL을 제거하면 고객 분기 11건과 행사 분기 120건이 합쳐져 131건, 13,100이 된다. 부등호로 바꾸면 행사 분기에서 겹치는 11건과 비회원 10건이 제외되어 99건이 남는다. 고객 분기까지 합하면 110건, 11,000이다. 올바른 결과는 120건, 12,000이다.
- 감소율은 (520 - 142) / 520 × 100으로 약 72.7%다. 첫째, 기존 FULL과 NO_EXPAND를 제거한 기본 OR SQL과 아직 비교하지 않았다. 둘째, 선택도가 다른 고객이나 행사에서는 분기별 읽기의 합이 커질 수 있다. 실제 배포 후보는 결과 동치성과 여러 입력값의 측정을 함께 통과해야 한다.