3장 SQL 기본 및 활용

Updated: March 06, 2024

SQL 기본

관계형 데이터베이스(Relation Database)

　관계형 데이터베이스는 릴레이션(Relation)과 릴레이션의 조인 연산을 통해서 합집합, 교집합, 차집합 등을 만들 수 있다. 데이터베이스는 데이터를 어떠한 형태의 자료구조로 사용하느냐에 따라서 나누어진다. 데이터베이스의 종류는 계층형, 네트워크형 데이터베이스, 관계형 데이터베이스 등이 있다.
　계층형 데이터베이스는 트리(Tree) 형태의 자료구조에 데이터를 저장하고 관리하며, 네트워크는 오너(Owner)와 멤버(Member) 형태로 데이터를 저장한다. 계층형 데이터베이스는 1대N 관계를 표현한다.
　네트워크 데이터베이스는 1대N과 함께 M대N 표현도 가능하다.
　관계형 데이터베이스는 릴레이션에 데이터를 저장하고 관리하고 릴레이션을 사용해서 집합 연산과 관계 연산을 할 수 있다.
　데이터베이스 관리 시스템(DataBase Management System)은 계층형 데이터베이스, 네트워크 데이터베이스, 관계형 데이터베이스 등을 관리하기 위한 소프트웨어를 의미하며, DBMS라고 한다.
　관계형 데이터베이스의 특징은 릴레이션을 사용해서 집합 연산과 관계 연산을 할 수 있다.

집합 연산
- 합집합(Union): 두 개의 릴레이션을 하나로 합하는 것이다. 중복된 형(튜플)은 한 번만 조회된다.
- 차집합(Diference): 본래 릴레이션에는 존재하고 다른 릴레이션에는 존재하지 않는 것을 조회한다.
- 교집합(Intersection): 두 개의 릴레이션 간에 공통된 것을 조회한다.
- 곱집합(Cartesian product): 각 릴레이션에 존재하는 모든 데이터를 조합하여 연산한다.
관계 연산
- 선택 연산(Selection): 릴레이션에서 조건에 맞는 형(튜플)만을 조회한다.
- 투명 연산(Projection): 릴레이션에서 조건에 맞는 속성만을 조회한다.
- 결합 연산(Jion): 여러 릴레이션에서 공통된 속성을 사용해서 새로운 릴레이션을 만들어 낸다.
- 나누기 연산(Division): 기준 릴레이션에서 나누는 릴레이션이 가지고 있는 속성과 동일한 값을 가지는 형(튜플)을 추출하고 나누는 릴레이션의 속성을 삭제한 후 중복된 행을 제거하는 연산이다.

　관계형 데이터베이스는 릴레이션에 데이터를 저장하고 릴레이션을 사용해서 집합 연산 및 관계 연산을 지원하여 다영한 형태로 데이터를 조회할 수 있다. 릴레이션은 최종적으로 데이터베이스 관리 시스템에서 테이블(Table)로 만들어 진다.
　기본키(Primary Key)는 하나의 테이블에서 유일성(Unique)과 최소성, Not Null을 만족하면서 해당 테이블을 대표하는 것이다. EMP 테이블에서는 사원번호가 기본키가 된다. 테이블은 행과 칼럼으로 구성된다. 그중에서 행(ROW)은 하나의 테이블에 저장되는 값으로 튜플(Tuple)이라고도 한다. 칼럼(Column)은 어떤 데이터를 저장하기 위한 필드(Field)로 속성(Attribute)이라고도 한다. 외래키(Foreign key)는 다른 테이블을 기본키로 참조(조인)하는 칼럼이다. 외래키는 관계 연산 중에서 결합 연산(Join)을 하기 위해서 사용한다.

SQL(Structed Query Language) 종류

　SQL은 관계형 데이터베이스에 대해서 데이터의 구조를 정의, 데이터 조작, 데이터 제어 등을 할 수 있는 절차형 언어이다. 관계형 데이터베이스는 데이터베이스를 연결하고 SQL문을 사용하여 데이터베이스를 누구나 쉽게 사용할 수 있도록 한다.
　SQL은 데이터 정의, 데이터 조작, 데이터 제어 등의 기능을 지원한다.

DDL(Data Definition Language)
- 관계형 데이터베이스의 구조를 정의하는 언어이다.
- CREATE, ALTER, DROP, RENAME, TRUNCATE문이 있다.
DML(Data Manipulation Language)
- 테이블에서 데이터를 입력, 수정, 삭제, 조회한다.
- INSERT, UPDATE, DELETE, SELECT문이 있다.
DCL(Data Control Language)
- 트랜잭션을 제어하는 명령어이다.
- COMMIT, ROLLBACK, SAVEPOINT문이 있다.

　DDL문은 데이터베이스 테이블을 생성하거나 변경, 삭제하는 것으로 데이터를 저장할 구조를 정의하는 언어이다. DML은 데이터 구조가 DDL로 정의되면 해당 데이터 구조에 데이터를 입력하거나 수정, 삭제, 조회할 수 있다. DCL은 DDL로 정의된 구조에 어떤 사용자가 접근할 수 있는지 권한을 부여하는 것이다. 작업의 순서를 보면 데이터베이스의 사용자에게 권한을 부여하고 권한이 부여되면 DDL로 데이터 구조를 정의한다. 데이터 구조가 정의되면 데이터를 입력한 후에 개발자 및 사용자가 그 데이터를 조회하는 것이다.
　트랜잭션(Transaction)은 데이터베이스의 작업을 처리하는 단위이다.

원자성(Atomicity)
- 트랜잭션은 데이터베이스 연산의 전부가 실행되거나 전혀 실행되지 않아야 한다.(ALL OR NOTHING)
- 트랜잭션의 처리가 완전히 끝나지 않았을 경우는 실행되지 않은 상태와 같아야 한다.
일관성(Consistency)
- 트랜잭션 실행 결과로 데이터베이스의 상태가 모순되지 않아야 한다.
- 트랜잭션 실행 후에도 일관성이 유지되어야 한다.
고립성(Isolation)
- 트랜잭션 실행 중에 생성하는 연산의 중간결과는 다른 트랜잭션이 접근할 수 없다.
- 부분적인 실행 결과를 다른 트랜잭션이 볼 수 없다.
영속성(Durability)
- 트랜잭션이 그 실행을 성공적으로 완료하면 그 결과는 영구적 보장이 되어야 한다.

　개발자가 작성한 SQL문은 3단계를 걸쳐서 실행된다. SQL문의 문법을 검사하고 구문 분석을한다. 구문 분석 이후에 SQL을 실행한다. SQL이 실행되면 데이터를 인출하게 된다.

파싱(Parsing): SQL문의 문법을 확인하고 구문 분석한다. 구문 분석한 SQL문은 Library Cache에 저장한다.
실행(Execution): 옵티마이저(Optimizer)가 수립한 실행 계획에 따라 SQL을 실행한다.
인출(Fetch): 데이터를 읽어 전송한다.

DDL(Data Definition Language)

　데이터베이스를 사용하기 위해서는 테이블을 먼저 생성해야 한다. 테이블 생성은 Create Table문을 사용하고 테이블 변경은 Alter Table문을 사용한다. 마지막으로 생성된 테이블을 삭제하고 싶을 때는 Drop Table문을 사용하면 된다.

Create Table
- 새로운 테이블을 생성한다.
- 테이블을 생성할 때 기본키, 외래키, 제약사항 등을 설정할 수 있다.
Alter Table
- 생성된 테이블을 변경한다.
- 칼럼을 추가하거나 변경, 삭제할 수 있다.
Drop Table
- 해당 테이블을 삭제한다.
- 테이블의 데이터 구조뿐만 아니라 저장된 데이터도 모두 삭제된다.

　테이블을 생성하는 방법은 여러 가지가 있다. 먼저 아주 간단한 테이블 생성 방법을 보자.

Create Table EMP (
  empno number(10) primary key,
  ename varchar2(20),
  sal   number(6),
);

Create Table
- () 사이에 칼럼을쓰고 마지막은 세미클론으로 끝난다.
칼럼 정보
- 테이블에 생성되는 칼럼 이름과 데이터 타입을 입력한다.
- 칼럼 이름은 영문, 한글, 숫자 모두 가능한다.
데이터 타입
- number는 칼럼의 데이터 타입을 숫자형 타입으로, varchar2는 가변 길이 문자열로 지정할 때 사용한다.
- char는 칼럼의 데이터 타입을 고정된 크기의 문자로 지정할 때, date는 날짜형 타입으로 지정할 때 사용된다.
기본키
- 기본키를 지정할 때 칼럼 옆에 primary key를 입력한다.

　기본키, 외래키, 기본값, not null 등은 테이블을 생성할 때 지정할 수 있다.

Create Table EMP (
  empno number(10),
  ename varchar2(20),
  sal   number(10, 2) default 0,
  deptno varchar2(4) not null,
  createdate date default sysdate,
  constraint emppk primary key(empno),
);

constraint를 사용하여 기본키(empno)와 기본키의 이름(emppk)을 지정할 수 있다.
만약 두 개의 기본키를 지정하고자 한다면 “constraint emppk primary key(empno, ename)”으로 지정하면 된다.
sal 칼럼은 number(10, 2)로 지정했다. 이것은 소수점 둘째 자리까지 저장하게 된다.
Oracle 데이터베이스에서 “sysdate”는 오늘 날짜를 조회한다. 이를 default 옵션을 사용해서 오늘 날짜를 기본값으로 지정할 수 있다.

　다음 코드를 보자.

Create Table DEPT (
  depno varchar2(4) primary key,
  deptname varchar2(20),
);

Create Table EMP (
  empno number(10),
  ename varchar2(20),
  sal   number(10, 2) default 0,
  deptno varchar2(4) not null,
  createdate date default sysdate,
  constraint emppk primary key(empno),
  constraint deptfk foreign key(deptno) references dept(deptno),
);

외래키(foreign key)를 지정하려면, 먼저 마스터 테이블이 생성되어야 한다. 즉, 사원과 부서 테이블에서는 부서가 마스터 테이블이 된다.
사원 테이블에서 부서 테이블의 deptno를 참조해야 하는 것이다.
EMP테이블을 생성할 때 constraint를 사용하여 외래키 이름인 deptfk를 입력 후 외래키를 생성한다.

　테이블을 생성할 때 CASCADE 옵션을 사용할 수 있다. CASCADE 옵션은 참조 관계(기본키와 외래키 관계)가 있을 경우 참조되는 데이터를 자동으로 반영할 수 있는 것이다.

Create Table DEPT (
  depno varchar2(4) primary key,
  deptname varchar2(20),
);

INSERT INTO DEPT VALUES ('1000', '인사팀');
INSERT INTO DEPT VALUES ('1001', '총무팀');

Create Table EMP (
  empno number(10),
  ename varchar2(20),
  sal   number(10, 2) default 0,
  deptno varchar2(4) not null,
  createdate date default sysdate,
  constraint emppk primary key(empno),
  constraint deptfk foreign key(deptno) references dept(deptno) ON DELETE CASCADE,
);

INSERT INTO EMP VALUES (100, '임베스트', 1000, '1000', sysdate);
INSERT INTO EMP VALUES (101, '을지문덕', 2000, '1001', sysdate);

DELETE FROM DEPT WHERE DEPTNO = '1000';
SELECT * FROM EMP;

먼저, 마스터 테이블을 생성하고 데이터를 입력한다.
EMP 테이블을 생성하고 테이터를 입력한다. 단, EMP 테이블을 생성할 때, “ON DELETE CASCADE” 옵션을 사용한다.
EMP 테이블을 생성하고 데이터 두 개를 입력한다.
DEPT 테이블에서 DEPTNO가 ‘1000’번인 인사팀을 삭제했다.
그리고 EMP 테이블에서 데이터를 조회한 결과, DEPTNO가 ‘1000’번이었던 ‘임베스트’데이터도 자동으로 삭제된 것을 알 수 있다.

　즉, ON DELETE CASCADE 옵션은 자신이 참조하고 있는 테이블의 데이터가 삭제되면 자동으로 자신도 삭제되는 옵션이다. ON DELETE CASCADE 옵션을 사용하면 참조 무결성을 준수할 수 있다. 참조 무결성이란, 마스터 테이블에는 해당 부서번호가 없는데, 슬레이브 테이블에는 해당 부서번호가 있는 경우를 참조 무결성 위배로 볼 수 있다.
　ALTER TABLE문을 통해 테이블 변경을 할 수 있으며, 테이블명 변경, 칼럼 추가, 변경, 삭제 등을 할 수 있다.

ALTER TABLE EMP
  RENAME TO NEW EMP;

테이블명 변경은 ALTER TABLE ~ RENAME TO문을 사용하면 된다.
```
ALTER TABLE EMP
ADD (age number(2) default 1);
```
생성된 EMP 테이블에 ALTER TABLE ~ ADD 문을 사용해서 칼럼을 추가한다.
```
ALTER TABLE EMP
MODIFY (ename varchar2(40) not null);
```
칼럼의 변경은 ALTER TABLE ~ MODIFY문을 사용하면 된다.
칼럼 변경을 통해 데이터 타입을 변경하거나 데이터의 길이를 변경할 수 있다.
칼럼을 변경할 때 제약조건을 설정할 수도 있다.
칼럼의 데이터 타입을 변경할 때 기존 데이터가 있는 경우 에러가 발생한다. 예를 들어 숫자 타입이고 숫자 데이터가 저장되어 있는데 문자형 데이터 타입으로 변경하면 에러가 발생하는 것이다.
```
ALTER TABLE EMP
DROP COLUMN age;
```
칼럼에 대한 삭제는 ALTER TABLE ~ DROP COLUMN문으로 삭제한다.
```
ALTER TABLE EMP
RENNAME COLUMN ename TO new_ename;
```
칼럼명 변경은 ALTER TABLE ~ RENAME COLUMN ~ TO문으로 변경할 수 있다.

　테이블 삭제는 DROP TABLE문을 사용해서 삭제할 수 있다. DROP TABLE은 테이블의 구조와 데이터를 모두 삭제한다. DROP TABLE에서 “CASCADE CONSTRAINT” 옵션을 사용할 수 있다. “CASCADE CONSTRAINT” 옵션은 해당 테이블의 데이터를 외래키로 참조한 슬레이브 테이블과 관계된 제약사항도 삭제할 때 사용된다.
　뷰(View)란 테이블로 부터 유도된 가상의 테이블이다. 실제 데이터를 가지고 있지 않고 테이블을 참조해서 원하는 칼럼만을 조회할 수 있게 한다. 뷰는 데이터 딕서너리(Data Dictionary)에 SQL문 형태로 저장하되 실행 시에 참조된다. 뷰의 특징은 다음과 같다.

참조한 테이블이 변경되면 뷰도 변경된다.
뷰의 검색은 참조한 테이블과 동일하게 할 수 있지만, 뷰에 대한 입력, 수정, 삭제에는 제약이 있다.
특정 칼럼만 조회시켜서 보안성을 향상시킨다.
한번 생성된 뷰는 변경할 수 없고 원하면 삭제 후 재생성해야 한다.
ALTER문을 사용해서 변경할 수 없다.

　뷰를 생성할 때 CREATE VIEW문을 사용하며 이때 참조할 테이블은 SELECT문으로 지정한다.

CREATE VIEW T_EMP AS
  SELECT * FROM EMP;

뷰의 조회는 SELECT문을 사용해서 일반 테이블처럼 조회한다.
뷰의 삭제는 TROP VIEW를 사용한다. 뷰를 삭제했다고 해서 참조했던 테이블이 삭제되지는 않는다.

　뷰의 장단점은 다음과 같다.

장점
- 특정 칼럼만 조회할 수 있기 때문에 보안 기능이 있다.
- 데이터 관리가 간단하다.
- SELECT문이 간단해진다.
- 하나의 테이블에 여러 개의 뷰를 생성할 수 있다.
단점
- 뷰는 독자적인 인덱스를 만들 수 없다.
- 삽입, 수정, 삭제 연산이 제약된다.
- 데이터 구조를 변경할 수는 없다.

DML(Data Manipulation Language)

　INSERT문은 테이블에 데이터를 입력하는 DML문이다.

INSERT INTO table (column1, column2, ...) VALUES (expression1, expression2, ...);

테이블을 입력할 때 문자열을 입력하는 경우에는 작은따옴표(‘ ‘)를 사용해야 한다.
만약 특정 테이블의 모든 칼럼에 대한 데이터를 삽입하는 경우에는 칼럼명을 생략할 수 있다.
모든 칼럼에 데이터를 입력하야 한다.
칼럼명을 생략할 수 있다.
주의사항은 INSERT문을 실행했다고 데이터 파일에 저장되는 것은 아니다. 최종적으로 데이터를 저장하려면 TCL문인 Commit을 실행해야 한다.
만약 Auto Commit(Set auto commit on)으로 설정된 경우에는 Commit을 실행하지 않아도 저장된다.

　SELECT문을 사용하여 데이터를 조회해서 해당 테이블에 바로 삽입할 수 있다. 단, 입력되는 태이블은 사전에 생성되어 있어야 한다.

INSERT INTO EMPT_TEST
  SELECT * FROM DEPT;

　데이터베이스에 데이터를 입력하면 로그파일(Log file)에 그 정보를 기록한다. Check point라는 이벤트가 발생하면 로그파일의 데이터를 데이터 파일에 저장한다. Nologging 옵션은 로그파일의 기록을 최소화시켜서 입력 시 성능을 향상시키는 방법이다. Nologging 옵션은 Buffer Cache라는 메모리 영역을 생략하고 기록한다.
　입력된 데이터의 값을 수정하려면, UPDATE문을 사용한다. UPDATE문을 사용하여 원하는 조건으로 데이터를 검색해서 해당 데이터를 수정할 수 있다. 만약, UPDATE문에 조건문을 입력하지 않으면 모든 데이터가 수정되므로 유의해야 한다.

UPDATE EMP
    SET ENAME = '조조'
  WHERE EMPNO = 100;

UPDATE문에서 주의사항은 데이터를 수정할 때 조건절에서 검색되는 행 수만큼 수정된다는 것이다.

　DELETE문은 원하는 조건을 검색해서 해당되는 행을 삭제한다. DELETE문에 조건문을 입력하지 않으면 모든 데이터가 삭제된다. 즉, 테이블에 있는 모든 데이터가 삭제되는 것이다. DELETE문으로 데이터를 삭제한다고 해서 테이블의 용량이 초기화되지는 않는다.

DELETE FROM EMP
    WHERE EMPNO=100;

　테이블의 모든 데이터를 삭제할 경우를 보자.

DELETE FROM 테이블명
- 테이블의 모든 데이터를 삭제한다.
- 데이터가 삭제되어도 테이블의 용량은 감소하지 않는다.
TRUNCATE TABLE 테이블명
- 테이블의 모든 데이터를 삭제한다.
- 데이터가 삭제되면 테이블의 용량은 초기화된다.

　테이블에 입력된 데이터를 조회하기 위해서 SELECT문을 사용한다. SELECT문은 특정 칼럼이나 특정 행만 조회할 수 있다.

SELECT *
FROM EMP
WHERE 사원번호 = 1000;

　SELECT 칼럼 지정은 다음과 같다.

SELECT EMPNO, ENAME FROM EMP;
SELECT * FROM EMP;
SELECT ENAME ‘님’ FROM EMP;
- EMP 테이블의 모든 행에서 ENAME 칼럼을 조회한다.
- 단, ENAME 칼럼 뒤에 ‘님’이라는 문자를 결합한다.

　SELECT문을 사용할 때 ORDER BY를 같이 사용할 수 있다. ORDER BY는 데이터를 오름차순(Ascending) 혹은 내림차순(Descending)으로 출력한다. ORDER BY가 정렬을 하는 시점은 모든 실행이 끝난 후에 데이터를 출력하 주기 바로 전이다. ORBER BY는 정렬을 하기 때문에 데이터베이스 메모리를 많이 사용하게 된다. 즉, 대량의 데이터를 정렬하게 되면 정렬로 인한 성능 저하가 발생한다.
　Oracle 데이터베이슨 정렬을 위해서 메모리 내부에 할당된 SORT_AREA_SIZE를 사용한다. 만약 SORT_AREA_SIZE가 너무 작으면 성능 저하가 발생한다. 정렬을 회피하기 위해서 인덱스(Index)를 생성할 때 사용자가 원하는 형태로 오름차순 혹은 내림차순으로 생성해야 한다. 특별한 지정이 없으면 ORBER BY는 오름차순으로 정렬한다.

SELECT * FROM EMP
    ORDER BY ENAME, SAL DESC;

ENAME 부분은 ENAME ASC와 같다.

　정렬은 Oracle 데이터베이스에 부하를 주므로, 인덱스를 사용해서 Order by를 회피할 수 있다. 다음을 보자.

CREATE TABLE EMP (
  EMPNO number(10) primary key,
  ENAME varchar2(20).
  SAL number(10),
);

INSERT INTO EMP VALUES(1000, '임베스트', 20000);
INSERT INTO EMP VALUES(1001, '조조', 20000);
INSERT INTO EMP VALUES(1002, '관우', 20000);

위와 같이 데이터를 입력하고 SELECT문을 실행하면 EMPNO로 오름차순 정렬되어 조회된다. 그 이유는 EMPNO가 기본키이기 때문에 자동으로 오름차순 인덱스가 생성된다.

SELECT /* + INDEX_DESC(A) + */
FROM EMP A;

위를 보면 ‘/* + INDEX_DESC(A) + *‘를 사용했다. EMP테이블에 생성된 인덱스를 내림차순으로 읽게 지정한 것이다. 따라서 SELECT문에 “ORDER BY EMPNO DESC”를 사용하지 않았다.

　DISTINCT문은 칼럼명 앞에 지정하여 중복된 데이터를 한 번만 조회하게 한다.

SELECT DISTINCT DEPTNO FROM EMP
ORDER BY DEPTNO;

Distinct를 사용하면 DEPTNO 값이 중복되지 않는다.

　Alias(별칭)은 테이블명이나 칼렴명이 너무 길어서 간단하게 할 때 사용한다.

SELECT ENAME AS "이름" FROM EMP a
WHERE a.EMPNO = 1000;

WHERE문 사용

　WHERE문이 사용할 수 있는 연산자는 비교 연산자, 부정 비교 연산자, 논리 연산자, SQL 연산자, 부정 SQL 연산자가 있다.

비교 연산자
- =, <, <=, >, >=
부정 비교 연산자
- !=
- ^=, < > : 같지 않은 것을 조회한다.
- NOT 칼렴명 =
- NOT 칼럼명 > : 크지 않은 것을 조회한다.
SQL 연산자
- LIKE ‘%비교 문자열%’: 비교 문자열을 조회한다. ‘%’는 모든 값을 의미한다.
- BETWEEN A AND B : A와 B사이의 값을 조회한다.
- IN (list) : OR을 의미하며 list 값 중에 하나만 일치해도 조회한다.
- IS NULL : NULL 값을 조회한다.
SQL 부정 연산자
- NOT BETWEEN A AND B
- NOT IN(list)
- IS NOT NULL

　LIKE문은 와일드카드를 사용해서 데이터를 조회할 수 있다. LIKE문에 와일드카드를 사용하지 않으면 =와 같다.

%: 어떤 문자를 포함한 모든 것을 조회한다. 예를 들어 ‘조%’는 ‘조’로 시작하는 모든 문자를 조회한다.
_: 단일 문자 하나를 의미한다.

SELECT * FROM EMP
WHERE ENAME LIK 'test%'

　BETWEEN문은 지정된 범위에 있는 값을 조회한다. IN문은 “OR”의 의미를 가지고 있어서 하나의 조건만 만족해도 조회가 된다. “JOB IN (‘CLECK’, ‘MANAGER’)”처럼 괄호를 사용하여 원하는 데이터를 칼럼명에 대응되도록 입력함으로써, IN문으로 여러 개의 칼럼에 대한 조건을 지정할 수 있다.
　NULL은 모르는 값, 값의 부재를 의미한다. NULL과 숫자 혹은 날짜를 더하면 NULL이 된다. NULL과 어떤 값을 비교할 때, ‘알 수 없음’이 반환된다.
　NULL을 조회할 경우는 IS NULL을 사용하고 NULL 값이 아닌 것을 조회할 때는 IS NOT NULL을 사용한다. NULL 관련 함수는 다음과 같다.

NVL
- NULL이면 다른 값으로 바뀌는 함수이다.
- ‘NVL(MGR,0)’는 MGR 칼럼이 NULL이면 0으로 바꾼다.
NVL2
- NVL 함수와 DECODE 함수를 하나로 만든 것이다.
- ‘NVL2(MGR, 1, 0)’은 MGR칼럼이 NULL이 아니면 1을, NULL이면 0을 반환한다.
NULLIF
- 두 개의 값이 같으면 NULL을, 같지 않으면 첫 번째 값을 반환한다.
- ‘NULLIF(exp1, exp2)’
COALESCE
- NULL이 아닌 최초의 인자 값을 반환한다.
- ‘COALESCE(exp1, exp2, …)’은 exp1이 NULL이 아니면 exp1의 값을 그렇지 않으면 그 뒤의 값의 NULL 여부를 판단하여 값을 반환한다.

GROUP 연산

　GROUP BY는 테이블에서 소규모행을 그룹화하여 합계, 평균, 최대값, 최솟값 등을 계산할 수 있다. HAVING구에 조건문을 사용한다. Grouping된 결과에 대한 조건문을 사용한다. ORDER BY를 사용해서 정렬을 할 수 있다.

SELECT DEPTNO, SUM(SAL)
  FROM EMP
GROUP BY DEPTNO;

GROUP BY에서 DEPTNO로 그룹을 만들고 그룹별 합계를 계산하라는 뜻이다.

　GROUP BY에 조건절을 사용하려면 HAVING을 사용해야 한다. 만약 WHERE절에 조건문을 사용하게 되면 조건을 충족하지 못하는 데이터들은 GROUP BY 대상에서 제외된다.

SELECT DEPTNO, SUM(SAL)
  FROM EMP
GROUP BY DEPTNO
HAVING SUM(SAL) > 1000;

　집계 함수의 종류는 다음과 같다.

COUNT(): 행 수를 조회한다.
- COUNT(*)는 NULL값을 포함한 모든 행 수를 계산한다.
- COUNT(칼럼명)는 NULL값을 제외한 행 수를 계산한다.
SUM()
AVG()
MAX(), MIN()
STDDEV(): 표준편차를 계산한다.
VARIAN(): 분산을 계산한다.

SELECT문 실행 순서

　SQL의 실행 순서는 결과로 조회된 데이터를 이해하는 데 아주 중요한 요소이다. SELECT문의 실행 순서는 FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY 순으로 실행된다.

명시적(Explict) 형변환과 임시적(Implicit) 형변화

　형변환이라는 것은 두 개의 데이터의 타입(형)이 일치하도록 변환하는 것이다. 예를 들어 숫자와 문자열의 비교, 문자열과 날짜형의 비교와 같이 데이터 타입이 불일치할 때 발생한다. 형변환은 명시적 형변환과 임시적 형변환이 있다. 명시적 형변환은 형변환 변수를 사용해서 데이터 타입을 일치시키는 것으로 개발자가 SQL을 사용할 때 형변환 함수를 사용해야 한다.

TO_NUMBER(문자열): 문자열을 숫자로 변환한다.
TO_CHAR(숫자, 혹은 날짜, [FORMAT]): 숫자 혹은 날짜를 지저된 FORMAT의 문자로 변환한다.
TO_DATE(문자열, FORMAT): 문자열을 지정된 FORMAT의 날짜형으로 변환한다. 　임시적 형변환은 개발자가 형변환을 하지 않은 경우 데이터베이스 관리 시스템이 자동으로 형변환하는 것을 의미한다.

내장형 함수(BUILT-IN Function)

　모든 데이터베이스는 SQL에서 사용하 수 있는 내장형 함수를 가지고 있다. 내장형 함수는 데이터베이스 관리 시스템 벤더별로 약간의 차이가 있지만, 거의 비슷한 방법으로 사용이 가능하다. 내장형 함수로는 형변환 함수, 문자열 및 숫자형 함수, 날짜형 함수가 있다.
　DUAL 테이블은 Oracle 데이터베이스에 의해서 자동으로 생성되는 테이블이다. Oracle 데이터베이스 사용자가 임시로 사용할 수 있는 테이블로 내장형 함수를 실행할 때도 사용할 수 있다. Oracle 데이터베이스의 모든 사용자가 사용할 수 있다.
　DUAL 테이블에 문자형 내장형 함수를 사용하면 다음과 같다.

ASCII 함수는 문자에 대한 ASCII 코드 값을 알려준다. ASCII 코드는 대문자 A를 기준으로 A(65), B(66) 등의 값이다.
SUBSTR 함수는 지정된 위치의 문자열을 자르는 함수이고 LENGTH 함수, LEN 함수는 문자열의 길이를 계산한다.
LTRIM 함수를 사용하면 문자열의 왼쪽 공백을 제거할 수 있다.
또한 함수를 중첩해서 사용해도 된다.
날짜형 함수 중에서 오늘 날짜를 구하기 위해서는 SYSDATE를 사용하면 된다.
만약, 해당 연도만 알고 싶다면, EXTRACT 함수를 사용한다.
TO_CHAR함수는 형변환 함수 중에서 가장 많이 사용하는 것으로 숫자나 날짜를 원하는 포멧의 문자열로 변환한다.
절대값을 계산하는 ABS 함수와 음수, 0, 양수를 구분하는 SIGN, 나머지를 계산하는 MOD등의 함수가 있다.

DECODE와 CASE문

　DECODE문으로 IF문을 구현할 수 있다. 즉, 특정 조건이 참이면 A, 거짓이면 B로 응답한다.

DECODE (EMPNO, 1000, 'TRUE', 'FALSE')

EMPNO를 1000과 비교해서 같으면 TRUE를 출력하고, 다르면 FALSE를 출력한다.

　CASE문은 IF~THEN~ELSE~END의 프로그래밍 언어처럼 조건문을 사용할 수 있다. 조건을 WHERE구에 사용한다. 해당 조건이 참이면 THEN이 실행되고 거짓이면 ELSE구가 실행된다.

CASE[expression]
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
  ...
  ELSE reesult
END

ROWNUM과 ROWID

　ROWNUM은 ORACLE 데이터베이스의 SELECT문 결과에 대해서 논리적인 일렬번호를 부여한다. ROWNUM은 조회되는 행 수를 제한할 때 많이 사용된다. ROWNUM은 화면에 데이터를 출력할 때 부여되는 논리적 순번이다. 만약 ROWNUM을 사용해서 페이지 단위를 출력하기 위해서는 인라인 뷰(Inline View)를 사용해야 한다.

SELECT *
FROM ( SELECT ROWNUM list, ENAME FROM EMP)
WHERE list <=5;

5건을 조회하기 위해서는 인라인 뷰를 사용하고 ROWNUM에 별칭을 사용한다. 　ROWID는 ORACLE 데이터베이스 내에서 데이터를 구분할 수 있는 유일한 값이다. ROWID는 SELECT문으로 확인할 수 있다. ROWID를 통해서 데이터가 어떤 데이터 파일, 어느 블록에 저장되어 있는 지 알 수 있다.

WITH구문

　WITH구문은 서브쿼리(Subquery)를 사용해서 임시 테이블이나 뷰처럼 사용할 수 있는 구문이다. 서브퀘러 블록에 별칭을 지정할 수 있다. 옵티마이저는 SQL을 인라인 뷰나 임시 테이블로 판단한다.

WITH viewData AS
  (SELECT * FROM EMP
    UNION ALL
  SELECT * FROM EMP
  )
  SELECT * FROM viewData WHERE EMPNO=1000;

DCL(Data Control Language)

　GRANT문은 데이터베이스 사용자에게 권한을 부여한다. 데이터베이스 사용을 위해서는 권한이 필요하며 연결, 수정, 삭제, 조회를 할 수 있다.

GRANT privieges ON object TO user;

privieges는 권한을 의미하며 object는 테이블명이다.
user는 Oracle 데이터베이스 사용자를 지정하면 된다.
권한(privieges)
- SELECT
- INSERT
- UPDATE
- DELETE
- REFERENCES
- ALTER
- INDEX
- ALL
WITH GRANT OPTION
- 특정 사용자에게 권한을 부여할 수 있는 권한을 부여한다.
- 권한을 A 사용자가 B에게 부여하고 B가 다시 C를 부여한 후에 권한을 취소(Revoke)하면 모든 권한이 회수된다.
ADMIN OPTION
- 테이블에 대한 모든 권한을 부여한다.
- 권한을 A 사용자가 B에게 부여하고 B가 다시 C를 부여한 후에 권한을 취소(Revoke)하면 B사용자 궈한만 회수된다.
```
GRANT SELECT, INSERT, UPDATE, DELETE
ON EMP
TO LINBEST WITH GRANT OPTION;
```

　REVOKE문은 데이터베이스 사용자에게 부여된 권한을 회수한다.

REVOKE privieges ON object FROM user;

TCL(Transaction Control Language)

　COMMIT은 INSERT, UPDATE, DELETE문으로 변경한 데이터를 데이터베이스에 반영한다. 변경 이전 데이터는 잃어버린다. 다른 모든 데이터베이스 사용자는 변경된 데이터를 볼 수 있다.
　COMMIT이 완료되면 데이터베이스 변경으로 인한 LOCK이 해제(UNLOCK)된다. COMMIT이 완료되면 다른 모든 데이터베이스 사용자는 변경된 데이터를 조작할 수 있다. COMMIT을 실행하면 하나의 트랜잭션 과정을 종료한다. Oracle 데이터베이스는 임시적 트랜잭션 관리를 한다. 즉, Oracle 데이터베이스로 트랜잭션을 시작하고 트랜잭션의 종료는 Oracle 데이터베이스 사용자가 COMMIT 혹은 ROLLBACK으로 처리해야 한다.
　ROLLBACK을 실행하면 데이터에 대한 변경 사용을 모두 취소하고 트랜잭션을 종료한다. INSERT, UPDATE, DELETE문의 작업을 모두 취소한다. 단, 이전에 COMMIT한 곳까지만 복구한다. ROLLBACK을 실행하면 LOCK이 해제되고 다른 사용자도 데이터베이스 행을 조작할 수 있다.
　SAVEPOINT는 트랜잭션을 작게 분할하여 관리하는 것으로 SAVEPOINT를 사용하면 지정된 위치 이후의 트랜잭션만 ROLLBACK 할 수 있다. SAVEPOINT의 지정은 SAVEPOINT <SAVEPOINT명>을 실행한다. 지정된 SAVEPOINT까지만 데이터 변경을 취소하고 싶은 경우는 “ROLLBACL TO <SAVEPOINT명>”을 실행한다. ROLLBACK을 실행하면 SAVEPOINT와 관계없이 데이터의 모든 변경사항을 저장하지 않는다.

SQL 활용

조인(Join)

　조인은 여러 개의 릴레이션을 사용해서 새로운 릴레이션을 만드는 과정이다. 조인의 가장 기본은 교집합을 만드는 것이다. 두 개의 테이블 간에 일치하는 것을 조인한다.
　EQUI 조인은 EMP 테이블과 DEPT 테이블에서 DEPTNO 칼럼을 사용하여 같은 것을 조인한다.
　INNER JOIN은 ON문을 사용해서 테이블을 연결한다.

SELECT * FROM EMP INNER JOIN DEPT
  ON EMP.DEPTNO = DEPT.DEPTNO
  AND EMP.ENAME LIKE '임%'
ORDER BY ENAME;

　INTERSECT 연산은 두 개의 테이블에서 교집합을 조회한다. 즉, 두 개 테이블에서 공통된 값을 조회한다.

SELECT DEPTNO FROM EMP
INTERSECT
SELECT DEPTNO FROM DEPT;

　Non-EQUI는 두 개의 테이블 간에 조인하는 경우 “=”을 사용하지 않고 >, <, >=, <= 등을 사용한다. 즉, Non-EQUI JOIN은 정확하게 일치하지 않는 것을 조인하는 것이다.
　OUTER JOIN은 두 개의 테이블 간에 교집합을 조회하고 한쪽 테이블에만 있는 데이터도 포함시켜 조회한다. 예를 들어 DEPT 테이블과 EMP 테이블을 OUTER JOIN하면 DEPTNO가 같은 것을 조회하고 DEPT 테이블에만 있는 DEPTNO도 포함시킨다. 이때 왼쪽 테이블에만 있는 행도 포함하면 LEFT OUTER JOIN이라고 하고 오른쪽 테이블의 행만 포함시키면 RIGHT OUTER JOIN이라고 한다. FULL OUTER JOIN은 LEFT OUTER JOIN과 RIGHT OUTER JOIN 모두를 하는 것이다. Oracle 데이터베이스에는 OUTER JOIN을 할 때 “(+)” 기호를 사용해서 할 수 있다.

SELECT * FROM DEPT, EMP
  WHERE EMP.DEPTNO (+)= DEPT.DEPTNO;

　CROSS JOIN은 조인 조건구 없이 2개의 테이블을 하나로 조인한다. 조인구가 없기 때문에 카테시안 곱이 발생한다. 예를 들어 행이 14개 있는 테이블과 행이 4개 있는 테이블을 조인하면 56개의 행이 조회된다. CROSS JOIN은 FROM절에 “CROSS JOIN”구를 사용하면 된다.

SELECT * FROM EMP CROSS JOIN DEPT;

　UNION 연산은 두 개의 테이블을 하나로 만드는 연산이다. 즉, 2개의 테이블을 하나로 합치는 것이다. 주의사항은 두 개의 테이블의 칼럼 수, 칼럼의 데이터 형식이 모두 일치해야 한다. 만약 두 개의 테이블에 UNION 연산이 사용될 때 칼럼 수 혹은 데이터 형식이 다르면 오류가 발생한다. UNION 연산은 두 개의 테이블을 하나로 합치면서 중복된 데이터를 제거한다. 그래서 UNION은 정렬 과정을 발생시킨다.
　UNION ALL은 두개의 테이블을 하나로 합치는 것이다. UNION처럼 중복을 제거하거나 정렬을 유발하지 않는다.
　MINUS 연산은 두 개의 테이블에서 차집합을 조회한다. 즉, 먼저 쓴 SELECT문에는 있고 뒤에 쓰는 SELCT문에는 업는 집합을 조회하는 것이다. MS-SQL에서는 EXCEPT이다.

계층형 조회(CONNECT BY)

　계층형 조회는 Oracle에서 지원하는 것으로 계층형으로 데이터를 조회할 수 있다. 예를 들어 부장에서 차장, 차장에서 과장, 과장에서 대리, 대리에서 사원 순으로 트리형태의 구조를 위에서 아래로 탐색하면서 조회하는 것이다. 물론 역방향 조회도 가능하다. CONNECT BY는 트리 형태의 구조로 질의를 수행하는 것으로 START WITH구는 시작 조건을 의미하고 CONNECT BY PRIOR는 조인 조건이다. Root 노드로부터 하위 노드의 질의를 실행한다. 계층형 조회에서 MAX(LEVEL)을 사용하여 최대 계층 수를 구할 수 있다. 즉, 계층형 구조에서 마지막 Leaf Node의 계층값을 구한다.

SELECT MAX(LEVEL)
FROM Limbest.EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;

MAX(LEVEL)이 4이면 트리의 최대 깊이는 4이다.

SELECT LEVEL, EMPNO, MGR, ENAME
FROM Limbest.EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;

　계층형 조회 결과를 명확히 보기 위해서 LPAD 함수를 사용할 수 있다.
　CONNECT BY구는 순방향 조회와 역방향 조회가 있다. 순방향 조회는 부모 엔티티로부터 자식 엔티티를 찾아가는 검색을 의미하고, 역방향 조회는 반대이다.

서브쿼리(Subquery)

　Subquery는 SELECT문 내에 다시 SELECT문을 사용하는 SQL문이다. Subquery의 형태는 FROM구에 SELECT문을 사용하는 인라인 뷰(Inline View)와 SELECT문에 Subquery를 사용하는 스칼라 서브쿼리(Scala Subquery) 등이 있다. WHERE구에 SELECT문을 사용하면 서브쿼리라고 한다.
　서브쿼리는 반환하는 행 수가 한 개인 것과 여러 개인 것에 따라서 단일 행 서브쿼리와 멀티 행 서브쿼리로 분류된다. 단일 행 서브쿼리는 단 하나의 행만 반환하는 것으로 비교 연산자를 사용한다. 다중 행 서브쿼리는 여러 개의 행을 반환하는 것으로 IN, ANY, ALL, EXISTS를 사용해야 한다.
　다중 행 서브쿼리(Multi row Subquery)는 서브쿼리 결과가 여러 개의 행을 반환하는 것으로 다중 행 연산자를 사용해야 한다.

IN(Subquery)
- Main query의 비교저건이 Subquery의 결과 중 하나만 동일하면 참이 된다.
ALL(Subquery)
- Main query와 Subquery의 결과가 모두 동일하면 참이 된다.
- /< ALL: 최소값을 반환한다.
- /> ALL: 최대값을 반환한다.
ANY(Subquery)
- Main query의 비교조건이 Subquery의 결과 중 하나 이상 동일하면 참이된다.
- < ANY: 하나라도 크게 되면 참이 된다.
- > ANY: 하나라도 작게 되면 참이 된다.
EXISTS(Subquery)
- Main query와 Subquery의 결과가 하나라도 존재하면 참이 된다.

　스칼라 Subquery는 반드시 한 행과 한 칼럼만 반환하는 서브쿼리이다. 만약 여러 행이 반환되면 오류가 발생한다.
　연관 Subquery는 Subquery 내에서 Main query 내의 칼럼을 사용하는 것을 의미한다.

그룹 합수(Group Function)

　ROLLUP은 GROUP BY의 칼럼에 대해서 Subtotal을 만들어 준다. ROLLUP을 할 때 GROUP BY구에 칼럼이 두 개 이상 오면 순서에 따라서 결가가 달라진다.

SELECT DECODE(DEPTNO, NULL, '전체합계', DEPTNO),
       SUM(SAL)
  FROM EMP
  GROUP BY ROLLUP (DEPTNO);

DEPTNO에 대해서 GROUP BY로 급여합계를 계산하고 부서별 전체합계를 추가해서 계산했다. 즉, ROLLUP은 DEPTNO에 대해서 기존 GROUPBY와는 다르게 부서별 전체합계를 계산하게 된다.
DECODE문은 전체합계를 조회할 때 ‘전체합계’라는 문자를 출력하기 위해서 사용된다. DECODE문을 사용해서 DEPTNO가 NULL과 같으면 ‘전체합계’라는 문자를 출력하고 그렇지 않으면 부서번호를 출력한다.
```
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);
```
부서별, 직업별 ROLLUP을 실행하면 부서별 합계, 직업별 합계, 전체합계가 모두 조회된다.

　GROUPING 함수는 ROLLUP, CUBE, GROUPING SETS에서 생성되는 합계값을 구분하기 위해서 만들어진 함수이다. 예들 들어 소계, 합계 등이 계산되면 GROUPING함수는 1을 반환하고 그렇지 않으면 0을 반환해서 합계값을 식별할 수 있다.

SELECT DEPTNO, GROUPING(DEPTNO), JOB, GROUPING(JOB), SUM(SAL)
  FROM EMP
  GROUP BY ROLLUP(DEPTNO, JOB);

　GROUPING의 반환값을 DECODE 혹은 CASE문으로 식별해서 SELECT문으로 ‘소계’, ‘합계’를 구분하는 것이다.

SELECT DEPTNO,
  DECODE(GROUPING(DEPTNO), 1, '전체합계') TOT, JOB
  DECODE(GROUPING(JOB), 1, '부서합계') T_DEPT, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);

GROUPING 함수 결과가 1이면 전체합계 혹은 부서합계를 출력하고 그렇지 않으면 NULL을 반환한다.

　GROUPING SETS 함수는 GROUP BY에 나오는 칼럼의 순서와 관계없이 다양한 소계를 만들 수 있다. GROUPING SETS 함수는 GROUP BY에 나오는 칼럼의 순서와 관계없이 개별적으로 모두 처리한다.
　CUBE는 CUBE 함수에 제시한 칼럼에 대해서 결합 가능한 모든 집계를 계산한다. 다차원 집계를 제공하여 다양하게 데이터를 분석할 수 있게 한다. 즉, 조합할 수 있는 경우의 수가 모두 조합되는 것이다.

윈도우 함수(Window Function)

　윈도우 함수는 행과 행 간의 관계를 정의하기 위해서 제공되는 함수이다. 윈도우 함수를 사용해서 순위, 합계, 평균, 행 위치 등을 조작할 수 있다.

SELECT WINDOW_FUNCTION(ARGUMENTS)
    OVER (PARTITION BY 칼럼
          ORDER BY WINDOWING절)
FROM 테이블명;

테이블 파티션(Table Partition)

　Partition은 대용량의 테이블을 여러 개의 데이터 파일에 분리해서 저장한다. 테이블의 데이터가 물리적으로 분리된 데이터 파일에 저장되면 입력, 수정, 삭제, 조회 성능이 향상된다. 파티션은 각각의 파티션 별로 독립적으로 관리될 수 있다. 즉, 파티션 별로 백업하고 복구가 가능하면 파티션 전용 인덱스 생성도 가능하다. 파티션은 Oracle 데이터베이스의 논리적 관리 단위인 테이블 스페이스 간에 이동이 가능하다. 데이터를 조회할 때 데이터의 범위를 줄여서 성능을 향살시킨다.
　Range Partition은 테이블의 칼럼 중에서 값의 범위를 기준으로 여러 개의 파티션으로 데이터를 나누어 저장하는 것이다.
　List Partition은 특정 값을 기준으로 분할하는 방법이다.
　Hash Partition은 데이터베이스 관리 시스템이 내부적으로 해시 함수를 사용해서 데이터를 분할한다. 결과적으로 데이터베이스 관리 시스템이 알아서 분할하고 관리하는 것이다. Hash Partition 이외에도 Composite Partition이 있는데, Composite Partition은 여러 개의 파티션 기법을 조합해서 사용하는 것이다.
　파티션 인덱스는 4가지 유형의 인덱스를 제공한다. 즉, 파티션 키를 사용해서 인덱스를 만드는 Prefixed Index와 해당 파티션만 사용하는 Local Index 등으로 나누어진다.

Global Index: 여러 개의 파티션에서 하나의 인덱스를 사용한다.
Local Index: 해당 파티션 별로 각자의 인덱스를 사용한다.
Prefixed Index: 파티션 키의 인덱스 키가 동일한다.
Non Prefixed Index: 파티션 키의 인덱스 키가 다른다.

SQL 최적화의 원리

옵티마이저(Optimizer)의 실행 계획

　SQL 개발자가 SQL을 작성하여 실행할 때, 옵티마이저는 SQL을 어떻게 실행할 것인지를 계획하게 된다. 즉, SQL 실행 계획(Excutuin Plan)을 수립하고 SQL을 실행한다. 옵티마이저는 SQL의 실행 계획을 수립하고 SQL을 실행하는 데이터베이스 관리 시스템의 소프트웨어이다. 동일한 결과가 나오는 SQL도 어떻게 실행하느냐에 따라서 성능이 달라진다. 따라서 옵티마이저의 실핼 계획은 SQL 성능에 아주 중요할 역할을 한다.
　옵티마이저는 데이터 딕셔너리(Data Dictionary)에 있는 오브젝트 통계, 시스템 통계 등의 정보를 사용해서 예상되는 비용을 산정한다. 옵티마이저는 여러 개의 실행 계획 중에서 최저비용을 가지고 있는 계획을 선택해서 SQL을 실행한다.
　SQL개발자가 작성한 SQL문을 어떻게 실행하느냐에 따라 성능이 달라진다. 옵티마이저는 실행계획을 수립하는 것이며, 만약 옵티마이저가 비효율적으로 실행 계획을 수립하면, SQL 개발자는 SQL문을 개선해야 한다. 이때 옵티마이저에게 실행 계획을 변경하도록 요청할 수가 있는데 이때 힌트(HINT)를 사용한다.
　옵티마이저는 SQL 실행 계획을 PLAN_TABLE에 저장한다. 그래서 SQL 개발자는 PLAN_TABLE을 조회해서 실행 계획을 확인할 수 있다.

옵티마이저 종류

　개발자가 SQL을 실행하면서 파싱(Parsing)을 실행해서 SQL의 문법 검사 및 구문 분석을 수행한다. 구문 분석이 완료되면 옵티마이저가 규칙 기반 혹은 비용 기반으로 실행 계획을 수립한다. 옵티마이저는 기본적으로 규칙 기반 혹은 비용 기반으로 실행 계획을 수립한다. 비용 기반 옵티마이저는 통계정보를 활용해서 최적의 실행 계획을 수립하는 것이다. 실행 계획이 완료되면 최종적으로 SQL을 실행하고 실행이 완료되면 데이터를 인출(Fetch)한다.

Query Transformer
- SQL문을 효율적으로 실행하기 위해서 옵티마이저가 변환한다.
- SQL이 변환되어도 그 결과는 동일하다.
Estimator
- 통계정보를 사용해서 SQL 실행비용을 계산한다.
- 총비용은 최적의 실행 계획을 수립하기 위해서이다.
Plan Gernerator
- SQL을 실행할 실행 계획을 수립한다.

　규칙 기반 옵티마이저(Rule base Optimizer)는 실행 계획을 수립할 때 15개의 우선순위를 기준으로 실행 계획을 수립한다. 최신 Oracle 버전은 규칙 기반 옵티마이저보다 비용 기반 옵티마이저를 기본적으로 사용한다.
　비용 기반 옵티마이저(Cost base Optimizer)는 오브젝트 통계 및 시스템 통계를 사용해서 총비용을 계산한다. 총비용이라는 것은 SQL문을 실행하기 위해서 예상되는 소요시간 혹은 자원의 사용량을 의미한다. 총비용이 적은 쪽으로 실행 계획을 수립한다. 단, 비용 기반 옵티마이저에서 통계정보가 부적절한 경우 성능 저하가 발생할 수 있다.

인덱스(Index)

　인덱스는 데이터를 빠르게 검색할 수 있는 방법을 제공한다. 인덱스는 인덱스 키로 정렬되어 있기 때문에 원하는 데이터를 빠르게 조회한다. 인덱스는 오름차순 및 내림차순 탐색이 가능하다. 하나의 테이블에 여러 개의 인덱스를 생성할 수 있고 하나의 인덱스는 여러 개의 칼럼으로 구성될 수 있다. 테이블을 생성할 때 기본키는 자동으로 인덱스가 만들어지고 인덱스의 이름은 SYSXXXX이다.
　인덱스의 구조는 Root Block, Branck Block, Leaf Block으로 구성되고 Root Block은 인덱스 트리에서 가장 상위 노드에 있는 노드를 의미하며 Branch Block은 다음 단계의 주소를 가지고 있는 포인터(Pointer)로 되어 있다. Leaf Block은 인덱스 키와 ROWID로 구성되고 인덱스 키는 정렬되어 저정되어 있다.
　인덱스 생성은 CREATE INDEX문을 사용해서 생성이 가능하다. 인덱스를 생성할 때는 한 개 이상의 칼럼을 사용해서 생성할 수 있다. 인덱스 키는 기본적으로 오름차순으로 정렬하고 “DESC” 구를 포함하면 내림차순으로 정렬한다.

CREATE INDEX IND EMP ON
        EMP (ENAME ASC, SAL DESC);

ENAME에 대해서 오름차순으로 SAL은 내림차순으로 정렬하는 인덱스를 생성한다. ASC는 생략이 가능하다.

　Index Unique Scan은 인덱스의 키 값이 중복되지 않는 경우, 해당 인덱스를 사용할 때 발생된다. 예를 들어 EMPNO가 중복되지 않는 경우 특정 하나의 EMPNO를 조회한다.
　Index Range Scan은 SELECT문에서 특정 범위를 조회하는 WHERE문을 사용할 경우 발생한다. LIKE, BETWEEN이 예이다. 물론 데이터 양이 적은 경우는 인덱스 자체를 실행하지 않고 TABLE FULL Scan이 될 수 있다. Index Range Scan은 인덱스의 Leaf Block의 특정 범위를 스캔한 것이다.
　Index Full Scan은 인덱스에서 검색되는 인덱스 키가 많은 경우에 Leaf Block의 처음부터 끝까지 전체를 읽어 들인다.

옵티마이저 조인(Optimizer Join)

　Nested Loop 조인은 하나의 테이블에서 데이터를 먼저 찾고 그다음 테이블을 조인하는 방식으로 실행된다. Nested Loop 조인에서 먼저 조회되는 테이블은 외부 테이블(Outer Table)이라고 하며 그다음 조회되는 테이블을 내부 테이블(Inner Table)이라고 한다. Nested Loop 조인에서는 외부 테이블(선행 테이블)의 크기가 작은 것을 먼저 착는 것이 중요하다. 그래야 데이터가 스캔되는 범위를 줄일 수 있기 때문이다. Nested Loop 조인은 RANDOM ACCESS가 발생하는데 RANDOM ACCESS가 많이 발생하면 성능 지연이 발생한다. 그러므로 Nested Loop 조인은 RANDOM ACCESS의 양을 줄여야 성능이 향상된다.
　Sort Merge 조인은 두 개의 테이블을 SORT_AREA라는 메모리 공간에 모두 로딩(Loading)하고 SORT를 수행한다. 두 개의 테이블에 대해서 SORT가 완료되면 구 개의 테이블을 병합(Merge)한다. Sort Merge 조인은 정렬이 발생하기 때문에 데이터양이 많아지면 성능이 떨어지게 된다. 정렬 데이터양이 너무 많으면 정렬은 임시 영역에서 수행된다. 임시 영역은 디스크에 있기 때문에 성능이 급격히 떨어진다.
　Hash 조인은 두 개의 테이블 중에서 적은 테이블을 HASH 메모리에 로딩하고 두 개의 테이블의 조인 키를 사용해서 해시 테이블을 생성한다. Hash 조인은 해시 함수를 사용해서 주소를 계산하고 해당 주소를 사용해서 테이블을 조인하기 때문에 CPU 연산을 많이 사용한다. 특히 Hash 조인 시에는 선행 테이블이 충분히 메모리에 로딩되는 크기여야 한다.

Twitter Facebook LinkedIn

MyeongWoo