[SQLD] 2과목 3장 관리 구문 - DML, TCL, DDL, DCL (구 SQL 최적화 기본 원리 / 2024 개정사항 반영) (終)

728x90

※ 이전글: 2023.03.05 - [Cerificate/SQLD] - [SQLD] 2과목 2장 SQL 활용 - 그룹 함수, 윈도우 함수 등 (구 그룹 함수~절차형SQL / 2024 개정사항 반영)

2024년 3월 SQLD 시험에 대한 이론은 이번 포스팅으로 끝난다. 마지막 주제는 관리 구문이다. 새로운 개념이 나온 것은 아니고, SQL 기본과 활용 부분에 흩어져 있던 것을 하나로 모은 챕터라고 보면 된다. 그래서 이번 글의 내용은 기존 글에서 혼재되어 있던 것을 이곳으로 모으고 이기적 강의의 내용을 추가하여 이해를 돕는 것에 주안점을 두었다.

SQLD 이론의 마지막 파트인 'SQL 최적화 기본 원리'로 넘어 왔다. 하지만 문제 풀이가 남았기 때문에 방심할 수 없다. 자격증 취득을 위해 끝까지 달리도록 하겠다.

Index

DML

DML (DATA MANIPULATION LANGUAGE) : 테이블에 원하는 자료들을 조작 (삽입, 수정, 삭제)하는 명령어

1. INSERT

테이블에 신규 데이터를 행 단위로 입력.
입력방법
- 1) INSERT INTO 테이블명 (COLUMN_LIST) VALUES (COLUMN_LIST에 넣을 VALUE_LIST);
- 2) INSERT INTO 테이블명 VALUES (전체 COLUMN에 넣을 VALUE_LIST); (비권장)
한 번에 한 건만 입력됨.
에러 케이스
- 1) 식별자 (PK)에 NULL을 입력한 경우.
- 2) NOT NULL인 컬럼에 NULL 입력한 경우.
- 3) 지정된 자료형을 지키지 않은 경우.
- 4) 컬럼 목록과 입력된 값의 수가 다른 경우.

2. UPDATE

데이터 수정.
형식: UPDATE 테이블명 SET 수정되어야 할 칼럼명 = 수정되기를 원하는 새로운 값 [WHERE] 조건;

3. DELETE

데이터를 행 단위로 삭제
형식: DELETE FROM 삭제를 원하는 정보가 들어있는 테이블명 [WHERE] 조건;
ROLLBACK으로 복원 가능.
DDL (CREATE, ALTER, RENAME, DROP) ▷ 작업 즉시 (AUTO COMMIT) 완료.
DML (INSERT, UPDATE, DELETE, SELECT) ▷ COMMIT 명령어를 입력 후 TRANSACTION 종료.

※ MERGE

MERGE INTO 기존 테이블 A
USING 신규 테이블 B
ON (A.ID = B.ID) -- 두 값을 비교해 동일하면 UPDATE, 다르면 INSERT

WHEN MATCHED THEN
UPDATE
SET A.ID = B.ID
	, A.번호 = B.번호
    
WHEN NOT MATCHED THEN 
INSERT (A.ID, A.이름, A.번호)
VALUES (B.ID, B.이름, B.번호);

기존 데이터는 업데이트하고, 신규 데이터는 추가함.
키워드 확인 (MERGE INTO, USING, ON 등)

4. SELECT

자료 조회
DISTINCT : 중복된 데이터 제외
WILDCARD 사용 : 애스터리스크(*) ▷ 모두 조회 | % ▷ 0개 이상의 특정 문자 | - ▷ 한 글자
ALIAS 부여 : 별명 사용.

5. 산술 연산자와 합성 연산자

산술 연산자
- NUMBER와 DATE 자료형에 적용됨.
- (), *, /, +, - 의 우선순위
합성 (CONCATENATION) 연산자
- 문자와 문자를 연결
- ORACLE : 2개의 수직 바(||)
  ex) SELECT PLAYER_NAME || '선수,' || HEIGHT || 'cm,' || WEIGHT || 'kg' 체격정보 FROM PLAYER;
- SQL SERVER : + 표시
  ex) SELECT PLAYER_NAME +'선수, '+ HEIGHT +'cm, '+ WEIGHT +'kg'체격정보 FROM PLAYER;

TCL

1. 트랜잭션 개요

트랜잭션 (TRANSACTION)
- 어떤 업무를 수행하기 위한 일련의 단계. (이기적)
- 데이터베이스의 논리적 연산단위.
- 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작.
- 하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함됨.
- ALL OR NOTHING (원자성)
- 커밋 (COMMIT): 옳게 입력된 데이터를 데이터베이스에 반영시키는 것.
- 롤백 (ROLLBACK): 트랜잭션 시작 이전의 상태로 되돌리는 것.
- 저장점 (SAVEPOINT)
- TCL (TRANSACTION CONTROL LANGUAGE)
트랜잭션의 특성 (빈출!)

2. COMMIT

INSERT, UPDATE, DELETE 문장 사용 후, 작업이 완료되었음을 데이터베이스에 알려 주기 위해 사용.
트랜잭션 이전의 데이터 상태
- 메모리 BUFFER에만 영향을 받았기 때문에 데이터의 변경 이전 상태로 복구 가능.
- 현재 사용자는 SELECT 문장으로 결과를 확인 가능.
- 다른 사용자는 현재 사용자가 수행한 명령의 결과를 열람 불가.
- 변경된 행은 잠금 (LOCKING)이 설정되어 다른 사용자가 변경 불가.
COMMIT 이후의 데이터 상태
- 데이터의 변경 사항이 데이터베이스에 반영.
- 이전 데이터는 영원히 상실.
- 모든 사용자가 결과를 볼 수 있음.
- 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행 조작 가능.
주의
- ORACLE - DML (INSERT, UPDATE, DELETE, MERGE) 수동 COMMIT / DDL (CREATE, ALTER, DROP) 자동 COMMIT
- SQL Server는 기본적으로 AUTO COMMIT.
SQL Server에서의 트랜잭션
- 1) AUTO COMMIT SQL Server의 기본 방식 ▷ DML, DDL을 수행할 때마다 DBMS가 트랜잭션을 컨트롤.
- 2) 암시적 트랜잭션 ▷ 트랜잭션의 시작은 DBMS가 처리, 끝은 사용자가 명시적으로 COMMIT or ROLLBACK.
- 3) 명시적 트랜잭션 ▷ 트랜잭션의 시작과 끝을 모두 사용자가 명시적으로 지정.

3. ROLLBACK

특정지점 (마지막 Commit 혹은 Savepoint)으로 원상복구.
변경사항 취소.
데이터가 이전 상태로 복구되며 관련된 행에 대한 잠금(LOCKING)이 풀리고 다른 사용자에 의한 데이터 변경 가능.
COMMIT과 ROLLBACK 효과
- 데이터 무결성 보장
- 영구적인 변경을 하기 전에 데이터의 변경 사항 확인 가능
- 논리적으로 연관된 작업을 그룹핑하여 처리 가능

4. SAVEPOINT

저장점 (SAVEPOINT) 정의 ▷ 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백 가능.
복수의 저장점을 정의할 수 있으며, 동일이름으로 저장점을 정의했을 때는 나중에 정의한 저장점이 유효.
ROLLBACK 시, SAVEPOINT를 지정하지 않으면 마지막 COMMIT 시점으로 복구.
저장점 정의: SAVEPOINT SVPT1;
저장점까지 롤백: ROLLBACK (TO) SVPT1; or (SQL Server) ROLLBACK TRANSACTION SVTR1;

5. Review!

COMMIT과 ROLLBACK을 실행하지 않아도 자동으로 트랜잭션이 종료되는 경우
- (ORACLE)
- CREATE, ALTER, DROP, RENAME, TRUNCATE TABLE 등 DDL 문장을 실행하면 그 전후 시점에 자동으로 커밋된다.
- DML 문장 이후에 커밋 없이 DDL 문장이 실행되면 DDL 수행 전에 자동으로 커밋된다.
- 데이터베이스를 정상적으로 접속을 종료하면 자동으로 트랜잭션이 커밋된다.
- 애플리케이션의 이상 종료로 데이터베이스와의 접속이 단절되었을 때는 트랜잭션이 자동으로 롤백.
- (SQL Server - AUTO COMMIT이 기본)
- 애플리케이션의 이상 종료로 데이터베이스 (인스턴스)와의 접속이 단절되었을 때, 트랜잭션 자동 롤백.

DDL

테이블과 같은 객체의 생성 / 수정 / 삭제 기능
CREATE, ALTER, DROP, RENAME, TRUNCATE

1. 데이터 유형

ORACLE
- VARCHAR2(n): n바이트까지 입력받을 수 있는 가변형 문자열.
- NUMBER(n, m): 숫자형 값을 n자리만큼, 소수를 m자리만큼 입력받음. n,m은 생략 가능 (예. NUMBER(2)-99까지 입력 가능)
- DATE: 날짜값 입력 (시간까지 나오는 TIMESTAMP 자료형도 있음.)
이외에도 ANSI/ISO에서는 Binary String Type, Binary Large Object String Type, National Character String Type, Boolean Type 등의 다양한 유형을 표시.
VARCHAR과 CHAR의 차이
- VARCHAR 유형은 가변 길이이므로 필요한 영역은 실제 데이터 크기뿐. ☞ 길이가 다양한 칼럼과 정의된 길이와 실제 데이터 길이에 차이가 있는 칼럼에 적합. 보다 작은 영역에 저장할 수 있음.
- CHAR에서는 문자열을 비교할 때 공백(BLANK)을 채워서 비교. ex) CHAR - 'AA' = 'AA ' | VARCHAR - 'AA' ≠ 'AA '

2. Create Table

테이블에 있는 모든 데이터를 고유하게 식별할 수 있으면서 반드시 값이 존재하는 단일 칼럼이나 칼럼의 조합들(후보키) 중에 하나를 선정하여 기본키 칼럼으로 지정.
구문 형식 : CREATE TABLE 테이블이름 ( 칼럼명1 DATATYPE [DEFAULT 형식], 칼럼명2 DATATYPE [DEFAULT 형식], 칼럼명2 DATATYPE [DEFAULT 형식] ) ;
테이블 생성 규칙
- 테이블명은 객체를 나타내는 적절한 이름을 사용한다. (가급적 단수형)
- 테이블 명은 다른 테이블의 이름과 중복되지 않아야 한다.
- 한 테이블 내에서는 칼럼명이 중복되게 지정될 수 없다.
- 테이블 이름을 지정하고 각 칼럼들은 괄호 '( )' 로 묶어 지정한다.
- 각 칼럼들은 콤마 ',' 로 구분되고, 테이블 생성문의 끝은 항상 세미콜론 ';' 으로 끝난다.
- 칼럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다.(데이터 표준화 관점)
- 칼럼 뒤에 데이터 유형은 꼭 지정되어야 한다.
- 테이블명과 칼럼명은 반드시 문자로 시작해야 하고, 벤더별로 길이에 대한 한계가 있다.
- 벤더에서 사전에 정의한 예약어(Reserved word)는 쓸 수 없다.
- A-Z, a-z, 0-9, _, $, # 문자만 허용된다.
- 테이블 생성 시 대/소문자 구분은 하지 않는다. (기본 대문자)
- DATETIME 데이터 유형에는 별도로 크기를 지정하지 않는다.
- 문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시해야 한다.
- 칼럼과 칼럼의 구분은 콤마로 하되, 마지막 칼럼은 콤마를 찍지 않는다.
- 칼럼에 대한 제약조건이 있으면 CONSTRAINT를 이용하여 추가할 수 있다.

테이블 구조 확인: “DESC 테이블명;” (Oracle)
Select 구문으로 테이블 생성 (CTAS: Create Table ~ As Select ~)
- 칼럼별로 데이터 유형을 다시 재정의 하지 않아도 되는 장점.
- 주의 : 기존 테이블 제약조건 중 NOT NULL만 새로운 복제 테이블에 적용되고 기본키, 고유키, 외래키, CHECK 등의 다른 제약 조건은 사라짐.

3. ALTER TABLE

* 제약조건

사용자가 원하는 조건의 데이터, 즉, 데이터의 무결성을 유지하기 위한 데이터베이스의 보편적인 방법으로 테이블의 특정 칼럼에 설정.

문법
- ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 PRIMARY KEY (컬럼명);
- ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 CHECK (컬럼명 IN ('MALE', 'FEMALE'));
PRIMARY KEY: 식별자 규칙을 물리적 모델링한 것. NULL, 중복값 모두 불가. 특정 컬럼을 식별자로 할 경우, 자동으로 NOT NULL, UNIQUE로 변경됨.
UNIQUE KEY: NULL 가능, 중복 불가.
NOT NULL: NULL 값이 입력되지 않게 함. (NULL (ASCII 코드 00번)은 공백 (BLANK, ASCII 코드 32번), 숫자 0 (ZERO, ASCII 48)과 다른 값. 공집합과도 다름. ‘아직 정의되지 않은 미지의 값’ or ‘현재 데이터를 입력하지 못하는 경우’를 뜻함.)
CHECK: 특정 컬럼에 조건에 맞는 지정된 데이터만 입력.
FOREIGN KEY: 테이블끼리 연결된 관계를 물리적 모델링한 것. (FK 설정으로 무결성 지원 가능.)

1) ADD COLUMN: 테이블에 필요한 칼럼을 추가
- 형식: ALTER TABLE 테이블명 ADD 추가할 칼럼명 데이터 유형;
- 주의: 새로 추가된 칼럼은 테이블의 마지막 칼럼이 되고, 칼럼의 위치 지정 불가.
2) DROP COLUMN: 불필요 칼럼 삭제
- 형식: ALTER TABLE 테이블명 DROP COLUMN 삭제할 칼럼명;
- 주의: 한 번에 하나의 칼럼만 삭제 가능. 칼럼 삭제 후 최소 하나 이상의 칼럼이 테이블에 존재해야 함. 제거된 칼럼의 복구 불가
3) MODIFY COLUMN: 칼럼의 속성 (데이터 유형, 디폴트(DEFAULT) 값, NOT NULL 제약조건 등) 변경.
- 형식: ALTER TABLE 테이블명 MODIFY (칼럼명1 데이터 유형 [DEFAULT 식] [NOT NULL], 칼럼명2 데이터 유형 …);
- 주의사항
  - (1) 해당 칼럼의 크기를 늘릴 수는 있지만 줄이지는 못한다. (기존의 데이터가 훼손될 수 있기 때문)
  - (2) 해당 칼럼이 NULL 값만 가지고 있거나 테이블에 아무 행도 없으면 칼럼의 폭을 줄일 수 있다.
  - (3) 해당 칼럼이 NULL 값만을 가지고 있으면 데이터 유형을 변경할 수 있다.
  - (4) 해당 칼럼의 DEFAULT 값을 바꾸면 변경 작업 이후 발생하는 행 삽입에만 영향을 미치게 된다.
  - (5) 해당 칼럼에 NULL 값이 없을 경우에만 NOT NULL 제약조건을 추가할 수 있다.
4) RENAME COLUMN: 컬럼명 변경.
- 형식: ALTER TABLE 테이블명 RENAME COLUMN 기존 컬럼명 TO 새로운 컬럼명;
5) DROP CONSTRAINT : 제약조건 삭제.
- 형식: ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;
6) ADD CONSTRAINT: 제약조건 추가.
- 형식: ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (칼럼명);
- 외부키(FK)를 설정하여 실수에 의한 테이블 혹은 데이터의 삭제 방지 가능.

4. RENAME TABLE

테이블의 이름 변경.
형식 : RENAME 변경전 테이블명 TO 변경후 테이블명;

5. DROP TABLE

테이블 영구 삭제
형식 : DROP TABLE 테이블명 [CASCADE CONSTRAINT];
CASCADE CONSTRAINT : 해당 테이블과 관계가 있는 제약조건도 삭제. (테이블이 삭제되는 건 아님.)

6. TRUNCATE TABLE

테이블의 데이터 삭제.
해당 테이블에 들어있던 모든 행들이 제거되고 저장 공간을 재사용 가능하도록 해제. (테이블 삭제 X)
형식 : TRUNCATE TABLE 테이블명;
WHERE절 사용 불가 (조건 부여 X)
DELETE, TRUNCATE, DROP의 차이!
- 1) 데이터 복구가 가능한가? DELETE만 ROLLBACK 가능. DDL인 TRUNCATE와 DROP은 데이터 삭제.
- 2) 테이블이 남아있는가? DROP만 테이블 삭제. DELETE, TRUNCATE는 테이블 유지.
- 테이블에 있는 데이터를 삭제하는 명령어 : TRUNCATE TABLE 명령어 & DELETE 명령어
- DELETE와 TRUNCATE의 처리방식이 상이.
- 테이블의 전체 데이터를 삭제하는 경우 ☞ 시스템 부하가 적은 TRUNCATE TABLE 권고 (단, "TRUNCATE TABLE" 은 정상적 복구 불가)

DCL

1. DCL 개요

SQL 문장 구분
- DDL : 테이블 생성과 조작
- DML : 데이터 조작
- TCL : TRANSACTION 제어
- DCL(DATA CONTROL LANGUAGE): 유저 생성 및 권한 제어 / 데이터베이스의 권한을 부여 혹은 회수하며 객체를 보호. (GRANT, REVOKE, ROLE)

2. 유저와 권한

Oracle 제공 유저

Oracle과 SQL Server의 사용자 아키텍처 차이
- Oracle : 유저를 통해 데이터베이스에 접속 ▷ 아이디와 비밀번호 방식으로 인스턴스에 접속하고, 이에 해당하는 스키마에 오브젝트 생성 등의 권한 부여.
- SQL : 로그인 생성. 인스턴스 내에 존재하는 다수의 데이터베이스에 연결하여 작업하기 위해 유저를 생성한 후 로그인과 유저를 매핑. 로그인 방식은 아래와 같음.
  - Windows 인증 방식 : Windows에 로그인한 정보 SQL Server에 접속하는 방식. Windows에서 사용자 ID를 확인함.
  - 혼합 모드 방식 : Windows 인증으로도 SQL Server에 접속 가능 + Oracle의 인증 방식인 사용자 아이디와 비밀번호로 SQL Server에 접속. 강력한 암호 (숫자+문자+특수문자 등의 혼용)이 요구됨.

1) 유저 생성과 시스템 권한 부여

모든 DDL 문장(CREATE, ALTER, DROP, RENAME 등)은 상응하는 권한이 있어야 문장을 실행할 수 있음 ▷ 시스템 권한 필요!
롤 (ROLE)을 이용해 간편하고 쉽게 권한을 부여.
유저 생성 권한 (CREATE USER) 부여.
ex) GRANT CREATE USER TO 대상;

2) OBJECT에 대한 권한 부여

오브젝트 권한 : 특정 오브젝트인 테이블, 뷰 등에 대한 SELECT, INSERT, DELETE, UPDATE 작업 명령어.
다른 유저의 테이블에 접근하려면 해당 테이블에 대한 오브젝트 권한을 소유자로부터 받아야 함.
ex) GRANT SELECT [ON 대상 객체] TO 대상 [WITH GRANT OPTION];
WITH GRANT OPTION: 타인에게 권한을 줄 수 있는 자격 부여.

※ REVOKE

권한을 다시 회수하는 문법.
형식: REVOKE 회수할 권한 [ON 회수할 객체(기능)] FROM 회수할 계정 [CASCADE];

3. ROLE

여러 권한을 한번에 부여할 수 있도록 패키지화.
예) GRANT CONNECT, RESOURCE TO USER
유저와 권한 사이의 중개 역할
ROLE에 포함되어 있는 권한이 필요한 유저에게 해당 ROLE만을 부여.
시스템 권한과 오브젝트 권한 모두 부여 가능. ROLE은 유저에게 직접 or 다른 ROLE에 포함되어 유저에게 부여될 수도 있음.

ex) CREATE ROLE LOGIN_TABLE; GRANT CREATE SESSION, CREATE TABLE TO LOGIN_TABLE;
Oracle : CONNECT와 RESOURCE 주로 사용.
- 유저 삭제 : DROP USER ▷CASCADE 옵션 : 오브젝트 삭제 후, 유저 삭제. ex) DROP USER JISUNG CASCADE;

SQL Server : ROLE에 멤버로 참여하는 방식 사용. Role을 자주 사용하지 않고, 서버 수준 역할 및 데이터베이스 수준 역할을 이용해 로그인 및 사용자 권한 제어.

2024 개정 이전 범위

1절 옵티마이저와 실행계획

1. 옵티마이저

옵티마이저 (Optimizer) : SQL문에 대해 최적의 실행 방법을 결정하는 역할.
실행계획 (Execution Plan) : 최적의 실행 방법
옵티마이저가 선택한 실행 방법의 적절성 여부는 질의의 수행 속도에 가장 큰 영향을 미침.
옵티마이저 분류 : 대부분의 관계형 데이터베이스는 비용기반 옵티마이저만 제공
- 규칙기반 옵티마이저 : 규칙(우선 순위)을 가지고 실행계획을 생성.
- 참조 정보 : 인덱스 유무와 종류, 연산자(=, <, <>, LIKE, BETWEEN 등)의 종류, 참조 객체(힙 테이블, 클러스터 테이블 등)의 종류 등
- 주요 규칙
  - 1) 규칙 1. Single row by rowid : ROWID를 통해서 테이블에서 하나의 행을 액세스하는 방식
  - 2) 규칙 4. Single row by unique or primary key : 유일 인덱스(Unique Index)를 통해서 하나의 행을 액세스하는 방식
  - 3) 규칙 8. Composite index : 복합 인덱스에 동등(‘=’ 연산자) 조건으로 검색하는 경우
  - 4) 규칙 9. Single column index : 단일 칼럼 인덱스에 ‘=’ 조건으로 검색하는 경우
- 인덱스를 이용한 액세스 방식이 전체 테이블 액세스 방식보다 우선 순위가 높음.
- 비용기반 옵티마이저 : SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식 (비용 : SQL문을 처리하기 위해 예상되는 소요시간 또는 자원 사용량)
- 비용 예측을 위해 규칙기반 옵티마이저가 사용하지 않는 테이블, 인덱스, 칼럼 등의 다양한 객체 통계정보와 시스템 통계정보 등을 이용.
- 질의 변환기, 대안 계획 생성기, 비용 예측기 등의 모듈로 구성.
  - 질의 변환기 : 사용자가 작성한 SQL문을 처리하기에 보다 용이한 형태로 변환하는 모듈.
  - 대안 계획 생성기 : 동일한 결과를 생성하는 다양한 대안 계획을 생성하는 모듈. (대안 계획은 연산의 적용 순서 변경, 연산 방법 변경, 조인 순서 변경 등을 통해서 생성됨.)
  - 비용 예측기 : 대안 계획 생성기에 의해서 생성된 대안 계획의 비용을 예측하는 모듈.

2. 실행계획

실행계획 (Execution Plan) : SQL에서 요구한 사항을 처리하기 위한 절차와 방법.
실행계획 생성 : SQL을 어떤 순서로 어떻게 실행할 지를 결정하는 작업.
실행계획 구성 요소
- 조인 순서(Join Order) : 조인작업을 수행할 때 참조하는 테이블의 순서
- 조인 기법(Join Method) : 두 개의 테이블을 조인할 때 사용할 수 있는 방법
- 액세스 기법(Access Method) : 테이블을 액세스할 때 사용할 수 있는 방법. ex) 인덱스 스캔, 전체 테이블 스캔
- 최적화 정보(Optimization Information) : 옵티마이저가 실행계획의 각 단계마다 예상되는 비용 사항을 표시한 것
  - 1) Cost : 상대적인 비용 정보
  - 2) Card : Cardinality의 약자. 주어진 조건을 만족한 결과 집합 or 조인 조건을 만족한 결과 집합의 건수
  - 3) Bytes : 결과 집합이 차지하는 메모리 양을 바이트로 표시한 것.
  - 모두 예상치!
- 연산(Operation) : 여러 조작을 통해서 원하는 결과를 얻어내는 일련의 작업.

2절 인덱스 기본

1. 인덱스 특징과 종류

책의 찾아보기와 유사한 개념. 테이블을 기반으로 선택적으로 생성할 수 있는 구조.
목적 : 검색 성능의 최적화
DML 작업은 테이블과 인덱스를 함께 변경해야 하므로 느려질 수 있음.

트리 기반 인덱스
- B-트리 인덱스 : DBMS에서 가장 일반적. 브랜치 블록(Branch Block)과 리프 블록(Leaf Block)으로 구성.
- 루트 블록(Root Block) : 브랜치 블록 중에서 가장 상위에서 있는 블록
- 브랜치 블록 : 분기를 목적으로 하는 블록. 다음 단계의 블록을 가리키는 포인터를 가짐.
- 리프 블록 : 트리의 가장 아래 단계에 존재. | 인덱스를 구성하는 칼럼의 데이터와 해당 데이터를 가지고 있는 행의 위치를 가리키는 레코드 식별자(RID, Record Identifier/Rowid)로 구성. (인덱스 데이터는 인덱스를 구성하는 칼럼의 값으로 정렬됨.) | 양방향 링크(Double Link)를 가짐.
- B-트리 인덱스 검색 과정
  - 1) 브랜치 블록의 가장 왼쪽 값이 찾고자 하는 값보다 작거나 같으면 왼쪽 포인터로 이동.
  - 2) 찾고자 하는 값이 브랜치 블록의 값 사이에 존재하면 가운데 포인터로 이동.
  - 3) 오른쪽에 있는 값보다 크면 오른쪽 포인터로 이동.
  - 상기 과정 반복.
- 비트맵 인덱스(Bitmap Index), 리버스 키 인덱스(Reverse Key Index), 함수기반 인덱스(FBI, Function-Based Index) 등 존재.
SQL Server의 클러스터형 인덱스
- 저장 구조에 따라 클러스터형(clustered) 인덱스와 비클러스터형(nonclustered) 인덱스로 구분.
- 클러스터형 인덱스의 특징
  - 1) 리프 페이지가 곧 데이터 페이지. ☞ 레코드 식별자가 리프 페이지에 없음. 리프 페이지를 탐색하면, 해당 테이블의 모든 칼럼 값을 바로 얻을 수 있음.
  - 2) 리프 페이지의 모든 로우(=데이터)는 인덱스 키 칼럼 순으로 물리적으로 정렬되어 저장 ☞ 클러스터형 인덱스는 테이블당 한 개만 생성 가능.

2. 전체 테이블 스캔과 인덱스 스캔

1) 전체 테이블 스캔

전체 테이블 스캔 방식 : 테이블에 있는 모든 데이터를 읽어 가며 조건에 맞으면 추출하고 조건에 맞지 않으면 버림. ▷ 장시간 소요 가능성 있음.
Oracle : 테이블의 고수위 마크(HWM, High Water Mark) 아래의 모든 블록을 읽음 (고수위 마크 : 테이블에 데이터가 쓰여졌던 블록 상의 최상위 위치)
전체 테이블 스캔 방식 선택사유
- SQL문에 조건이 존재하지 않는 경우
- 주어진 조건에 사용 가능한 인덱스가 존재하는 않는 경우
- 옵티마이저의 취사선택
- 기타 (병렬처리 방식)

2) 인덱스 스캔

인덱스 스캔 : 인덱스를 구성하는 칼럼의 값을 기반으로 데이터를 추출하는 액세스 기법.
(트리 기반 인덱스) 인덱스의 리프 블록은 인덱스 구성하는 칼럼과 레코드 식별자로 구성.
인덱스는 인덱스 구성 칼럼의 순서로 정렬.
종류
- 1) 인덱스 유일 스캔 : 유일 인덱스(Unique Index)를 사용하여 단 하나의 데이터를 추출하는 방식.
- 2) 인덱스 범위 스캔 : 인덱스를 이용하여 한 건 이상의 데이터를 추출하는 방식.
- 3) 인덱스 역순 범위 스캔 : 리프 블록의 양방향 링크를 이용하여 내림 차순으로 데이터를 읽는 방식. 최댓값 찾기 용이.
- 4) 기타 : 인덱스 전체 스캔(Index Full Scan), 인덱스 고속 전체 스캔(Fast Full Index Scan), 인덱스 스킵 스캔(Index Skip Scan) 등

3) 전체 테이블 스캔과 인덱스 스캔 방식의 비교

인덱스 스캔 방식 : 적절한 인덱스가 존재할 때만 이용 가능 ↔ 전체 테이블 스캔 방식 : 항상 이용 가능
한번의 I/O 요청에 한 블록씩 데이터를 읽음 (데이터 위치 파악) ↔ 여러 블록을 한꺼번에 읽음.

3절 조인 수행 원리

조인 : 조인이란 두 개 이상의 테이블을 하나의 집합으로 만드는 연산.
세 개의 테이블 있을 때, 동시에 조인이 수행되는 것은 아님.
조인 단계별로 다른 조인 기법을 사용할 수 있음.

1. NL Join

NL Join : 프로그래밍에서의 중첩된 반복문과 유사한 방식으로 조인을 수행
반복문의 외부에 있는 테이블 : 선행 테이블 or 외부 테이블(Outer Table)
반복문의 내부에 있는 테이블 : 후행 테이블 or 내부 테이블(Inner Table)
FOR 선행 테이블 읽음 → 외부 테이블(Outer Table) FOR 후행 테이블 읽음 → 내부 테이블(Inner Table) (선행 테이블과 후행 테이블 조인)
작업 방법
- 1) 선행 테이블에서 주어진 조건을 만족하는 행을 찾음
- 2) 선행 테이블의 조인 키 값을 가지고 후행 테이블에서 조인
- 3) 선행 테이블의 조건을 만족하는 모든 행에 대해 1번 작업 반복
조인이 성공 시 즉시 조인 결과를 사용자에게 보여 줄 수 있어 온라인 프로그램에 적합.

2. Sort Merge Join

Sort Merge Join : 조인 칼럼을 기준으로 데이터를 정렬하여 조인.
NL Join : 주로 랜덤 액세스 방식으로 데이터를 읽음 ↔ Sort Merge Join : 주로 스캔 방식으로 읽음.
데이터가 많아 메모리에서 모든 정렬 작업을 수행하기 어려울 때, 임시 영역(디스크)을 사용해 성능 하락의 우려.
대체로 CPU 작업 위주로 처리하는 Hash Join이 성능상 유리 but! Sort Merge Join은 Hash Join과는 달리 동등 조인 뿐만 아니라 비동등 조인에 대해서도 조인 작업이 가능.
프로세스
- 1) 선행 테이블에서 주어진 조건을 만족하는 행을 찾음
- 2) 선행 테이블의 조인 키를 기준으로 정렬 작업을 수행 (1~2 조건을 만족하는 모든 행에 대해 반복.)
- 3) 후행 테이블에서 주어진 조건을 만족하는 행을 찾음.
- 4) 후행 테이블의 조인 키를 기준으로 정렬 작업을 수행 (3~ 4 후행 테이블의 조건을 만족하는 행에 대해 반복)
- 5) 정렬된 결과를 이용하여 조인을 수행. 성공 시 추출버퍼에 넣음 (추출버퍼 : SQL문의 실행결과를 보관하는 버퍼. 운반단위, Array Size, Prefetch Size라고도 함.)
조인 칼럼의 인덱스를 사용하지 않으므로 인덱스가 존재하지 않을 경우에도 사용 가능.

3. Hash Join

조인을 수행할 테이블의 조인 칼럼을 기준으로 해쉬 함수를 수행하여 서로 동일한 해쉬 값을 갖는 것들 사이에서 실제 값이 같은지를 비교하며 조인을 수행.
NL Join의 랜덤 액세스 문제점과 Sort Merge Join의 문제점인 정렬 작업의 부담을 해결하기 위한 대안.
프로세스
- 1) 선행 테이블에서 주어진 조건을 만족하는 행을 찾음
- 2) 선행 테이블의 조인 키를 기준으로 해쉬 함수를 적용하여 해쉬 테이블을 생성 (조인 칼럼과 SELECT 절에서 필요로 하는 칼럼도 함께 저장됨. | 1~ 2 선행 테이블의 조건을 만족하는 모든 행에 대해 반복)
- 3) 후행 테이블에서 주어진 조건을 만족하는 행을 찾음
- 4) 후행 테이블의 조인 키를 기준으로 해쉬 함수를 적용하여 해당 버킷을 찾음 (조인 키를 이용해 실제 조인될 데이터를 찾음)
- 5) 조인 성공 시, 추출버퍼에 넣음 (3~5 작업을 후행 테이블의 조건을 만족하는 모든 행에 대해서 반복)
조인 칼럼의 인덱스를 사용하지 않으므로 인덱스가 존재하지 않을 경우에도 사용 가능.
동등 조인에서만 사용 가능.
조인 작업을 수행하기 위해 해쉬 테이블을 메모리에 생성해야 함. ▷ 해쉬 테이블의 크기가 메모리 용량보다 더 커지면 임시 영역(디스크)에 해쉬 테이블 저장. ▷ 결과 행의 수가 적은 테이블을 선행 테이블로 사용 권장.
선행 테이블을 이용하여 먼저 해쉬 테이블을 생성한다고 해서 선행 테이블을 Build Input. 후행 테이블은 만들어진 해쉬 테이블에 대해 해쉬 값의 존재여부를 검사한다고 해서 Prove Input이라고 함.

참고

Data On Air

23.03.05 작성 시작 및 완료.
24.03.05 2024 이기적 SQLD 강의 반영.