데이터베이스의 업데이트 내역이 데이터 파일에 영구 기록되기까지의 과정을 알아보자.

데이터베이스에서 실행한 트랜잭션(transaction)들은 커밋(commit) 명령을 실행하기 이전까지는 미확정된 내용으로, 해당 유저의 세션에만 적용된다. 이 때의 내용들은 데이터베이스 버퍼 캐쉬(Buffer Cache)에 임시적으로 저장된다.

커밋 명령이 실행되면 그 이전까지의 데이터베이스 업데이트 내역이 확정된 것으로 취급되어 모든 유저의 세션에 적용되게 된다. 그러면 트랜잭션의 기록은 우선 리두 로그(Redo Log)에 기록되고, 그 뒤 실제 데이터 파일에 기록된다. 리두 로그에 남은 기록들은 추후 데이터 파일에 손상이 가해졌을 때 복구를 위해 사용된다.

오라클 데이터베이스에서 이러한 기록 작업을 수행하는 것은 배경중의 처리, 즉 백그라운드 프로세스(Background Process)들이다. DBWn(Database Writer), LGWR(Log Writer) 등의 프로세스들은 말 그대로 데이터베이스와 로그에 기록하는 작업을 관리하며, CKPT(Checkpoint)는 로그의 내용을 정기적으로 데이터 파일에 기록하는 체크포인트 작업을 수행한다.

위의 과정을 단순하게 살펴보자면 다음 그림과 같다.

서브쿼리(Subquery)와 인라인 뷰(Inline View)

1. 서브쿼리 (Subquery)

서브쿼리는 말 그대로 부(sub)쿼리 라는 뜻이다. 내부 쿼리, 혹은 하위 쿼리라고 부르기도 한다. SQL 쿼리의 내부에 액자구조로 또 다른 쿼리가 위치할 경우 내부의 쿼리를 서브쿼리라고 한다. 서브쿼리를 포함한 쿼리에서 서브쿼리를 뺀 바깥쪽 부분은 외부 쿼리(Outer Query) 혹은 상위 쿼리라고 한다. 

서브쿼리는 SELECT 문, INSERT/UPDATE/DELETE 문, 그리고 다른 서브쿼리 내부에서 사용될 수 있으며, 복합적 쿼리를 위해 사용된다는 점에서 조인과 유사하다. 같은 내용의 쿼리를 서브쿼리 방식이나 조인 방식 중 선택하여 구현할 수 있는 경우도 있다.

모든 절이 서브쿼리를 포함할 수 있는 것은 아니며, 서브쿼리가 들어갈 수 있는 위치에는 SELECT절/FROM절/WHERE절/HAVING절/ORDER BY절과 INSERT 문의 VALUES절, UPDATE문의 SET절이 있다.

서브쿼리에는 단일 행 서브쿼리, 다중 행 서브쿼리, 그리고 다중 컬럼 서브쿼리가 있다. 

1.1. 단일 행 서브쿼리(Single Row Subquery)

서브쿼리의 결과가 1행 혹은 0행인 경우를 말한다. 예를 들어 '기본키 = 101인 컬럼의 DATE값' 등의 쿼리를 생각할 수 있다. 

쿼리의 결과가 단일값이므로 외부 쿼리와 '=','<','>' 등의 연산자로 연결될 수 있다.

1.2. 다중 행 서브쿼리(Multi Row Subquery)

서브쿼리의 결과가 2개 이상일 수 있는 경우를 말한다. 예를 들어 '직위 = 과장인 모든 직원의 이름' 등을 생각할 수 있다.

쿼리의 결과가 여러개일 수 있으므로 외부 쿼리와 '='등의 연산자로 연결하면 에러가 나게 된다. 따라서 다중 행 비교 연산자를 사용해야 한다. 다중 행 비교 연산자에는 IN, ALL, ANY, SOME, EXISTS 등이 있으며, 쿼리 결과 전체 중 특정 조건을 만족하는 값이 존재하는지를 연산하게 된다.

1.3. 다중 컬럼 서브쿼리(Multi Column Subquery)

서브쿼리의 결과로 여러 컬럼이 반환되는 경우이다. 다중 행 서브쿼리에 포함된다고도 생각할 수 있다. 예를 들어 '직위 = 과장인 모든 직원의 나이 및 근속년수' 등을 생각할 수 있다.

서브쿼리의 컬럼 순서와 외부쿼리의 컬럼 순서가 서로 맞도록 주의해야 한다.

2. 인라인 뷰 (Inline View)

서브쿼리가 FROM 절 안에서 사용될 경우, 해당 서브쿼리를 '인라인 뷰'라고 일컫는다. 서브쿼리의 결과가 하나의 테이블에 대한 뷰처럼 사용되기에 이렇게 부르는 것이다. 

MSSQL에서 인라인 뷰를 사용할 때에는 서브쿼리의 결과에 대해 앨리어스(Alias), 즉 가명을 지정해 주어야 한다.

고유키(Unique Key) 속성이 있는 컬럼은 모든 로우가 서로 다른 값을 가져야 하며, 여기에는 NULL값도 포함된다. 기본키(Primary Key) 속성이 있는 컬럼은 한 테이블 당 한 컬럼이 최대한도이며, 모든 로우가 서로 다른 값을 가지면서 NULL 값이 아니어야 한다. 

즉 고유키 + NOT NULL = 기본키 라고 할 수 있다.

* 일부 데이터베이스의 경우 기본키의 NOT NULL 속성이 필수가 아닌 경우도 있다. 위 공식은 오라클과 MSSQL 등 주요 데이터베이스의 경우이다.

1. 개념설명

커서 (Cursor)는 컴퓨터로 문서를 작성해 본 사람이라면 누구나 알고 있는 용어이다. 화면에서 현재 사용자의 위치를 나타내며 깜빡거리는 막대기가 바로 커서이다.

데이터베이스에서의 커서 또한 유사한 개념이다. 방대한 양의 데이터에서 특정 위치, 특정 로우(row)를 가리킬때 커서가 사용된다. 위키피디아에서는 커서에 대해 '많은 로우 중 한 로우를 가리키는 포인터와 같다'고 설명하고 있다. 

즉 커서란 현재 작업중인 레코드를 가리키는 오브젝트이다. 

2. 오라클

오라클의 경우 커서를 묵시적 커서(Implicit Cursor)와 명시적 커서(Explicit Cursor)로 분류한다. 

묵시적 커서는 오라클에서 SQL문장이 처리되는 위치를 가리키는 주소이며, 실행되는 모든 SQL 문장에 대해서 자동적으로 생성된다. 예를 들어 SELECT구문으로 데이터를 검색했을 때, 묵시적 커서가 생성되어 현재 검색중인 레코드를 가리키고 있게 된다. 

명시적 커서는 묵시적 커서가 생성되는 부분, 예를 들어 PL/SQL 구문에 프로그래머가 커서의 이름 등 속성을 명시할 경우 생성된다. 명시적으로 커서를 생성했을 경우 프로그래머가 커서에 직접 접근할 수 있으며, 이를 통해 쿼리의 각 로우를 개별적으로 처리할 수 있는 등의 장점이 있다. 하지만 커서 선언문을 처리해야 하므로 처리 속도는 묵시적 커서에 비해 느리다.

작업이 끝난 후에도 커서가 계속해서 작동하면서 메모리를 사용할 수 있으니, 커서를 닫는(Close) 것을 잊지 말자.

3. MSSQL

MSSQL의 경우 커서는 정적 커서(Static Cursor), 동적 커서(Dynamic Cursor), 키 집합 커서(Keyset-Driven Cursor), 정방향 전용 커서(Forward-Only Cursor)로 나뉜다. 

정적 커서는 읽기 전용인 커서로, tempdb에 쿼리 결과의 전체 집합을 복사해서 저장해 나타낸다. 커서가 열린 뒤 쿼리 결과에 변화가 생겨도 정적 커서에는 반영되지 않는다. 

동적 커서는 정적 커서의 반대 개념으로, 커서를 통해 스크롤을 이동할 때 변경된 로우가 있으면 커서에 반영된다. 

정방향 전용 커서는 쿼리의 처음부터 끝까지 한 방향으로만 움직인다. 스크롤을 올릴 수 없기 때문에 커서를 통해 로우가 인출된 뒤 데이터베이스에 변화가 생겨도 대부분 커서에 나타나지 않는다. 빠른 정방향 전용 커서(FAST_FORWARD Cursor)는 커서 중 가장 속도가 빠르다.

키 집합 커서는 정적 커서와 동적 커서의 혼합된 형태라고 할 수 있다. 키 집합 커서는 쿼리 결과 전체에 대한 '키'를 tempdb에 저장하여, 스크롤을 이동할 때 '키'를 통해 각 로우에 대한 실시간 데이터를 받아온다. 따라서 동적 커서와 마찬가지로 로우 값의 변경 내역이 커서에 반영된다. 하지만 로우 자체가 삭제되거나 키 값이 업데이트되었을 경우 변경 이후의 내용은 결과에 나타나지 않는다. 또한 외부에서 입력된 로우의 경우 키 값이 존재하지 않으므로 커서에 반영되지 못한다.

클러스터드/넌클러스터드 인덱스의 개념이 생각보다 복잡해서 따로 포스팅에 정리하기로 했다.

이 글에서 말하는 모든 인덱스 구조는 B*tree 구조를 바탕으로 하고 있다. 즉 각 노드들이 트리모양을 이루며 루트 노드부터 리프 노드까지 서로 포인터를 통해 연결되어 있는 구조이다.

1. 클러스터드 인덱스 (Clustered Index)

우선 클러스터드 인덱스의 생성 방법을 동영상을 통해 익혀보자.

동영상에 예시로 등장하는 테이블의 데이터는 정렬되어 있지 않은 상태이다. 이런 상태의 테이블을 힙(Heap)이라고 일컫는다. 그 상태에서 '클러스터드 인덱스 (Clustered Index)'를 생성할 경우, 테이블의 데이터 자체가 물리적으로 재배열되어 순서대로 정렬된다. 테이블 자체가 클러스터드 인덱스의 리프 노드(leaf node)로서 기능하기 때문이다. 

인덱스가 테이블 데이터 자체를 포함하는 이런 형식은 흔히 전화번호부와 비교된다. 전화번호부 또한 번호목록이라는 데이터 자체가 인덱스 기능을 하고 있다. 전화번호부가 단순한 번호목록과 구별되는 부분은 각 페이지의 첫 상호명, ㄱ으로 시작하는 첫 상호명 등 일부 데이터를 한 눈에 알아볼 수 있도록 표기함으로서 데이터의 큰 경향을 쉽게 파악해서 원하는 데이터가 있는 부분으로 이동할 수 있도록 한 부분이다. 클러스터드 인덱스도 마찬가지이다. 

(출처:https://technet.microsoft.com/en-us/library/ms177443(v=sql.105).aspx)

위 그림에서 리프 노드들은 테이블의 데이터 자체이고, 그를 큰 단위로 분류한 중간 단계(Internediate Level)와 하나의 페이지 안에 모아놓은 루트 노드 (Root Node)로 연결된다. 전화번호부의 비유에서 ㄱ각 페이지의 첫 상호를 크게 표시한 것은 중간 단계에, ㄱ부터 ㅎ까지의 자음을 포함한 첫 상호명들을 모아놓은 것은 루트 노드에 비유될 수 있을 것이다. 

테이블 자체가 물리적으로 재배열되어 인덱스 구조에 포함되므로, 클러스터드 인덱스는 각 테이블 당 1개씩만 생성될 수 있다.

2. 넌클러스터드 인덱스 (Non-Clustered Index)

클러스터드 인덱스와 달리 넌클러스터드 인덱스의 필요성과 생성 과정은 직관적으로 이해하기 힘들다. 우선 아래의 동영상을 통해 사용 사례를 살펴보자.

넌클러스터드 인덱스는 원 테이블을 재배열하지 않는다. 무작위, 혹은 클러스터드 인덱스대로 배열되어있는 테이블을 단지 가리킬 뿐이다. 위 동영상의 경우 원 테이블은 ID 순서대로 배열되어 있으나, 이에 대해 이름 순서대로 배열된 인덱스를 따로 생성해서, 그 인덱스가 원 테이블을 가리키도록 하고 있다. 

(출처: https://technet.microsoft.com/en-us/library/ms177484(v=sql.105).aspx)

즉 클러스터드 인덱스에서는 데이터 페이지 자체를 리프 노드로 사용하는 반면, 넌클러스터드 인덱스는 원하는 순서대로 정렬된 리프 노드를 새로 만들어 그 리프노드들이 데이터 페이지를 가리키도록 하는 것이다. 따라서 원 테이블에는 영향이 가지 않으므로 얼마든지 새로 만들 수 있으며, 테이블에서 일부 데이터만을 따로 인덱스로 만드는 일도 가능하다. 

넌클러스터드 인덱스를 생성하면 이처럼 한 테이블에 대해 다양한 인덱스를 만들 수 있으나, 그림에서도 나타나다시피 저장 공간이 훨씬 많이 소요되고 구조 또한 복잡하다는 단점이 있다.