뷰·인덱스·옵티마이저·기타 SQL 정리
뷰, 인덱스, RBO vs CBO, 실행계획, PIVOT/UNPIVOT, ROWNUM, 프로시저·트리거까지 한눈에 정리
1. 뷰 (View)
뷰란?
하나 이상의 테이블을 기반으로 만든 가상의 테이블입니다. 실제 데이터를 저장하지 않고, SELECT문을 저장하여 호출 시마다 실행합니다.
뷰의 장점
- 독립성 — 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경 불필요
- 편리성 — 복잡한 쿼리를 뷰로 만들어 간단하게 조회
- 보안성 — 민감한 컬럼을 제외하고 뷰를 생성하여 접근 제한
뷰 생성·삭제
-- 뷰 생성
CREATE VIEW V_EMP AS
SELECT EMP_ID, NAME, DEPT_ID FROM EMP WHERE SAL > 3000;
-- 뷰 교체 (있으면 대체, 없으면 생성)
CREATE OR REPLACE VIEW V_EMP AS ...;
-- 뷰 삭제
DROP VIEW V_EMP;
WITH CHECK OPTION
뷰를 통한 DML(INSERT, UPDATE) 시 뷰의 조건을 벗어나는 데이터 변경을 방지합니다.
CREATE VIEW V_DEPT10 AS
SELECT * FROM EMP WHERE DEPT_ID = 10
WITH CHECK OPTION;
-- DEPT_ID를 20으로 변경하려 하면 오류 발생
UPDATE V_DEPT10 SET DEPT_ID = 20 WHERE EMP_ID = 1; -- ERROR
뷰 DML 제한
다음의 경우 뷰를 통한 DML이 불가합니다: 집계함수, DISTINCT, GROUP BY, UNION, 계산 컬럼 등이 포함된 뷰는 수정 불가(읽기 전용)입니다.
2. 인덱스 (Index)
인덱스란?
테이블의 데이터를 빠르게 검색하기 위한 별도의 데이터 구조입니다. 책의 색인과 같은 역할로, 전체 테이블을 스캔하지 않고 원하는 행을 빠르게 찾습니다.
B-Tree 인덱스 구조
가장 일반적인 인덱스 구조입니다. 루트 → 브랜치 → 리프 노드로 구성됩니다.
- 루트 노드 — 탐색의 시작점, 브랜치 노드를 가리키는 포인터
- 브랜치 노드 — 중간 단계, 리프 노드의 범위 정보 보유
- 리프 노드 — 인덱스 키 값 + 테이블 행의 ROWID 저장. 리프끼리 연결 리스트로 연결
클러스터드 vs 논클러스터드 인덱스
| 항목 | 클러스터드 인덱스 | 논클러스터드 인덱스 |
|---|---|---|
| 데이터 정렬 | 인덱스 키 순서로 물리적 정렬 | 별도 인덱스 구조, 데이터 정렬 안 함 |
| 개수 | 테이블당 1개만 가능 | 여러 개 생성 가능 |
| 리프 노드 | 데이터 페이지 자체 | 데이터 페이지를 가리키는 포인터 |
| 비유 | 사전 (단어 자체가 정렬) | 책 뒤의 색인 (별도 목록) |
SQL Server에서 PK는 기본적으로 클러스터드 인덱스, Oracle에서는 논클러스터드 인덱스입니다.
인덱스 생성·삭제
-- 인덱스 생성
CREATE INDEX IDX_EMP_NAME ON EMP(NAME);
-- 유니크 인덱스
CREATE UNIQUE INDEX IDX_EMP_EMAIL ON EMP(EMAIL);
-- 복합 인덱스
CREATE INDEX IDX_EMP_DEPT_SAL ON EMP(DEPT_ID, SAL);
-- 인덱스 삭제
DROP INDEX IDX_EMP_NAME;
인덱스를 사용하지 못하는 경우
- • 인덱스 컬럼에 변형(함수, 연산)을 가한 경우: WHERE UPPER(NAME) = 'KIM'
- • NOT, <>, NOT IN 조건 사용 시
- • IS NULL, IS NOT NULL 조건 (일부 DBMS)
- • LIKE '%값' (앞에 %가 오는 경우)
- • 묵시적 형변환이 발생하는 경우
3. 옵티마이저
옵티마이저란?
SQL을 가장 효율적으로 실행하기 위한 최적의 실행계획(Execution Plan)을 결정하는 DBMS 내부 엔진입니다.
RBO vs CBO
| 항목 | RBO (규칙기반) | CBO (비용기반) |
|---|---|---|
| 판단 기준 | 미리 정해진 우선순위 규칙 | 통계 정보 기반 비용 계산 |
| 통계 정보 | 사용하지 않음 | 테이블/인덱스 통계 활용 |
| 인덱스 선택 | 인덱스가 있으면 무조건 사용 | 비용이 낮은 방법을 선택 |
| 데이터 양 고려 | 고려하지 않음 | 데이터 분포/양을 고려 |
| 현재 상태 | 거의 사용되지 않음 | 현재 대부분의 DBMS 기본값 |
실행계획 읽는 법
실행계획은 옵티마이저가 결정한 SQL의 처리 경로를 보여줍니다.
- 읽는 순서 — 안쪽(들여쓰기가 깊은 것)부터 바깥쪽으로, 같은 레벨이면 위에서 아래로
- TABLE ACCESS FULL — 전체 테이블 스캔 (Full Table Scan)
- INDEX RANGE SCAN — 인덱스를 범위 조건으로 스캔
- INDEX UNIQUE SCAN — 유니크 인덱스에서 = 조건으로 1건 조회
- NESTED LOOP JOIN — 소량 데이터에 유리한 조인 (바깥 루프 × 안쪽 인덱스 탐색)
- HASH JOIN — 대량 데이터에 유리한 조인 (해시 테이블 생성 후 매칭)
- SORT MERGE JOIN — 양쪽 정렬 후 병합하는 조인
4. PIVOT / UNPIVOT
PIVOT — 행을 열로 변환
특정 컬럼의 값들을 열(컬럼)로 전환합니다. 크로스탭 보고서에 유용합니다.
SELECT *
FROM (
SELECT DEPT_ID, JOB, SAL FROM EMP
)
PIVOT (
SUM(SAL)
FOR JOB IN ('개발' AS 개발, '기획' AS 기획, '영업' AS 영업)
);
UNPIVOT — 열을 행으로 변환
PIVOT의 반대로, 여러 컬럼을 행으로 전환합니다.
SELECT *
FROM DEPT_SAL_SUMMARY
UNPIVOT (
SAL_SUM FOR JOB IN (개발, 기획, 영업)
);
5. ROWNUM, TOP, FETCH FIRST
행 수 제한 비교
| DBMS | 구문 | 특징 |
|---|---|---|
| Oracle | WHERE ROWNUM<= N | WHERE절에서 사용, 1부터 시작 |
| SQL Server | SELECT TOP N ... | SELECT절에서 사용, TOP N WITH TIES 가능 |
| ANSI 표준 | FETCH FIRST N ROWS ONLY | ORDER BY 뒤에 사용, OFFSET 지원 |
ROWNUM 주의사항
-- 올바른 사용
WHERE ROWNUM <= 5 -- 상위 5건
WHERE ROWNUM = 1 -- 첫 번째 행
-- 잘못된 사용 (결과 없음!)
WHERE ROWNUM = 2 -- 1이 먼저 부여되므로 2는 절대 도달 불가
WHERE ROWNUM > 5 -- 1부터 시작하므로 항상 FALSE
ROWNUM은 WHERE절이 실행될 때 1부터 순차적으로 부여됩니다. ROWNUM = 1 (또는 <= N)만 사용 가능하며, > N이나 = N(N>1)은 동작하지 않습니다.
ROWNUM + ORDER BY 함정
-- 잘못된 코드: ROWNUM이 ORDER BY보다 먼저 실행됨
SELECT ROWNUM, NAME, SAL FROM EMP
WHERE ROWNUM <= 3
ORDER BY SAL DESC; -- 임의의 3건을 뽑고 정렬
-- 올바른 코드: 서브쿼리로 정렬 후 ROWNUM 부여
SELECT ROWNUM, NAME, SAL
FROM (SELECT NAME, SAL FROM EMP ORDER BY SAL DESC)
WHERE ROWNUM <= 3; -- 급여 상위 3건
ROWNUM은 WHERE절(2순위)에서 부여되고 ORDER BY(6순위)는 마지막에 실행됩니다. 정렬된 결과에서 상위 N건을 뽑으려면 반드시 서브쿼리를 사용해야 합니다.
6. 프로시저·트리거 기본 개념
프로시저 (Procedure)
일련의 SQL문을 하나의 단위로 묶어 저장하고 호출하는 데이터베이스 객체입니다.
| 특징 | 설명 |
|---|---|
| 매개변수 | IN(입력), OUT(출력), INOUT(양방향) |
| 반환값 | 별도의 RETURN 값 없음 (OUT 파라미터 사용) |
| 호출 | EXECUTE(EXEC) 프로시저명 |
프로시저 vs 함수 (User Defined Function)
| 항목 | 프로시저 | 함수 (UDF) |
|---|---|---|
| 반환 | OUT 파라미터 (여러 개 가능) | RETURN 값 (1개, 필수) |
| SQL 내 사용 | SELECT 내에서 호출 불가 | SELECT 내에서 호출 가능 |
| DML 가능 | INSERT/UPDATE/DELETE 가능 | 제한적 (읽기 위주) |
트리거 (Trigger)
특정 이벤트(INSERT, UPDATE, DELETE)가 발생했을 때 자동으로 실행되는 프로시저입니다.
- BEFORE 트리거 — DML 실행 전에 동작 (데이터 검증, 값 변경)
- AFTER 트리거 — DML 실행 후에 동작 (로그 기록, 연쇄 작업)
- :OLD / :NEW — 변경 전/후 값을 참조하는 의사 레코드
- 행 트리거 vs 문장 트리거 — FOR EACH ROW(행마다 실행) vs 문장 단위(1번 실행)
7. 시험 포인트
SQLD 빈출 핵심 정리
- 1. 뷰는 실제 데이터를 저장하지 않는다 — SELECT문을 저장하며, 조회 시마다 실행
- 2. WITH CHECK OPTION은 뷰 조건 위반 방지 — 뷰의 WHERE 조건을 벗어나는 DML을 차단
- 3. 인덱스 컬럼 변형 시 인덱스 사용 불가 — 함수, 연산, 묵시적 형변환 시 Full Scan
- 4. 클러스터드 인덱스는 테이블당 1개 — 데이터 자체가 인덱스 순서로 정렬
- 5. CBO는 통계 정보 기반, RBO는 규칙 기반 — 현재 대부분 CBO 사용
- 6. ROWNUM은 WHERE보다 ORDER BY가 늦게 실행 — 정렬 후 상위 N건은 서브쿼리 필수
- 7. ROWNUM > 1 또는 ROWNUM = 2는 결과 없음 — ROWNUM은 1부터 순차 부여이므로 1이 없으면 2도 없음
- 8. 트리거는 자동 실행, COMMIT 포함 불가(Oracle) — 트리거 내에서 TCL(COMMIT, ROLLBACK) 사용 불가
- 9. 프로시저는 RETURN 없음, 함수는 RETURN 필수 — 프로시저는 OUT 파라미터로 결과 반환
개념을 확인했다면 문제로 실력을 검증해보세요