테이블 엑세스 최소화

Last modified: 2026-02-08 21:46:41 +0900 View History

인덱스 ROWID

SELECT *
FROM TBL
WHERE 지역 = '서울'

Execution Plan
-----------------------------------------------------
0   SELECT STATEMENT Optimizer=ALL_ROWS
1   0   TABLE ACCESS BY INDEX ROWID OF 'TBL' (TABLE)
2   1       INDEX RANGE SCAN OF 'TBL_지역_IDX' (INDEX)
Index
Index

메인 메모리 DB(MMDB)

I/O 메커니즘 복습

Index

인덱스 클러스터링 팩터

Index
인덱스 클러스터링 팩터가 좋은 예시
Index
인덱스 클러스터링 팩터가 나쁜 예시

인덱스 클러스터링 팩터 효과

Index

인덱스 손익분기점

Img

인덱스 손익분기점과 버퍼캐시 히트율

온라인 프로그램 튜닝 vs 배치 프로그램 튜닝

-- 실명확인번호로 조회한 특정 고객의 1년 이내 변경 이력 중 전월 말일 데이터 출력
SELECT C.고객번호, C.고객명, H.전화번호, H.주소, H.상태코드, H.변경일시
FROM 고객 C, 고객변경이력 H
WHERE C.실명확인번호 = :rmno
AND H.고객번호 = C.고객번호
AND H.변경일시 = (SELECT MAX(변경일시)
                FROM 고객변경이력 M
                WHERE 고객번호 = C.고객번호
                AND 변경일시 >= TRUNC(ADD_MONTH(SYSDATE, -12), 'mm')
                AND 변경일시 <= TRUNC(SYSDATE, 'mm'));

Execution Plan
-------------------------------------------------------------------------------------------
0   SELECT STATEMENT Optimizer=ALL_ROWS (Cost=...)
1   0   NESTED LOOPS
2   1       NESTED LOOPS (Cost=...)
3   2           NESTED LOOPS (Cost=...)
4   3               TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) (Cost=...)
5   4                   INDEX (RANGE SCAN) OF '고객_X01' (INDEX) (Cost=...)
6   3               VIEW PUSHED PREDICATE OF 'SYS.VW_SQ_1' (VIEW) (Cost=...)
7   6                   SORT (AGGREGATE) (Cost=...)
8   7                       FIRST ROW (Cost=...)
9   8                           INDEX (RANGE SCAN (MIN/MAX)) OF '고객변경이력_PK' (Cost=...)
10  2               INDEX (UNIQUE SCAN) OF '고객변경이력_PK' (INDEX(UNIQUE)) (Cost=...)
11  1           TABLE ACCESS (BY INDEX ROWID) OF '고객변경이력' (TABLE) (Cost=...)


-- 고객구분코드가 'A001'인 고객의 최근 1년 이내 변경 이력 중 전월 말일 데이터를 읽어 고객_임시 테이블에 입력
-- 전체 300만명 중 조건을 만족하는 고객이 100만명일 때, 첫 쿼리에서 조건절만 바꿔 같은 방식으로 수행하면 성능이 좋지 않음
INSERT INTO 고객_임시
SELECT C.고객번호, C.고객명, H.전화번호, H.주소, H.상태코드, H.변경일시
FROM 고객 C, 고객변경이력 H
WHERE C.고객구분코드 = 'A001'
AND H.고객번호 = C.고객번호
AND H.변경일시 = (SELECT MAX(변경일시)
                FROM 고객변경이력 M
                WHERE 고객번호 = C.고객번호
                AND 변경일시 >= TRUNC(ADD_MONTH(SYSDATE, -12), 'mm')
                AND 변경일시 <= TRUNC(SYSDATE, 'mm'));

-- Full Scan + 해시 조인으로 변경
-- 조건절에 해당하지 않는 고객 데이터, 1년을 초과한 이력 데이터까지 읽는 비효율이 있지만, 수행속도는 훨씬 빠름
INSERT INTO 고객_임시
SELECT /*+ FULL(C) FULL(H) INDEX_FFS(M.고객변경이력)
            ORDERD NO_MERGE(M) USE_HASH(M) USE_HASH(H) */
    C.고객번호, C.고객명, H.전화번호, H.주소, H.상태코드, H.변경일시
FROM 고객 C
    ,(SELECT 고객번호, MAX(변경일시) 최종변경일시
      FROM 고객변경이력
      WHERE 변경일시 >= TRUNC(ADD_MONTH(SYSDATE, -12), 'mm')
      AND 변경일시 <= TRUNC(SYSDATE, 'mm')
      GROUP BY 고객번호)  M
    , 고객변경이력 H
WHERE C.고객구분코드 = 'A001'
AND M.고객번호 = C.고객번호
AND H.고객번호 = M.고객번호
AND H.변경일시 = M.최종변경일시


AND H.고객번호 = C.고객번호
AND H.변경일시 = (SELECT MAX(변경일시)
                FROM 고객변경이력 M
                WHERE 고객번호 = C.고객번호
                AND 변경일시 >= TRUNC(ADD_MONTH(SYSDATE, -12), 'mm')
                AND 변경일시 <= TRUNC(SYSDATE, 'mm'));

Execution Plan
-------------------------------------------------------------------------------------
0   INSERT STATEMENT Optimizer=ALL_ROWS (Cost=...)
1   0   LOAD TABLE CONVENTIONAL OF '고객_임시'
2   1       HASH JOIN (Cost=...)
3   2           HASH JOIN (Cost=...)
4   3               TABLE ACCESS (FULL) OF '고객' TABLE (Cost=...)
5   3               VIEW (Cost=...)
6   5                   SORT (GROUP BY) (Cost=...)
7   6                       FILTER
8   7                           INDEX (FAST FULL SCAN) OF '고객변경이력_PK' (Cost=...)
9   2           TABLE ACCESS (FULL) OF '고객변경이력' (TABLE) (Cost=...)

-- 고객변경이력 테이블을 두 번 읽는 비효율 제거
INSERT INTO 고객_임시
SELECT 고객번호, 고객명, 전화번호, 주소, 상태코드, 변경일시
FROM (SELECT /*+ FULL(C), FULL(H), LEADING(C) USE_HASH(H) */
        C.고객번호, C.고객명, H.전화번호, H.주소, H.상태코드, H.변경일시
        , RANK() OVER (PARTITION BY H.고객번호, ORDER BY H.변경일시 DESC) NO
      FROM 고객 C, 고객변경이력 H
      WHERE C.고객구분코드 = 'A001'
      AND H.변경일시 >= TRUNC(ADD_MONTHS(SYSDATE, -12), 'mm')
      AND H.변경일시 <= TRUNC(SYSDATE, 'mm')
      AND H.고객번호 = C.고객번호)
WHERE NO = 1

Execution Plan
-----------------------------------------------------------------------------------
0   INSERT STATEMENT Optimizer=ALL_ROWS (Cost=...)
1   0   LOAD TABLE COVENTIONAL OF '고객_임시'
2   1       VIEW (Cost=...)
3   2           WINDOW (SORT PUSHED RANK) (Cost=...)
4   3               FILTER
5   4                   HASH JOIN (Cost=...)
6   5                       TABLE ACCESS (FULL) OF '고객' (TABLE) (Cost=...)
7   5                       TABLE ACCESS (FULL) OF '고객변경이력' (TABLE) (Cost=...)

인덱스 컬럼 추가

SELECT /*+ INDEX(EMP, EMP_X01) */
FROM EMP
WHERE DEPT_NO = 30 AND SAL >= 2000
Img
사원 한 명을 찾기 위해 테이블을 여섯번 엑세스
Img
EMP_X01에 SAL 추가

인덱스만 읽고 처리

-- 부서번호 단일 컬럼으로 구성된 인덱스를 사용하는 경우
SELECT 부서번호, SUM(수량)
FROM TBL
WHERE 부서번호 LIKE '12%'
GROUP BY 부서번호

Include 인덱스

CREATE INDEX EMP_X01 ON EMP (DEPTNO) INCLUDE (SAL)
CREATE INDEX EMP_X02 ON EMP (DEPTNO, SAL)

-- EMP_X01, EMP_X02 둘 다 Covered 인덱스 이므로 테이블 랜덤 엑세스 생략 가능
SELECT SAL FROM EMP WHERE DEPTNO = 20

-- 테이블 랜덤 엑세스 측면에서는 동일 
-- 불필요한 테이블 엑세스가 발생하지 않음
-- 인덱스 스캔량은 EMP_X02 인덱스가 더 적음
-- SAL 컬럼도 인덱스 엑세스 조건으로 사용하기 때문
SELECT * FROM EMP WHERE DEPTNO = 20 AND SAL >= 2000
SELECT * FROM EMP WHERE DEPTNO = 20 AND SAL <= 3000
SELECT * FROM EMP WHERE DEPTNO = 20 AND SAL BETWEEN 2000 AND 3000

-- EMP_X02는 소트 연산 생략 가능하지만 EMP_X01은 불가
SELECT * FROM EMP WHERE DEPTNO = 20 ORDER BY SAL

인덱스 구조 테이블

Img
-- 테이블을 인덱스 구조로 만들기
CREATE TABLE INDEX_ORG_T (A NUMBER, B VARCHAR(10), CONSTRAINT INDEX_ORG_T_PK PRIMARY KEY (A))
ORGANIZATION INDEX;

-- 일반 테이블은 '힙 구조 테이블'이라 하는데, 아래처럼 옵션 명시 가능
CREATE TABLE INDEX_ORG_T (A NUMBER, B VARCHAR(10), CONSTRAINT INDEX_ORG_T_PK PRIMARY KEY (A))
ORGANIZATION HEAP;

클러스터 테이블

인덱스 클러스터 테이블

Img
-- 클러스터 생성
CREATE CLUSTER C_DEPT# (DEPTNO NUMBER(2)) INDEX;

-- 클러스터에 테이블을 담기 전에 반드시 클러스터 인덱스를 정의
-- 데이터 검색 용도 뿐만 아니라 데이터가 저장될 위치를 찾을 때도 사용하기 때문
CREATE INDEX C_DEPT#_IDX ON CLUSTER C_DEPT#;

-- 클러스터 테이블 생성
CREATE TABLE DEPT (
    DEPTNO NUMBER(2) NOT NULL
    ,DNAME VARCHAR(14) NOT NULL
    ,LOC VARCHAR2(13))
CLUSTER C_DEPT#(DEPTNO);
Img
SELECT * FROM DEPT WHERE DEPTNO = :DEPTNO;

Execution Plan
---------------------------------------------------------------------------------
0   SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=30)
1   0   TABLE ACCESS (CLUSTER) OF 'DEPT' (CLUSTER) (Cost=1 Card=1 Bytes=30)
2   1       INDEX (UNIQUE SCAN) OF 'C_DEPT#_IDX' (INDEX (CLUSTER)) (Cost=1 Card=1)

해시 클러스터 테이블

Img
-- 클러스터 생성
CREATE CLUSTER C_DEPT# (DEPTNO NUMBER(2)) HASHKEY 4;

-- 클러스터 테이블 생성
CREATE TABLE DEPT (
    DEPTNO NUMBER(2) NOT NULL
    ,DNAME VARCHAR(14) NOT NULL
    ,LOC VARCHAR2(13))
CLUSTER C_DEPT#(DEPTNO);

SELECT * FROM DEPT WHERE DEPTNO = :DEPTNO;

Execution Plan
---------------------------------------------------------------------------------
0   SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=30)
1   0   TABLE ACCESS (HASH) OF 'DEPT' (CLUSTER (HASH)) (Cost=1 Card=1 Bytes=30)