테이블 랜덤 액세스 줄이기 - 커버링 인덱스와 컬럼 추가
이 장에서 배우는 것
앞 장에서 느린 SQL을 찾고 같은 조건으로 효과를 측정하는 절차를 살펴보았다. 이번에는 온라인 서점의 출고 대기 주문 화면을 대상으로, 인덱스를 읽은 뒤 테이블을 방문하는 횟수를 줄인다. 인덱스를 사용한다는 사실만으로 조회 비용이 작아지는 것은 아니다. 인덱스에서 찾은 후보가 많고 테이블에서 버리는 행이 많으면, 최종 결과가 몇 건뿐이어도 상당한 블록을 읽는다.
이 장의 실습은 Oracle 19c의 힙 테이블을 기준으로 한다. SQL*Plus에서 실행할 수 있는 SQL과 PL/SQL을 제공하며, 실행 통계를 수집한 계획을 출력한다. 본문에 제시한 논리 읽기 예시는 실제 측정값으로 오해하지 않도록 따로 표시한다. 독자의 환경에서 얻은 수치는 완성 코드가 출력하는 실행 통계로 확인한다.
- 인덱스 후보 행 수와 테이블 방문 후 반환 행 수를 구분한다.
- 인덱스에 필터 컬럼을 추가하여 불필요한 테이블 방문을 줄인다.
- 커버링 인덱스로 테이블 액세스를 생략하는 조건을 확인한다.
- 인덱스 구성 테이블과 클러스터의 배치 원리를 이번 사례와 연결한다.
- 실행계획의 실제 행 수와 논리 읽기를 함께 비교하고 변경 비용을 판단한다.
문제 상황
온라인 서점의 운영자는 고객 문의를 받으면 고객별 출고 대기 주문을 조회한다. 화면은 주문 번호와 결제 금액만 보여 주며, 고객 번호와 주문 상태를 검색 조건으로 사용한다. 오래된 주문을 포함하여 고객별 주문이 쌓이면서 화면 응답이 느려졌다. 운영자는 “보이는 주문은 열 건인데 왜 오래 걸리는가”라고 묻는다.
기존 인덱스는 고객 번호와 주문 번호 순서다. 고객의 주문을 찾고 주문 번호 순서로 반환하기에는 적합하지만, 주문 상태는 들어 있지 않다. 따라서 데이터베이스는 해당 고객의 주문을 인덱스에서 찾은 뒤 테이블을 방문하여 출고 대기 상태인지 확인한다. 이미 처리된 주문도 상태를 확인하려면 테이블 블록을 읽어야 한다.
실습 데이터는 주문 200,000건, 고객 1,000명으로 구성한다. 고객마다 주문이 200건이며, 그중 10건만 출고 대기 상태인 READY다. 나머지 190건은 DONE이다. 고객 42의 주문 번호는 42부터 시작하여 1,000씩 증가한다. 테이블은 주문 번호 순서로 적재하므로 한 고객의 주문은 적재 순서상 서로 떨어져 있다.
실제 화면에서는 바인드 변수를 사용하지만, 실습에서는 고객 번호 42를 고정한다. 고객별 선택도 차이와 바인드 관련 계획 변화를 배제하고 인덱스 구성만 비교하기 위해서다. 화면에 필요한 출력 컬럼도 두 개로 제한한다. 조회 컬럼이 늘어나면 커버링 여부가 달라진다는 점은 뒤에서 따로 확인한다.
인덱스 다음에 발생하는 테이블 방문
힙 테이블의 일반적인 보조 인덱스에는 키와 행 위치 정보인 ROWID가 저장된다. 인덱스에 없는 컬럼을 확인하려면 이 위치를 따라 테이블 블록을 읽는다. 이 과정을 테이블 랜덤 액세스(random table access)라고 부른다. 여기서 랜덤은 디스크를 매번 물리적으로 읽는다는 뜻이 아니라, 인덱스 순서로 얻은 행 위치가 테이블 블록의 연속 순서와 일치하지 않을 수 있다는 뜻이다.
대상 블록이 버퍼 캐시에 있으면 물리 읽기는 발생하지 않을 수 있다. 그래도 블록을 찾아 접근하는 논리 읽기와 CPU 작업은 남는다. 따라서 캐시가 충분하다는 이유로 테이블 방문 비용을 무시할 수 없다. 같은 블록을 여러 번 접근할 수도 있으므로 논리 읽기를 읽은 서로 다른 블록의 개수로 해석해서도 안 된다.
기존 인덱스에서 고객 42에 해당하는 후보는 200건이다. 상태 조건을 검사하려면 이 후보들의 테이블 행을 확인해야 한다. 그중 READY인 10건만 화면으로 나간다. 실행계획에서 인덱스 단계의 실제 출력 행 수가 200이고 테이블 단계의 출력 행 수가 10이라면, 두 수의 차이가 테이블에서 제거된 후보를 보여 준다.
후보 200건이 곧 논리 읽기 200회를 뜻하지는 않는다. 같은 블록의 재사용, ROWID 일괄 처리, 인덱스의 높이와 리프 블록 수 등이 영향을 준다. 클러스터링 팩터는 인덱스 순서와 테이블 배치의 관계를 추정하는 데 쓰이지만, 실행 때 방문한 블록 수를 그대로 기록한 값은 아니다. 이번 사례에서는 실제 행 수로 낭비의 위치를 찾고, 실행 통계의 Buffers로 비용 변화를 확인한다.
계획에 TABLE ACCESS BY INDEX ROWID BATCHED가 나타날 수도 있다. ROWID를 모아 블록 접근을 효율화하는 방식이므로 일반적인 TABLE ACCESS BY INDEX ROWID와 이름이 다르다고 해서 다른 문제로 볼 필요는 없다. 어느 경우든 상태를 검사하는 데 테이블 행이 필요한지가 핵심이다.
필터 컬럼 추가와 커버링 인덱스
필터를 먼저 검사하도록 만든다
첫 번째 변경은 기존 인덱스의 뒤에 주문 상태를 추가하는 것이다. 인덱스 구성을 고객 번호, 주문 번호, 주문 상태 순서로 바꾼다. 이제 고객의 인덱스 엔트리를 읽으면서 READY 여부를 검사할 수 있다. DONE인 후보는 테이블을 방문하기 전에 제거한다. 결제 금액은 아직 인덱스에 없으므로 남은 10건은 테이블에서 읽는다.
이 변경은 인덱스에서 읽어야 하는 고객 범위를 크게 줄이는 변경과 구분해야 한다. 조건이 없는 주문 번호 뒤에 주문 상태를 넣었으므로, 상태 조건은 이 실습의 범위 스캔에서 주로 인덱스 필터로 작동한다. 고객의 후보 200개를 살펴보는 일은 남지만 테이블에 넘기는 ROWID는 10개로 줄어든다. 실행계획의 인덱스 A-Rows가 10이라고 해서 리프에서 엔트리 10개만 검사했다고 결론 내리면 안 된다.
고객 번호, 주문 상태, 주문 번호 순서로 구성하면 두 등호 조건으로 읽을 범위를 더 좁힐 수 있다. 두 선행 컬럼이 고정되므로 이 조회의 주문 번호 정렬도 지원할 수 있다. 다만 기존 인덱스가 상태 조건 없는 고객별 주문 조회에도 쓰인다면 영향이 달라진다. 완성 코드에서는 테이블 방문 감소 효과를 분리하여 보기 위해 상태 컬럼을 뒤에 추가한다.
조회에 필요한 값을 모두 담는다
두 번째 변경은 결제 금액도 인덱스 뒤에 추가하는 것이다. 조건 검사, 정렬, 결과 반환에 필요한 컬럼이 모두 한 인덱스 안에 들어간다. 이처럼 특정 SQL이 요구하는 값을 인덱스만으로 제공하는 구성을 커버링 인덱스(covering index)라고 한다. 별도의 인덱스 종류라기보다 인덱스와 조회 SQL 사이의 관계를 나타낸다.
이번 조회에서는 테이블 액세스 연산이 사라지고 인덱스 범위 스캔만 남는 형태를 기대할 수 있다. 테이블에서 읽던 결제 금액을 인덱스에서 바로 반환하기 때문이다. 반면 배송 메모를 출력 컬럼에 추가하면 같은 인덱스로는 조회를 모두 처리할 수 없다. 커버링 여부는 테이블 전체가 아니라 SQL마다 판단한다.
Oracle의 일반 B-tree 인덱스에서 뒤에 추가한 컬럼도 인덱스 키의 일부다. 별도 저장 공간에 무료로 붙는 값이 아니다. 인덱스가 넓어지면 리프 블록 수와 캐시 점유량이 늘 수 있다. INSERT와 DELETE의 유지 비용도 늘며, 결제 금액을 수정할 때는 해당 인덱스 엔트리도 갱신해야 한다. 테이블 방문 열 번을 없애기 위해 큰 문자열 여러 개를 추가하는 선택은 전체 업무에서 손해가 될 수 있다.
| 구성 | 상태 검사 위치 | 테이블 확인 대상 | 남는 작업 |
|---|---|---|---|
| 고객, 주문 | 테이블 | 후보 200건 | 상태 검사와 금액 조회 |
| 고객, 주문, 상태 | 인덱스 | 통과한 10건 | 금액 조회 |
| 고객, 주문, 상태, 금액 | 인덱스 | 없음 | 인덱스에서 결과 반환 |
테이블 배치까지 바꾸는 선택
인덱스 구성 테이블(index-organized table, IOT)은 기본 키를 기준으로 구성한 인덱스 구조에 행 데이터를 저장한다. 기본 키 검색에서 별도의 힙 테이블로 이동하는 과정을 줄일 수 있다. 모든 보조 인덱스를 커버링으로 만드는 것과는 다른 접근이다. 테이블의 저장 구조와 기본 키 접근 특성을 함께 바꾼다.
그렇다고 IOT의 모든 조회에서 추가 접근이 없어지는 것은 아니다. 보조 인덱스 조회에는 기본 키를 이용한 행 탐색이 필요할 수 있고, 행 일부를 오버플로 영역에 저장했다면 그 영역에 대한 접근도 고려해야 한다. 고객별 조회 한 개가 느리다는 이유만으로 주문 테이블 전체를 IOT로 전환하는 것은 변경 범위가 크다.
Oracle의 테이블 클러스터는 같은 클러스터 키를 가진 행을 가까운 데이터 블록에 저장하도록 구성하는 기능이다. 인덱스 클러스터를 고객 번호 기준으로 구성한다면 같은 고객의 주문을 읽을 때 블록 재사용을 높일 여지가 있다. 다만 고객별 데이터 크기의 편차, 증가량, 다른 접근 경로까지 함께 검토해야 한다. 클러스터는 여러 서버를 묶는 구성과도 다른 개념이다.
이번 사례에서는 기존 힙 테이블을 유지하고 인덱스만 바꾸는 방법을 우선 비교한다. 저장 구조 변경은 테이블 전체의 접근 패턴이 그 배치와 잘 맞는 경우에 검토한다. MySQL 8의 InnoDB는 기본 키를 중심으로 행을 저장하므로 Oracle 힙 테이블의 ROWID 접근을 그대로 대입하면 안 된다.
| 항목 | Oracle 19c | MySQL 8 InnoDB |
|---|---|---|
| 일반적인 행 저장 | 힙 테이블과 기본 키 인덱스가 별개다 | 클러스터형 인덱스의 리프에 행을 저장한다 |
| 보조 인덱스 다음 접근 | 힙 테이블에서는 ROWID로 행을 찾는다 | 보조 인덱스에 저장된 기본 키로 행을 찾는다 |
| 인덱스 조건 검사 | 인덱스 필터로 테이블에 보낼 후보를 줄일 수 있다 | 적용 가능한 경우 인덱스 조건 푸시다운으로 행 조회를 줄인다 |
| 커버링 판단 | 해당 인덱스가 필요한 값을 제공하는지 확인한다 | 보조 인덱스에 포함된 기본 키 컬럼도 고려한다 |
| 실행 통계 | DBMS_XPLAN에서 A-Rows와 Buffers를 확인한다 | 8.0.18 이상에서 EXPLAIN ANALYZE로 실제 실행을 확인하되 Oracle의 Buffers와 같은 수치가 직접 제공되지는 않는다 |
InnoDB의 커버링 접근도 일관된 읽기를 위한 가시성 확인 등의 이유로 클러스터형 인덱스를 참조할 수 있다. 따라서 실행계획의 커버링 표시를 모든 내부 접근이 없다는 의미로 확대하지 않는다. 사실 관계를 더 확인하려면 Oracle의 인덱스와 IOT 설명, Oracle의 DBMS_XPLAN 설명, MySQL의 InnoDB 인덱스 설명, MySQL의 인덱스 조건 푸시다운 설명을 참고한다.
완성 코드
다음 파일은 전용 실습 스키마에서 한 번 실행한다. 같은 이름의 테이블이나 인덱스가 없는 상태를 전제로 하며, 기존 객체를 자동으로 삭제하지 않는다. 테이블 생성 권한과 테이블스페이스 할당량이 필요하다. 실행계획을 읽으려면 SYS.V_$SQL, SYS.V_$SQL_PLAN, SYS.V_$SQL_PLAN_STATISTICS_ALL, SYS.V_$SESSION에 대한 조회 권한과 DBMS_XPLAN 실행 권한도 필요하다. 권한은 환경 관리자가 준비한다.
macOS/Linux는 클라이언트 실행 환경이다. 접속할 Oracle 19c 데이터베이스와 SQL*Plus를 준비한다. 스크립트는 컴파일 대상 저장 프로시저를 만들지 않고 익명 PL/SQL 블록을 실행한다. 각 조회는 두 번 끝까지 가져오며 두 번째 실행의 계획 통계를 출력한다. 이 답변에서 데이터베이스 실행을 수행한 것은 아니므로, 아래 수치 예시를 검증된 실측값으로 사용하지 않는다.
table_access.sql
SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 0
SET LINESIZE 220
SET TRIMSPOOL ON
SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WRAPPED
WHENEVER OSERROR EXIT FAILURE
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
-- [01] 실습 테이블
CREATE TABLE lab_orders (
order_id NUMBER(10) NOT NULL,
customer_id NUMBER(10) NOT NULL,
order_status VARCHAR2(5) NOT NULL,
total_amount NUMBER(12) NOT NULL,
padding VARCHAR2(200) NOT NULL,
CONSTRAINT pk_lab_orders PRIMARY KEY (order_id)
);
-- [02] 고객마다 200건, READY 10건을 생성한다.
INSERT INTO lab_orders (
order_id, customer_id, order_status, total_amount, padding
)
SELECT LEVEL,
MOD(LEVEL - 1, 1000) + 1,
CASE
WHEN MOD(TRUNC((LEVEL - 1) / 1000), 20) = 0
THEN 'READY'
ELSE 'DONE'
END,
15000 + MOD(LEVEL, 10) * 1000,
RPAD('x', 200, 'x')
FROM dual
CONNECT BY LEVEL <= 200000;
COMMIT;
-- [03] 테이블과 기존 기본 키 인덱스의 통계
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => USER,
tabname => 'LAB_ORDERS',
estimate_percent => 100,
method_opt => 'FOR ALL COLUMNS SIZE 1',
cascade => TRUE
);
END;
/
DECLARE
-- [04] 동적 SELECT를 끝까지 가져올 커서
l_cursor SYS_REFCURSOR;
-- [05] 동일한 조회 및 통계 출력 절차
PROCEDURE run_case(
p_label IN VARCHAR2,
p_index IN VARCHAR2,
p_tag IN VARCHAR2
) IS
l_sql VARCHAR2(1000);
l_order_id NUMBER;
l_amount NUMBER;
l_rows PLS_INTEGER;
l_id_sum NUMBER;
l_amount_sum NUMBER;
l_sql_id VARCHAR2(13);
l_child NUMBER;
BEGIN
l_sql :=
'SELECT /*+ gather_plan_statistics index(o ' ||
p_index || ') */ /* ' || p_tag || ' */ ' ||
'o.order_id, o.total_amount ' ||
'FROM lab_orders o ' ||
'WHERE o.customer_id = 42 ' ||
'AND o.order_status = ''READY'' ' ||
'ORDER BY o.order_id';
-- [06] 첫 실행 후 같은 SQL을 다시 끝까지 가져온다.
FOR attempt IN 1..2 LOOP
l_rows := 0;
l_id_sum := 0;
l_amount_sum := 0;
OPEN l_cursor FOR l_sql;
LOOP
FETCH l_cursor INTO l_order_id, l_amount;
EXIT WHEN l_cursor%NOTFOUND;
l_rows := l_rows + 1;
l_id_sum := l_id_sum + l_order_id;
l_amount_sum := l_amount_sum + l_amount;
END LOOP;
CLOSE l_cursor;
END LOOP;
DBMS_OUTPUT.PUT_LINE(
p_label ||
' rows=' || TO_CHAR(l_rows, 'FM999999990') ||
' id_sum=' || TO_CHAR(l_id_sum, 'FM9999999990') ||
' amount_sum=' || TO_CHAR(l_amount_sum, 'FM9999999990')
);
-- [07] 방금 실행한 SQL의 자식 커서를 찾는다.
SELECT sql_id, child_number
INTO l_sql_id, l_child
FROM (
SELECT sql_id, child_number
FROM v$sql
WHERE sql_text = l_sql
AND executions > 0
ORDER BY last_active_time DESC, child_number DESC
)
WHERE ROWNUM = 1;
-- [08] 추정 계획이 아닌 마지막 실행 통계를 출력한다.
FOR r IN (
SELECT plan_table_output
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
l_sql_id,
l_child,
'ALLSTATS LAST +PREDICATE'
)
)
) LOOP
DBMS_OUTPUT.PUT_LINE(r.plan_table_output);
END LOOP;
END run_case;
-- [09] 각 실험 인덱스의 통계 수집
PROCEDURE index_stats(p_name IN VARCHAR2) IS
BEGIN
DBMS_STATS.GATHER_INDEX_STATS(
ownname => USER,
indname => p_name,
estimate_percent => 100
);
END index_stats;
BEGIN
-- [10] 기존 구성
EXECUTE IMMEDIATE
'CREATE INDEX ix_orders_c_o ' ||
'ON lab_orders(customer_id, order_id)';
index_stats('IX_ORDERS_C_O');
run_case('BASE', 'ix_orders_c_o', 'book_ra_base');
-- [11] 상태를 인덱스에서 검사한다.
EXECUTE IMMEDIATE 'DROP INDEX ix_orders_c_o';
EXECUTE IMMEDIATE
'CREATE INDEX ix_orders_c_o_s ' ||
'ON lab_orders(customer_id, order_id, order_status)';
index_stats('IX_ORDERS_C_O_S');
run_case('FILTER', 'ix_orders_c_o_s', 'book_ra_filter');
-- [12] 금액까지 인덱스에서 반환한다.
EXECUTE IMMEDIATE 'DROP INDEX ix_orders_c_o_s';
EXECUTE IMMEDIATE
'CREATE INDEX ix_orders_cover ' ||
'ON lab_orders(customer_id, order_id, order_status, total_amount)';
index_stats('IX_ORDERS_COVER');
run_case('COVER', 'ix_orders_cover', 'book_ra_cover');
END;
/
EXIT SUCCESS
줄별 해설
[01]은 모든 조회 관련 컬럼을 NOT NULL로 정의한다. Oracle B-tree 인덱스는 모든 키 컬럼이 NULL인 행을 저장하지 않으므로, 인덱스만 읽는 조회를 일반화할 때는 NULL과 행 누락 가능성도 검토해야 한다. 이번 실습은 그 변수를 제거한다. padding은 테이블 행의 폭을 늘리는 실습용 컬럼이며 조회 결과에 포함하지 않는다.
[02]의 고객 번호 계산은 1부터 1,000까지 반복된다. 상태는 고객 번호가 아니라 1,000건 단위의 적재 묶음으로 결정한다. 고객 번호로 상태를 결정하면 특정 고객의 모든 주문이 같은 상태가 되어 의도한 200 대 10의 비교가 성립하지 않는다. 여기서는 200개 묶음 중 20개마다 하나가 READY이므로 고객마다 10건이 남는다.
[03]은 데이터 적재 후 통계를 수집한다. 히스토그램을 만들지 않는 설정은 실습의 계획 변수를 줄이기 위한 것이다. 운영 환경에서도 히스토그램을 없애야 한다는 의미는 아니다. [09]는 인덱스를 생성할 때마다 해당 인덱스 통계를 별도로 수집한다.
[04]와 [05]는 세 가지 인덱스를 같은 조회문 구조로 비교하기 위한 공통 절차다. 인덱스 힌트는 비교 대상을 지정하고 실행 통계 수집 힌트는 실제 작업량을 남긴다. 힌트에 적힌 이름은 모두 코드 안에서 정한 상수다. 사용자 입력을 문자열로 연결하는 동적 SQL 작성 예제로 사용해서는 안 된다.
[06]에서 마지막 행까지 가져오는 것이 중요하다. 커서를 열고 일부 행만 읽은 뒤 닫으면 전체 결과를 처리한 통계와 비교할 수 없다. 두 번째 실행을 사용하는 이유는 최초 접근의 영향을 줄이기 위해서다. 인덱스 생성 자체도 캐시에 영향을 주므로 이것을 엄밀한 콜드 캐시 실험이라고 부르지는 않는다. 여기서는 주로 논리 읽기를 비교한다.
출력하는 행 수와 두 합계는 세 실행의 결과가 같다는 것을 빠르게 확인하는 장치다. 다만 합계가 같아도 서로 다른 결과 집합일 수 있으므로 일반적인 회귀 검증을 대체하지는 않는다. 이 실습에서는 데이터 생성 규칙으로 정답이 정해져 있어 보조 확인 수단으로 사용한다.
[07]은 실행한 SQL 문자열과 일치하는 커서를 찾아 식별한다. DBMS_OUTPUT이나 다른 SQL의 실행 때문에 마지막 커서가 달라지는 상황을 피하려고 SQL 식별자를 명시한다. 이 선택 방식은 다른 세션이 동일한 실습을 동시에 실행하지 않는 전용 실습 환경을 전제로 한다. [08]의 LAST는 여러 실행의 누계 대신 마지막 실행의 행 소스 통계를 표시하도록 요청한다.
[10]부터 [12]까지는 비교용 보조 인덱스를 하나씩 교체한다. 주문 번호 기본 키 인덱스는 그대로 존재한다. 인덱스 DDL에는 암묵적 커밋이 수반되므로 뒤에서 오류가 나더라도 앞서 만든 객체와 적재 데이터가 모두 롤백되지는 않는다. 재실행이 필요하면 실습 객체의 상태를 확인한 뒤 정리해야 한다.
실행 결과
파일을 저장한 디렉터리에서 다음 명령을 실행한다. 주소와 서비스 이름은 준비한 데이터베이스 값으로 바꾼다. 비밀번호를 명령행에 적지 않으면 SQL*Plus가 입력을 요청한다.
sqlplus -L lab_user@//127.0.0.1:1521/ORCLPDB1 @table_access.sql정상 실행 시 아래 세 요약 줄이 각각의 실행계획 앞에 출력된다. 다음은 실행계획 부분을 제외한 예상 출력이며, 숫자는 데이터 생성 코드로 결정된다. 고객 42의 READY 주문 번호는 42, 20042, 40042 순서로 증가하여 180042까지 열 개다. 모든 해당 주문의 금액은 17,000이다.
BASE rows=10 id_sum=900420 amount_sum=170000
FILTER rows=10 id_sum=900420 amount_sum=170000
COVER rows=10 id_sum=900420 amount_sum=170000실제 출력에는 SQL 식별자, 계획 해시, 실행 시간, 통계와 조건절이 추가된다. 이 값들은 환경에 따라 달라지므로 고정된 예상 출력으로 제시하지 않는다. 다음은 확인할 계획 형태를 설명하기 위한 축약도다. BATCHED 여부나 추가 표시 열은 달라질 수 있다.
BASE
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID LAB_ORDERS A-Rows: 10
INDEX RANGE SCAN IX_ORDERS_C_O A-Rows: 200
FILTER
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID LAB_ORDERS A-Rows: 10
INDEX RANGE SCAN IX_ORDERS_C_O_S A-Rows: 10
COVER
SELECT STATEMENT
INDEX RANGE SCAN IX_ORDERS_COVER A-Rows: 10BASE의 Predicate Information에서는 고객 번호가 인덱스의 접근 조건이고 주문 상태가 테이블의 필터 조건인지 확인한다. FILTER에서는 주문 상태 필터가 인덱스 단계로 이동했는지 본다. COVER에서는 테이블 액세스 연산이 없어졌는지 확인한다. 계획 형태가 예상과 다르면 먼저 힌트 적용 여부, 인덱스 구성과 통계 수집 상태를 점검한다.
다음 표의 Buffers는 읽는 방법을 설명하기 위한 가정값이다. Oracle 19c에서 실제 실행해 얻은 수치가 아니며 재현 목표도 아니다. 실제 비교표에는 완성 코드가 출력한 각 계획의 최상위 Buffers를 옮긴다. 상위 연산의 통계에는 하위 작업이 포함될 수 있으므로 모든 행의 Buffers를 더하지 않는다.
| 단계 | 최종 행 수 | 전체 Buffers 가정값 | 직전 단계 대비 감소 |
|---|---|---|---|
| BASE | 10 | 204 | 비교 시작점 |
| FILTER | 10 | 14 | 190, 약 93.1% |
| COVER | 10 | 4 | 10, 약 71.4% |
가정값에서 가장 큰 감소는 상태 필터를 인덱스로 옮길 때 발생한다. 결과로 남지 않을 주문의 테이블 방문을 먼저 제거했기 때문이다. 커버링 구성은 남은 열 건의 금액 조회까지 없앤다. 다만 인덱스 폭 증가로 리프 블록 읽기가 늘어날 수 있으므로 실제 Buffers가 이 비율로 줄어든다고 보장할 수 없다.
논리 읽기가 줄었다고 화면 응답 시간이 같은 비율로 줄어드는 것도 아니다. 네트워크 왕복, 애플리케이션 처리, 동시 부하가 전체 응답에 포함된다. 이 실습은 특정 SQL 내부의 읽기 작업 감소를 확인한다. 운영 반영 판단에서는 대표 고객 여러 명과 반복 실행을 사용하고, 조회뿐 아니라 주문 입력과 상태 변경의 비용도 측정한다.
실무에서 자주 틀리는 것
뒤에 추가한 필터 컬럼이 읽을 범위도 줄인다고 생각한다
다음 인덱스 자체가 잘못된 것은 아니다. 다만 이 순서만 보고 READY 범위만 읽는다고 판단하는 것이 잘못이다. 주문 번호에 조건이 없으므로 고객 범위 안에서 상태를 검사하는 방식이 될 수 있다.
-- 잘못된 목적 설명: READY 구간만 좁게 읽기 위한 구성
CREATE INDEX ix_range_demo
ON lab_orders(customer_id, order_id, order_status);고객과 상태로 범위를 제한하는 것이 목적이라면 다음 순서를 검토한다. 다른 SQL에서 기존 정렬과 접근 경로를 어떻게 사용하는지도 함께 확인한다. 아래 코드는 대안을 보여 주는 별도 예시이며 완성 코드 뒤에 이어 실행할 필요는 없다.
-- 목적에 맞춘 대안: 두 등호 조건을 선두에 배치한다.
CREATE INDEX ix_range_demo
ON lab_orders(customer_id, order_status, order_id);커버링 인덱스를 만들고 모든 컬럼을 요청한다
화면이 두 컬럼만 사용하는데 모든 컬럼을 요청하면 padding까지 읽어야 하므로 이번 커버링 구성이 성립하지 않는다. 사용하지 않는 컬럼도 반환 대상에 들어가면 접근 경로에 영향을 준다.
-- 불필요한 컬럼까지 요청한다.
SELECT o.*
FROM lab_orders o
WHERE o.customer_id = 42
AND o.order_status = 'READY'
ORDER BY o.order_id;
-- 화면에 필요한 컬럼을 명시한다.
SELECT o.order_id, o.total_amount
FROM lab_orders o
WHERE o.customer_id = 42
AND o.order_status = 'READY'
ORDER BY o.order_id;예상 계획을 실제 읽기 통계로 해석한다
EXPLAIN PLAN은 조회를 끝까지 실행한 결과를 제공하지 않는다. 예상 비용이 감소했다는 사실만으로 논리 읽기 감소를 확정하면 안 된다. 다음 첫 코드는 계획 검토에는 쓸 수 있지만 이 장의 효과 측정을 충족하지 못한다.
-- 실제 논리 읽기를 확인할 수 없는 방식
EXPLAIN PLAN FOR
SELECT order_id, total_amount
FROM lab_orders
WHERE customer_id = 42
AND order_status = 'READY';
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY);실제 실행 통계를 수집하고 전체 결과를 가져온 뒤 해당 커서를 조회한다. 다음 짧은 예시는 SQL*Plus에서 두 SELECT 사이에 다른 SQL이 실행되지 않는 조건으로 사용한다. 완성 코드는 커서 식별자를 찾아 명시하는 방식으로 구성했다.
SET SERVEROUTPUT OFF
SELECT /*+ gather_plan_statistics */
order_id, total_amount
FROM lab_orders
WHERE customer_id = 42
AND order_status = 'READY'
ORDER BY order_id;
SELECT plan_table_output
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST +PREDICATE')
);읽기를 줄이려고 넓은 컬럼을 모두 추가한다
조회하지 않는 문자열을 인덱스에 넣으면 저장 공간과 갱신 비용만 늘어난다. 여러 화면을 한 인덱스로 모두 처리하려고 컬럼을 계속 추가하기 전에 각 화면의 실행 빈도와 반환 행 수를 비교한다.
-- 이 화면에서 사용하지 않는 padding까지 포함한다.
CREATE INDEX ix_width_demo
ON lab_orders(
customer_id, order_id, order_status, total_amount, padding
);
-- 이 화면에 필요한 범위로 제한한 대안
CREATE INDEX ix_width_demo
ON lab_orders(
customer_id, order_id, order_status, total_amount
);두 CREATE 문은 동시에 실행하는 코드가 아니라 교체 전후의 대안이다. 운영 인덱스 변경 시에는 기존 인덱스와 선두 컬럼이 겹친다는 이유만으로 즉시 삭제하지 않는다. 다른 SQL이 쓰는 접근 경로와 제약조건 지원 여부를 먼저 확인한다.
한눈에 보기
| 관찰 | 해석 | 검토할 변경 | 확인할 비용 |
|---|---|---|---|
| 인덱스 200건, 테이블 출력 10건 | 테이블에서 후보를 많이 버린다 | 상태 컬럼을 인덱스에 추가한다 | 인덱스 폭과 상태 변경 비용 |
| 인덱스 10건, 테이블 출력 10건 | 필터는 이동했지만 출력값이 부족하다 | 금액까지 포함할지 판단한다 | 금액 수정과 저장 공간 |
| 테이블 액세스 연산이 없다 | 이 조회가 인덱스로 처리된다 | 실제 Buffers와 결과를 확인한다 | 리프 블록 수와 다른 업무 영향 |
| 필터 이동 후에도 인덱스 읽기가 많다 | 후보 범위 자체가 넓을 수 있다 | 선두 컬럼 순서를 재검토한다 | 다른 조회의 정렬과 범위 탐색 |
| 특정 키 조회가 업무 전반을 지배한다 | 행 배치 변경의 여지가 있다 | IOT나 클러스터를 검토한다 | 다른 접근 경로와 운영 복잡도 |
이번 사례는 한 테이블에서 불필요한 방문을 줄이는 문제였다. 다음 장에서는 한쪽 입력의 행마다 다른 테이블을 반복 방문하는 조인을 다룬다. 이때도 실제로 전달된 행 수와 반복된 블록 접근을 함께 보는 습관이 판단의 출발점이 된다.
연습 문제
- BASE에서 인덱스 A-Rows가 200, 테이블 A-Rows가 10이다. 테이블 논리 읽기가 정확히 200이라고 말할 수 없는 이유를 두 가지 이상 설명하라.
- FILTER에서 인덱스 A-Rows가 10으로 줄었다. 인덱스 엔트리도 열 개만 검사했다고 판단할 수 있는가. 컬럼 순서와 접근 조건을 연결하여 설명하라.
- 화면 요구사항에 padding 반환이 추가되었다. 기존 커버링 인덱스가 유지되는지 판단하고, 인덱스에 padding을 추가하기 전에 확인할 항목을 두 가지 제시하라.
- 실측한 전체 Buffers가 BASE 230, FILTER 22, COVER 7이다. BASE 대비 두 변경의 감소율을 구하고, 이 값만으로 운영에 반영할 인덱스를 결정할 수 있는지 설명하라.
정답과 해설
- 행 수와 블록 접근 수는 다른 단위다. 여러 행이 같은 테이블 블록에 있을 수 있고, 같은 블록을 반복 접근할 수도 있다. ROWID 일괄 처리 방식도 영향을 준다. 전체 Buffers에는 인덱스 읽기도 포함된다. 실제 논리 읽기는 실행 통계로 확인해야 한다.
- 판단할 수 없다. A-Rows는 해당 연산이 상위로 전달한 행 수다. 고객 번호, 주문 번호, 상태 순서에서는 주문 번호 조건이 없으므로 고객 후보 범위를 읽으면서 상태를 검사할 수 있다. 열 건은 필터 통과 수이며 검사한 엔트리 수와 같다는 보장은 없다.
- padding이 인덱스에 없으므로 기존 구성은 변경된 조회를 커버하지 못한다. 추가 전에는 문자열의 실제 평균 길이와 인덱스 크기 증가량을 확인한다. 해당 화면의 빈도, padding 변경 빈도, 저장 공간과 캐시 부담도 비교한다. 결과가 열 건뿐이라면 테이블 방문을 허용하는 편이 나을 수 있다.
- FILTER의 감소율은 (230 - 22) / 230 × 100으로 약 90.4%다. COVER는 (230 - 7) / 230 × 100으로 약 97.0%다. COVER가 이 조회의 읽기를 더 줄이지만 입력과 갱신 비용까지 평가한 결과는 아니다. 대표 부하에서의 응답 시간, 인덱스 크기, 주문 상태와 금액 변경 비용을 함께 확인한 뒤 선택한다.