참고로 이 글은 전반적인 이론에 대한 설명이 아닌 SQLD 시험을 위한 키워드 노트와 같은 글로, 이미 공부를 한 사람에게는 키워드 암기를 위한 용도로,

SQLD 시험에 대해 알아보는 사람에게는 대략적인 시험 범위와 내용을 알리고자 하는 용도로 작성한 글로 SQLD 전반을 위한 공부는 책을 구입하는 것을 추천드립니다.

1. SQLD 자격증이란? 👩‍💻

1. 시험 정보

한국데이터산업징흥원 K data 에서 주관하는 SQL 자격검정이다.

  • SQLP : Structured Query Language Professional
  • SQLD : Structured Query Language Developer

해당 자격증은 2가지 등급이 존재하며 SQLP보다는 한 단계 낮은 자격증이다.

  • 시험시간 : 90분
  • 문항수 : 50 (데이터 모델링의 이해 10 + SQL 기본 및 활용 40)
  • 배점 : 각 문항당 2점씩 100점 만점
  • 합격 기준 : 평균 60, 과목별 40% 이상 취득
  • 총 50문항 중 30문항을 맞아야한다.
  • 과락을 피하려면 모델링 4문항 SQL 20문항을 넘겨야한다.

SQLP 는 난이도가 꽤 높다고 알려져있다. 하지만 SQLD 는 난이도가 낮은편이다. 퇴근 후, 그리고 주말을 이용해 약 10일 공부하고 합격했다. 따라서 얇은 책을 선택했고, 그 책은 ‘SQL 개발자’(임호진 저, 영진닷컴) 이었으나 잘못된 설명도 간간히 보이고, 오타도 많기로 유명한 책이었다. 😭😭

아무튼 그래도 합격 하는데 문제는 없었으나 공부를 위해서라면 다른 책을 사는게 좋을 것 같으니 판단은 알아서 하시길…

2. 개발자에게 SQL 이란?

우선 Middleware 에 대해 알아보자.

  • Client PC - Database : 직접 연결, 2계층 클라이언트/서버 로 구성된다. -> ODBC
  • Client PC - Middleware - Database : 대부분의 환경 구성으로 Middleware가 하는 일은 Client와 Server 사이에서 Transaction 을 관리한다.

Python Middleware

사진 출처

그리고 이 Middleware 는 RPC(Remote Procedure Call), MOM(Message Oriented Middleware), TP-Monitor(Transaction Processing Monitor), ORB(Object Request Broker), WAS(Web Application Server) 와 같은 것들이 존재한다.

그리고 이 WAS 를 구성하는게 개발자고, ORM 을 사용할 수도 있지만 MyBatis 같은 SQL 을 것을 이용해 SQL 명령을 직접 내리기도 한다. 그리고 ORM 을 쓰는 것 역시 기본적으로 SQL 에 대한 이해가 있어야 정확하고 효율적인 코드 구현이 가능하기 때문에 개발자에게도 SQL 을 이해하는 것은 중요하다.

3. Oracle DB Settings

Database 마다 문법도 조금씩 다르고, 설정 하는 것 역시 다르다. 그 중 SQLD 는 Oracle 을 기본으로 한다.

Oracle 의 설정은 설치된 디렉토리의 ~/network/admin/ 안에 있다.

  • tnsnames.ora : DB 접속 정보를 설정. e.g. 프로토콜, 서버 주소, 포트 번호는 물론 인스턴스 등
  • listener.ora : DB와 Client 사이의 네트워크를 설정.
  • sqlnet.ora : DB 접속 인증 방식을 설정. i.e. 어떤 인증 방식을 선택할 것인지…

2. 데이터 모델링의 이해 👩‍💻

1. Data modeling

1 ) Data modeling 의 특징

특-단추명

  • 단순화(Simplification) : 누구나 쉽게 이애할 수 있도록 표현한다.
  • 추상화(Abstraction) : 현실세계를 간략하게 표현한다.
  • 명확성(Clarity) : 명확하게 의미가 해석되어야 하고, 한 가지 의미를 가져야 한다.


2 ) Data modeling 의 단계

단-개논물

  1. 개념적 모델링(Conceptual Data Modeling) : 고객 설명용
    • 업무적 관점, 전사적 관점 (O), 기술적 용어 (X)
    • 추상화 수준이 가장 높음.
    • Entity, Attribute, 개념적 ERD(Entity Relationship Diagram)을 작성.
  1. 논리적 모델링(Physical Modeling) : 설계자용
    • 개념적 모델링 => 논리적 모델링 변환.
    • Identifiers(식별자), Relation(관계), Attribute 을 표현.
    • 정규화를 통해 재사용성을 높임.
  1. 물리적 모델링(Physical Modeling) : 개발자용 or DBA용
    • 실제 Database를 구축.
    • Table, Index, Stored Procedure, Function, View 등을 생성.
    • 성능, 보안, 가용성을 고려.


3 ) Data modeling 의 3요소

띵어리

  • Things
  • Attributes
  • Relationships


4 ) Data modeling 의 관점

  • Data : What -> 구조 분석, 정적 분석.
  • Process : How -> 시나리오 분석, 도메인 분석, 동적 분석.
  • Data & Process : Interaction -> 데이터와 프로세스의 관계. CRUD 분석.

2. ERD(Entity Relationship Diagram)

ERD 는 1976년 Peter Chen 이 만든 표기법으로 오늘날 데이터 모델링의 표준으로 사용되고있다.

ERD

ERD Relationship

1 ) ERD 작성 절차

(Entity) 도배관설 (관계) 관서참여 필수 : Entity 도배와 관로포설은 관련부서 참여가 필수다.

  1. Entity 도출, 그리기 : 사각형 그리기.
  2. Entity 배치 : Entity 위치 설정(중요도 높으면 왼쪽 상단에…).
  3. Entity 관계 설정 : Entity 선 긋기.
  4. 관계명 서술 : 선 위에 설명 달기(관계 규정).
  5. 관계 참여도 : 1:1, 1:N 등을 표현(선 위에 사람 모양).
  6. 관계 필수 여부 : 반드시 존재해야 하는 것(선 위에 막대기).


2 ) ERD 작성시 고려 사항

  • ERD 는 쉽고 복잡하지 않아야 한다.
  • 독립성 : 데이터 중복을 제거해 정규화

3. 3-Level Schema

ANSI 표준으로 독립성을 확보하기 위한 방법으로 3 개의 레벨로 나눈다.

외개내 : 외계인은 우리 내부에 있다.

Schema Characteristics
외부(External) 스키마 사용자, 앱 관점
개념(Conceptual) 스키마 설계자 관점(전체 DB/테이블 표현)
내부(Internal) 스키마 개발자 관점(물리적/레코드, 필드, 인덱스 표현)

3-Level Schema

독립성 Characteristics
논리적 독립성 개념 스키마 변경 -> 외부 스키마 영향 X
물리적 독립성 내부 스키마 변경 -> 개념 스키마 영향 X

4. Entity

1 ) Entity (=테이블, 릴레이션) 특징

식인집속관업 : 식인을 하는 집 속에 갔더니 관이 업청나게 많았다.

특징 설명
식별자 유일한 식별자(PK) (ID, 계좌번호…)
인스턴스 집합 레코드 2개 이상 존재 (고객 정보는 2명 이상)
속성 엔터티는 속성이 반드시 필요 (ID, PW, 이름, 주소…)
관계 다른 엔터티와 1개 이상 관계 필요 (고객은 계좌를 개설)
업무 업무에서 관리되어야 하는 집합 (고객, 계좌…)


2 ) Entity 유무형

유개사 : 유개사 지물실

엔터티 설명
유형 엔터티 지속적 사용, 물리적 형태 O (고객, 사원)
개념 엔터티 물리적 형태 X ( 거래소 종목, 보험 상품)
사건 엔터티 비즈니스 프로세스 실행 (주문, 계약)


3 ) Entity 발생 시점

기중행 : 기중행 키엔독

엔터티 설명
기본 엔터티 키 엔터티, 다른 엔터티 영향 X 독립적 (고객, 상품)
중심 엔터티 기본 엔터티로부터 발생 행위 엔터티를 생성 (주문, 계약)
행위 엔터티 2개 이상의 엔터티로터 발생 (주문 이력)


4 ) Entity 3가지 공통점

명저띵비프

  • 개념, 사건, 사람, 장소 등과 같이 명사.
  • 저장이 필요한 어떤 것(Things).
  • 비즈니스 프로세스에서 관리되어야 하는 정보.

5. Kind of Relationship

1 ) 관계 종류

존행 : Entity 간 관계는 존나 행복하다

관계 설명
존재 관계 Entity 사이의 상태
(고객은 관리점에 소속된다 ⇒ 고객 Entity와 관리점 Entity는 존재 관계)
행위 관계 Entity 사이의 행위
(고객이 계좌를 개설한다 ⇒ 고객 Entity와 계좌 Entity는 행위 관계)


2 ) 관계 차수(Cardinality)

ERD Relationship

관계 설명
1:1 관계 완전 1:1 관계 ⇒ 1:1 Entity 관계 반드시 존재
선택적 1:1 관계 ⇒ 1:1 or 1:0 Entity 관계 필수 아님
1:N 관계 고객은 여러 개의 계좌를 가질 수 있다. (1:N)
M:N 관계 학생은 여러 과목을 수강할 수 있다. (1:N)
과목은 여러 명의 학생이 수강한다. (1:M)
⇒ RDB에서 M:N join은 카테시안 곱이 발생하므로 수강이라는 Entity를 추가해 1:N, N:1로 해소.
필수적 관계 O 반드시 하나가 있어야 한다. (고객이 있어야 계좌를 만들 수 있다.)
선택적 관계 ⎹ 없을 수도 있다. (고객은 있지만 계좌는 없을 수 있다.)


3 ) 식별 관계(Identification Relationship)

  • 실선
  • PK가 다른 개체의 FK이면서 PK


4 ) 비식별 관계(Non-Identification Relationship)

  • 점선
  • PK가 다른 개체의 FK지만 PK가 아닌 일반 컬럼


5 ) 강한 개체(Strong Entity)

  • 다른 엔터티에 의존하지 않고 독립적으로 존재
  • 다른 엔터티와 관계를 가질 때 다른 엔터티에 기본키를 공유
  • 식별 관계
  • 강한 개체의 기본키 값이 변경되면 식별 관계(기본키를 공유받은) 엔터티의 값도 변경
  • 고객 개체에 해당


6 ) 약한 개체(Week Entity)

  • 개체의 존재가 다른 개체의 존재에 달려있다
  • 계좌 개체에 해당

6. Attribute

1 ) Attribute (=컬럼, 속성) 특징

  • 인스턴스(레코드) 구성 요소
  • 더 이상 분해 X
  • 하나의 값만 가짐


2 ) Attribute 분해

단복다 : 단 맛 나는 복어다

속성 설명
단일 속성 ID, 이름 등 분해 불가
복합 속성 주소 = 도, 시, 구, 동으로 분해
다중 속성 엔터티로 분해되는 것. (상품 리스트)


3 ) Attribute 생성 특성

기설파 : 기설파 비프 데모 다속

속성 설명
기본 속성 비즈니스 프로세스에서 도출 (ID, 이름, 계좌번호)
설계 속성 데이터 모델링 과정에서 발생, 유일한 값 부여 (상품 코드)
파생 속성 다른 속성으로부터 생성 (합계, 평균)


4 ) Attribute 구성 방식에 따른 분류

기외일

  • 기본키 속성
  • 외래키 속성
  • 일반 속성


5 ) 도메인(Domain)

속성이 가질 수 있는 값의 범위 : 성별 (여, 남, 기타)

7. Entity Identifier

1 ) 주식별자(PK)

최대유불

주식별자의 속성 설명
최소성 최소성을 만족하는 키
대표성 엔터티를 대표
유일성 엔터티의 인스턴스를 유일하게 식별
불변성 자주 변경되지 않아야 한다


키의 종류

후기대슈 : 영화 관람 후기좀 대슈

키의 종류 설명
후보키(Candidate key) 유일성, 최소성 만족
기본키(Primary key) (주식별자) 후보키 중 대표, Not null
대체키(Alternate key) (보조식별자) 후보키 중 기본키를 제외한 나머지
슈퍼키(Super key) 유일성 만족

후보키(기본키 + 대체키)는 유일성, 최소성을 만족
슈퍼키는 유일성만 만족한다.


2 ) 외래키(Foreign key)

참조 무결성(Referential Integrity)을 위해 사용


3 ) 특성에 따른 식별자의 구분

  • 대표성
식별자 설명
주식별자 PK
보조식별자 대체키
  • 생성 여부 : 내부 외부 식별자화
식별자 설명
내부 식별자 엔터티 내부에서 스스로 생성
외부 식별자 다른 엔터티와 관계로 인해 생성 (계좌 엔터티의 회원 ID)
  • 속성의 수 : 단일 복합 식별자
식별자 설명
단일 식별자 하나의 속성
복합 식별자 두 개 이상의 속성 (여러 컬럼을 동시에 unique, pk 설정)
  • 대체 여부 : 본질 인조 식별자
식별자 설명
본질 식별자 비즈니스 프로세스에서 생성(주민번호, 회원 ID)
인조 식별자 인위로 생성(sequence number, auto increment)

3. 데이터 모델링 - 정규화/반정규화 👩‍💻

1. Normalization

1 ) 정규화

  • 일관성
  • 데이터 중복 최소화
  • 데이터 유연성 최대화
  • 모델 독립성 확보

직원과 부서를 하나의 테이블에 넣으면 부서를 추가하기 위해 사원 컬럼을 임의의 값으로 채워 넣는 Anomaly(이상현상)가 발생한다.
=> 정규화로 해결


2 ) 정규화 절차

123B45 기분이 대기다조

정규화 설명
제1정규화 기본키 설정
제2정규화 부분 함수 종속성 제거 : 기본키가 2개 이상의 속성일 경우 분해
제3정규화 이행 함수 종속성 제거 : 기본키를 제외한 컬럼의 종속성을 제거
BCNF 대체키가 기본키를 종속시키면 분해
제4정규화 다중값 종속성 제거 : 여러 컬럼들이 하나의 컬럼에 종속할 경우 분해
제5정규화 조인 종속성 분해 : 조인에 의해 종속성 발생시 분해

이 중 123 정규화, BCNF 는 함수적 종속성(Functional Dependency)을 근거로 한다.

  • 정규화 장점 : 데이터 중복 제거, 모델 유연성 최대
  • 정규화 단점 : Join 으로 인한 성능 저하 => Index, Optimizer, 반정규화(중복 허용)으로 해소한다.

2. De-Normalization

1 ) 반정규화

  • 성능 향상을 위해 데이터 중복 허용(Join 을 줄임, 무결성을 깨뜨릴 위험이 존재)
  • SELECT 속도가 향상(모델 유연성이 낮아짐)

반정규화를 수행하는 경우

  • 정규화로 수행 속도가 느려짐
  • 다량의 범위를 자주 처리
  • 특정 범위 데이터만 자주 처리
  • 요약/집게 정보를 자주 요구


속도 향상 기법 설명
계산된 컬럼 추가 배치 프로그램으로 미리 계산하고 그 결과를 특정 컬럼에 추가
수직 파티셔닝 컬럼을 분할해 2개 이상의 테이블로 나눈다
수평 파티셔닝(샤딩) 값을 기준으로 테이블을 분할
테이블 병합 1:1 관계의 테이블 병합, 1:N 관계의 테이블 병합(이 경우 데이터 중복 발생), Super type & Sub type 병합

실제로 빅데이터를 이용해 BI(Business Intelligence)를 하고자 하는 경우 ETL(Extract, Transform, Load)을 통해
분산된 데이터를 모아 Data Warehouse를 구축하는 일을 하게 된다. 즉, 데이터를 꺼내기 쉽고, 빠르게 꺼낼 수 있도록 반정규화를 처리하는 것이다.


2 ) 반정규화 절차

대조검 다반

절차 설명
대상 조사 및 검토 반정규화 대상을 조사
다른 방법 검토 클러스터링, 뷰, 인덱스 튜닝, 응용 프로그램, 파티셔닝 등을 검토
반정규화 수행  

Clustering : 인덱스 정보를 저장할 때 물리적으로 정렬해서 저장하는 것을 클러스터링 인덱스라 한다.


3 ) 반정규화 기법

  • Partitioning : 논리적으로는 하나의 테이블이지만 여러 테이블에 분산되어 기록
파티션 종류 설명 예시
Range Partition 범위를 기준으로 파티션 0 ~ 10, 11 ~ 20, 21 ~ 30
List Partition 특정한 값을 기준으로 파티션 VVIP, VIP, NORMAL
Hash Partition 해시 함수를 적용해 파티션 DBMS가 알아서 분류
Composite Partition 범위 & 해시 함수를 적용해 파티션  
  • 엑세스 범위가 줄어 SELECT 성능 향상
  • 데이터 분할로 I/O 성능 향상
  • 파티션 독립적 백업 및 복구 가능


  • Partition Index
파티션 인덱스 종류 설명
Global Index 여러 파티션에서 하나의 인덱스를 사용
Local Index 파티션 별로 각자의 인덱스를 사용
Prefixed Index 파티션 키 = 인덱스 키
Non Prefixed Index 파티션 키 ≠ 인덱스 키

오라클은 Global Non-Prefixed 를 지원하지 않는다.


  • Super type, Sub type
    • Super type : 고객 엔터티
    • Sub type : 개인 고객 엔터티, 기업 고객 엔터티
  • Super type ↔ Sub type 변환
변환 방법 설명
OneToOne type (1:1 type) 슈퍼 타입, 서브 타입 개별 테이블로, 조인 발생, 관리 어렵
Plus type (super + sub type) 슈퍼 타입, 서브 타입 테이블로, 조인 발생, 관리 어렵
Single type (All in One type) 하나의 테이블로, 조인 성능 좋고 관리 편함. 입출력 성능 나쁨.

3. Distributed Database

1 ) 분산 데이터베이스

  • 중앙 집중형 데이터베이스 : 한 대의 물리적 시스템.
  • 분산 데이터베이스 : 물리적으로 떨어짐. 네트워크로 연결.


2 ) 분산 데이터베이스의 투명성

알 필요 없다, 몰라도 된다, 문제 없다 로 치환하면 맞아 떨어진다.

분위지 중장병 투명성

투명성 종류 설명
분할 투명성 고객은 분할 저장을 알 필요 없다
위치 투명성 저장 장소 명시할 필요 없다
지역 사상 투명성 지역 시스템과 무관한 이름을 사용한다
중복 투명성 데이터베이스 객체가 여러 시스템에 중복되어도 고객의 데이터 일관성은 유지
장애 투명성 통신망 이상 발생, 데이터 무결성 보장
병행 투명성 여러 앱 동시 트랜잭션 수행, 결과 문제 없다


3 ) 분산 데이터베이스 설계

  • 상향식 : 지역 스키마 -> 전역 스키마
  • 하향식 : 전역 스키마 -> 지역 스키마


4 ) 장단점

  • 장점 : 신가빠확 신뢰성, 가용성, 병렬 처리 빠른 응답, 용량 확장
  • 단점 : 관보무설 관리 통제 어려움, 보안 관리 어려움, 무결성 관리 어려움, 설계가 복잡

4. RDB and SQL 👩‍💻

1. Hierarchical Database Model (계층형)

  • Tree 구조
  • Owner 와 Member 형태로 저장
  • 1:N 관계

XML 문서 같은 곳에서 사용되는 방식으로 오늘날 데이터베이스에서는 거의 사용되지 않는다.

2. Network Database Model (네트워크형)

계층형 모델을 보완하기 위해 생겨난 모델로 네트워크 모델을 데이터베이스에 적용했다.

  • 1:N, N:N 표현이 가능

3. Relational Database Model (관계형)

오늘날 대부분의 데이터베이스가 취하고 있는 형태로 흔히 RDB라고 부른다. RDB 는 DBMS(Database Management System)을 통해 관리하며, 집합 연산과 관계 연산을 이용한다.


1 ) 집합연산

합차교곱

연산 설명
합집합(Union) 두 릴레이션을 합함. 중복 행은 한 번만 조회
차집합(Difference) 공통 제외 조회
교집합(Intersection) 공통 조회
곱집합(Cartesian product) 각 릴레이션에 존재하는 모든 데이터를 조합하여 연산


2 ) 관계연산

선투결나

연산 설명
선택 연산(Selection) row 조회
투영 연산(Projection) column 조회
결합 연산(Join) 공통 속성으로 새 릴레이션을 만들어낸다
나누기 연산(Division) 동일한 속성값 행을 추출 후 중복 행을 제거

4. Structured Query Language (SQL)

1 ) SQL

RDB 를 다루기 위한 언어로 절차형 언어로 ANSI/ISO 표준을 준수하는 언어로 명령은 주로 다음과 같은 4개의 그룹으로 나뉜다.

  • DDL(Define) : CREATE, ALTER, DROP, TRUNCATE
  • DML(Manipulation) : CRUD
  • DCL(Control) : GRANT, REVOKE
  • TCL(Control) : COMMIT, ROLLBACK, SAVEPOINT


2 ) Transaction

데이터베이스에서 작업을 처리하는 단위를 말하며 다음 특징을 갖는다.

원일독지

  • 원자성(Atomicity) : All ot Nothing
  • 일관성(Consistency) : 트랜잭션 처리 전 후 일관성 유지
  • 독립성(Isolation, 고립성) : 진행 중인 트랜잭션은 서로의 연산에 영향을 주지 않는다
  • 지속성(Durability, 영속성) : 트랜잭션 성공 시 결과는 영구적이다


3 ) SQL 실행 순서

파실인 (SQL 은 파를 싫어하는 인간이다)

  • 파싱(Parsing) : SQL 문법 확인 및 구문 분석 후 Library Cache 에 저장
  • 실행(Execution) : SQL 을 실행
  • 인출(Fetch) : 데이터를 읽어서 전송

5. Data Definition Language 👩‍💻

1. DDL

DDL에는 대표적으로 다음 명령이 존재한다. CREATE, ALTER, DROP, TRUNCATE

그리고 테이블을 생성할 때는 반드시 RDB 의 무결성을 해치지 않도록 하기 위해 다음을 고려해야한다.
CASCADE : 참조 무결성 위배(부서 테이블에서 부서를 삭제하면 직원 테이블의 해당 부서 직원이 삭제됨)


시험에서 가장 혼동되기 쉬운 ALTER 명령을 정리하면 다음과 같다.

테이블명 변경 : ALTER TABLE ~ RENAME TO 컬럼명 변경 : ALTER TABLE ~ RENAME COLUMN ~ TO 컬럼 추가 : ALTER TABLE ~ ADD (컬럼 자료형) 컬럼 변경 : ALTER TABLE ~ MODIFY (컬럼 자료형) 컬럼 삭제 : ALTER TABLE ~ DROP 컬럼

2. View

그리고 Table 외에 Table 과 거의 유사한 View도 존재한다. 하지만 명확하게 Table 은 아니다. View 가 갖는 특징은 다음과 같다.

  • 실제 데이터를 가지지 않는 가상 테이블.
  • Data Dictionary에 SQL 형태로 저장, 실행 시 참조.
  • 특정 컬럼만 조회 시켜 보안성을 향상.
  • ALTER(수정) 불가능 , CREATE OR REPLACE ~ : 삭제 후 재생성
  • 인덱스 생성 불가
  • CRUD 중 조회만 가능

6. Data Manipulation Language 👩‍💻

1. CRUD

쉽게 DML = CRUD를 생각하면 된다. SQLD 를 취득하려는 사람 중 SQL 을 아예 다뤄보지 않은 사람은 없을테니 기본적인 쿼리는 모두 생략하고 일부 특징정인 부분만 정리한다.

특히 데이터를 INSERT 할 때 값을 직접 넣는 것 외에도 조회한 값을 이용해 데이터를 등록하는 방법도 있으니 참고하자.

INSERT INTO 테이블 (컬럼1, 컬럼2)
VALUES ('A1', 'A2'), ('B1', 'B2');

조회한 값을 테이블에 넣을 수도 있다.

INSERT INTO 테이블 (컬럼1, 컬럼2)
SELECT * FROM 테이블2
  • Nologgin

Check point 이벤트 발생 시 로그를 파일에 저장하는데 Buffer Cache 메모리 영역 생략하고 기록해 Insert 성능을 향상시킨다.

ALTER TABLE DEPT LONOGGING;
  • DELETE 문으로 데이터를 삭제한다고 테이블 용량(MAX_EXTENTS)이 초기화가 되지 않는다.
DELETE FROM DEPT;     -- 모든 데이터 삭제, 테이블 용량 초기화 X
TRUNCATE TABLE DEPT;  -- 모든 데이터 삭제, 테이블 용량 초기화 O

2. Sort

  • 메모리 많이 사용.
  • SORT_AREA_SIZE가 작으면 성능 저하 발생.
  • 조회시 비용이 많이 드는 정렬을 회피하기 위해 Index 생성 시 오름차순 혹은 내림차순으로 생성.

  • ORDER BY의 default는 ASC

  • Index hint를 이용한 정렬

Oracle 에서만 되는 것으로 알고 있다. 참고로 시험은 Oracle 기준이다.

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

-- ORDER BY를 사용한 것 보다 Index hint 정렬이 가볍고 빠르다.
SELECT A.*
FROM EMP A
ORDER BY A.EMPNO DESC;

3. WHERE의 부정 비교 연산자

연산자 설명
!=
^=
<>
NOT 컬럼명 =		 같지 않은 것을 조회한다
NOT 컬럼명 > 크지 않은 것을 조회한다

4. 와일드카드

LIKE와 함께 사용한다.

와일드카드 설명
% 어떤 문자든 포함
_ 1개면 단일 문자 
SELECT name
FROM PRODUCTS
WHERE 1 = 1
AND name LIKE ‘%IPHONE_’

Apple_IPHONE7, Apple_IPHONE8 조회됨
IPHONE7, Apple_IPHONE11 조회 안 됨

5. NULL

NULL은 다음과 같은 상태를 나타내기 위해 사용한다.

  • 모르는 값
  • 값의 부재
  • NULL + 숫자 혹은 날짜 = NULL
  • NULL 과 모든 비교는 알 수 없음을 반환
함수 설명
NVL(MGR, 0) MGR 이 NULL이면 0
NVL2(MGR, 1, 0) NVL + DECODE
MGR 이 NOT NULL이면 1, NULL이면 0
NULLIF(exp1, exp2) exp1 = epx2 면 NULL (true 면 NULL)
exp1 ≠ exp2 면 exp1
COALESCE(exp1, exp2, exp3, …) NULL 이 아닌 최초 값 반환

6. GROUP BY ~ HAVING

SELECT DEPTNO
		 , SUM(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING SUM(SAL) > 10000;
  • GROUP BY에 WHERE 사용 : GROUP BY 이전 데이터를 걸러낸 후 GROUP BY
  • GROUP BY에 HAVING 사용 : GROUP BY 이후 집계 함수 데이터를 걸러냄(서브쿼리에 넣고 WHERE 한 것과 같다).

SQL 을 많이 다뤄보지 않았다면 충분히 헷갈릴만한 문제고, 시험에서도 함정으로 나오니 구분하는 게 좋다.

7. 집계 함수

함수 설명
COUNT() 개수
SUM() 합
AVG() 평균
MAX(), MIN() 최댓값, 최솟값
STDDEV() 표준편차
VARIAN() 분산

8. SELECT 쿼리 실행 순서

FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY

9. Type Casting

  • 명시적(Explicit) 형변환
TO_NUMBER(문자열)
TO_CHAR(숫자 혹은 날짜, [FORMAT])
TO_DATE(문자열, [FORMAT])
CAST(필드 AS 타입)
  • 암시적(Implicit) 형변환 : DBMS 가 자동 형변환
    ⇒ Index 컬럼에 형변환 수행 시 Index 사용 불가함에 주의해야한다.
SELECT *
FROM EMP
WHERE EMPNO = '100';  -- EMPNO는 NUMBER로 암시적 형변환이 발생. Index 사용 불가.

SELECT *
FROM EMP
WHERE EMPNO = TO_NUMBER('100');  -- 올바른 사용법.

10. Built-in Function

Built-in Function 설명
ASCII(문자) 문자 or 숫자 ⇒ ASCII 코드값
CHAR(ASCII 코드값) ASCII 코드값 ⇒ 문자
SUBSTR(문자열, m, n) 문자열에서 m번째 부터 n개
LENGTH(문자열) or LEN(문자열) 문자열 길이
LTRIM(문자열, 지정 문자) 지정 문자 삭제. 생략 시 공백 삭제. 왼쪽.
RTRIM(문자열, 지정 문자) 지정 문자 삭제. 생략 시 공백 삭제. 오른쪽.
TRIM(문자열, 지정 문자) 지정 문자 삭제. 생략 시 공백 삭제. 양쪽.
SYSDATE 오늘 날짜 (22/05/21)
EXTRACT(YEAR FROM SYSDATE) 2022
TO_CHAR(SYSDATE, ‘YYYYMMDD’) 20220521
SIGN(숫자) 양수, 음수, 0 구별
MOD(숫자1, 숫자2) or % 나머지
CEIL(숫자) or CEILING(숫자) 올림(정수 반환)
FLOOR(숫자) 내림(정수 반환)
ROUND(숫자, m) 소수점 m 자리에서 반올림 (default 0)
TRUNC(숫자, m) 소수점 m 자리에서 버림(default 0)
DECODE(A, B, true, false) Ternary operator

11. ROWNUM & Inline view

SELECT *                   -- Main Query
FROM (
		SELECT ROWNUM list     -- Sub Query(=Inline view)
				 , ename
		FROM EMP
)
WHERE list <= 5;

모든 데이터베이스의 SQL 이 동일한 것은 아니다. 참고로 SQL Server는 TOP(5), MySQL은 LIMIT 5 를 사용한다.

12. ROWID

데이터가 어떤 데이터 파일, 어느 블록에 저장되었는지 구별하는 고유값. 모든 데이터는 ROWID를 갖는다.

SELECT ROWID
     , name
FROM WINE;

ROWID                 | WINE
------------------------------------
AAAGhSAABAAALLBAAA    | 까베르네 쇼비뇽
AAAGhSAABAAALLBAAB    | 멜롯
AAAGhSAABAAALLBAAC    | 샤도네이

13. WITH ~ AS

옵티마이저는 Sub Query를 임시 테이블이나 뷰로 판단하고 다룬다.

7. Data Control Language 👩‍💻

1. GRANT

GRANT ~ TO를 이용해 권한을 부여할 수 있다.

GRANT privileges ON object TO user;

GRANT SELECT, INSERT, UPDATE, DELETE
	ON EMP
	TO USER_KIM;


권한의 종류

privileges 설명
SELECT, INSERT, UPDATE, DELETE  
REFERENCES 지정된 테이블을 참조하는 제약조건을 생성하는 권한
ALTER 지정된 테이블에 대해서 수정할 수 있는 권한
INDEX 지정된 테이블에 대해서 인덱스를 생성할 수 있는 권한
ALL 테이블에 대한 모든 권한

💡테이블에 대한 권한은 각 테이블의 소유자가 가지고 있기 때문에 소유자로부터 권한을 받지 않으면 다른 유저의 테이블에 접근할 수 없다.

2. REVOKE

ROVOKE ~ FROM을 이용해 권한을 회수할 수 있다.

REVOKE privileges ON object FROM user;

3. WITH GRANT OPTION

관리자는 계정에 권한을 부여할 때 WITH GRANT OPTION과 WITH ADMIN OPTION이라는 2가지 옵션을 줄 수 있다. 이 두 옵션은 모두 특정 사용자에게 테이블에 대한 접근 권한을 부여할 때 옵션으로써 권한을 부여할 수 있는 권한을 부여하는 것이다.

그렇다면 둘이 어떻게 다를까? 우선 WITH GRANT OPTION 부터 살펴본다.

A 가 B 에게 USER 테이블의 조회 권한을 WITH GRANT OPTION 을 주어 부여했다. 그리고 B 가 다시 C 에게 이 테이블의 조회 권한을 부여한다. 이제 C 는 USER 테이블의 조회 권한을 갖는다.
(참고로 B 가 C 에게 권한을 부여할 때 A 로부터 WITH GRANT OPTION 옵션을 부여 받은 권한에 대해서만 가능하다.)


이제 A 가 B 에게 부여한 권한을 REVOKE 를 이용해 회수해보자.

A 가 B 에게 부여한 USER 테이블의 조회 권한을 회수했다. 그러자 B 와 C 의 'USER' 테이블에 대한 조회 권한이 모두 회수되었다.

4. WITH ADMIN OPTION

이번에는 WITH ADMIN OPTION 을 살펴본다.

위와 마찬가지로 A 가 B 에게 USER 테이블의 조회 권한을 부여한다. 그러나 이번에는 WITH ADMIN OPTION 을 주어 부여했다. 이번에도 B 는 C 에게 이 테이블의 조회 권한을 부여한다. 이제 C 는 USER 테이블의 조회 권한을 갖는다.


이번에도 동일하게 A 가 B 에게 부여한 권한을 REVOKE 를 이용해 회수해보자.

A 가 B 에게 부여한 USER 테이블의 조회 권한을 회수했다. 그러자 B 의 'USER' 테이블에 대한 조회 권한은 회수되었으나 C 의 권한은 유지되어 여전히 ‘USER’ 테이블의 조회 권한을 갖고 있다.

즉, WITH GRANT OPTION은 권한을 부여할 때 Hierarchy 구조를 만들어 공유시킴으로써 회수시 recursive 하게 권한을 회수하는 반면, WITH ADMIN OPTION은 그 권한을 복사해 연쇄 작용이 일어나지 않는다.

8. Transaction Control Language 👩‍💻

1. COMMIT

  • INSERT, UPDATE, DELETE 결과를 DB에 반영.
  • 변경 이전 데이터는 잃어버린다.
  • COMMIT 완료시 LOCK 이 해제(UNLOCK)된다.
  • COMMIT 완료시 다른 사용자는 변경된 데이터를 조작할 수 있다.
  • COMMIT 완료시 하나의 트랜잭션 과정을 종료한다.

2. ROLLBACK

이전 커밋 이후의 INSERT, UPDATE, DELETE 를 모두 취소하고 LOCK 을 해제한다.

3. SAVEPOINT

  • 트랜잭션을 작게 분할하여 관리하기 위함.
  • ROLLBACK TO [SAVEPOINT 명] 을 하면 SAVEPOINT 까지만 ROLLBACK.
  • 그냥 ROLLBACK 실행시 SAVEPOINT 와 무관하게 전부 ROLLABCK.

9. Join 👩‍💻

1. EQUI JOIN (등가 조인)

교집합을 구하는 방법 중 하나로 Hash Join 을 사용한다.
ANSI/ISO 표준 SQL이다.

SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

2. INNER JOIN

INNER JOIN 역시 EQUI JOIN 과 마찬가지로 ANSI/ISO 표준 SQL이다.

SELECT *
FROM EMP
		INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

EQUI JOIN과 INNER JOIN은 상호 호환이 가능하다.

3. INTERSECT

EQUI JOIN 과 INNER JOIN 모두 교집합을 구하는 집합 연산이었다. 그리고 여기 교집합을 구하는 또다른 집합 연산이 존재한다.

SELECT DEPTNO FROM EMP
INTERSECT
SELECT DEPTNO FROM DEPT;

INTERSECT는 교집합을 구하지만 EQUI JOIN or INNER JOIN과는 목적이 조금 다르다.

  • INTERSECT : SELECT 에 조회하려는 값을 대상으로 교집합 연산을 한다.
  • EQUI JOIN or INNER JOIN : WHERE 또는 ON을 이용해 특정 조건을 대상으로 교집한 연산을 한다. 즉, SELECT 에 조회하려는 대상과 WHERE 또는 ON 에서 교집합 연산하려는 대상이 일치할 필요가 없다.

4. MINUS, EXCEPT (차집합)

JOIN 애 ON과 WHERE 조건만 잘 연산해서 한쪽에만 존재하는 차집합을 연산하는 것도 가능하다. 하지만 이 경우 MINUS 연산을 이용하면 더욱 쉽게 조회를 하는 것이 가능하다.

  • Just JOIN
SELECT *
FROM EMP
LEFT OUTER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO
WHERE DEPT.DEPTNO IS NULL;
  • MUNUS
SELECT * FROM EMP
MINUS
SELECT * FROM DEPT;

두 쿼리는 동일한 연산 결과를 갖는다.


참고로 MySQL 또는 PostgreSQL 에서는 EXCEPT를 사용한다. 시험에서 함정 카드로 자주 나오니 어떤 데이터베이스인지 꼭 확인하도록 하자.

  • EXCEPT (MySQL, PostgreSQL)
SELECT * FROM EMP
EXCEPT
SELECT * FROM DEPT;

5. Non-EQUI JOIN (비등가 조인)

EQUI JOIN 과 달리 =을 사용하지 않고 >, <, >=, <= 를 사용한다.

6. OUTER JOIN

  • LEFT OUTER JOIN : 왼쪽 테이블 데이터를 기준으로 JOIN 한다.
  • RIGHT OUTER JOIN : 오른쪽 테이블 데이터를 기준으로 JOIN 한다.
  • FULL OUTER JOIN : 합집합 연산을 한다.

오라클에서는 (+) 기호를 써서 OUTER JOIN을 할 수 있다.

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

위 SQL 은 DEPT, EMP 순으로 불러왔으니 LEFT OUTER JOIN으로 변경하면 다음과 같다.

SELECT *
FROM DEPT
LEFT OUTER JOIN EMP ON DEPT.DEPTNO = EMP.DEPTNO

SQL JOINS

7. CROSS JOIN

  • 조인 조건 없이 2개의 테이블을 하나로 조인
  • Cartesian product(카테시안 곱)이 발생.
  • 14행 CROSS JOIN 4행 = 56행
SELECT *
FROM EMP, DEPT

SELECT *
FROM EMP
    CROSS JOIN DEPT;

위 두 쿼리는 동일하다. 특히 첫 번째 쿼리에 = 비교가 없어 EQUI JOIN이 발생하지 않는다는 거에 주목하자.

8. NATURAL JOIN

  • alias 를 사용할 수 없다.
  • 동일한 컬럼 이름을 가지는 모든 컬럼에 대해 등가 조인(EQUI JOIN)한다.
  • 동일 컬럼이 2개 이상일 경우 JOIN ~ USING 문장을 이용한다.

INNER JOIN과 다른 점은 중복 컬럼을 중복해서 조회하지 않는다는 것이다.

  • INNER JOIN
SELECT *
FROM company
    INNER JOIN foods ON company.company_id = foods.company_id;

COMPANY_ID COMPANY_NAME    COMPANY_CITY    ITEM_ID    ITEM_NAME       ITEM_UNIT  COMPANY_ID
---------- --------------- --------------- ---------- --------------- ---------- ----------
16         Akas Foods      Delhi           1          Chex Mix        Pcs        16
15         Jack Hill Ltd   London          6          Cheez-It        Pcs        15
15         Jack Hill Ltd   London          2          BN Biscuit      Pcs        15
17         Foodies.        London          3          Mighty Munch    Pcs        17
15         Jack Hill Ltd   London          4          Pot Rice        Pcs        15
18         Order All       Boston          5          Jaffa Cakes     Pcs        18
  • NATURAL JOIN
SELECT * 
FROM company
    NATURAL JOIN foods;

COMPANY_ID COMPANY_NAME    COMPANY_CITY    ITEM_ID    ITEM_NAME       ITEM_UNIT
---------- --------------- --------------- ---------- --------------- ----------
16         Akas Foods      Delhi           1          Chex Mix        Pcs
15         Jack Hill Ltd   London          6          Cheez-It        Pcs
15         Jack Hill Ltd   London          2          BN Biscuit      Pcs
17         Foodies.        London          3          Mighty Munch    Pcs
15         Jack Hill Ltd   London          4          Pot Rice        Pcs
18         Order All       Boston          5          Jaffa Cakes     Pcs

9. UNION (합집합)

UNION이 FULL OUTER JOIN과 다른 점은 INTERSECT과 EQUI JOIN or INNER JOIN의 비교에서와 유사하다.

  • UNION : SELECT 에 조회하려는 값을 대상으로 합집합 연산을 한다.
  • FULL OUTER JOIN : ON을 이용해 특정 조건을 대상으로 교집한 연산을 한다. 즉, SELECT 에 조회하려는 대상과 WHERE 또는 ON 에서 교집합 연산하려는 대상이 일치할 필요가 없다.

UNION 연산은 두 테이블의 컬럼 수, 컬럼 데이터의 순서, 컬럼 데이터의 순서별 데이터 유형 모두가 일치해야한다.

그리고 UNION 연산에는 두 가지가 있다. 시험 예제에 함께 나오니 꼭 구별해야한다.

  • UNION : 중복 제거 O, 정렬 O
  • UNION ALL : 중복 제거 X, 정렬 X

10. CONNECT BY & WITH RECURSIVE (계층형 조회) 👩‍💻

계층형 또는 재귀로 불리며 다음과 같은 특징을 갖는다.

  • Tree 구조로 질의를 수행. Root node부터 하위 노드의 질의를 실행.
  • START WITH 시작 조건.
  • CONNECT BY PRIOR 조인 조건.
  • MAX(LEVEL)을 사용해 최대 계층 수를 구한다. 마지막 Leaf Node의 계층값 반환.
명령어 설명
LEVEL 검색 항목 깊이. (최상위 레벨 1)
CONNECT_BY_ROOT 계층 구조 최상위 값
CONNECT_BY_ISLEAF 계층 구조 최하위 값
SYS_CONNECT_BY_PATH 계층 구조 전채 전개 경로를 표시
NOCYCLE 순환 구조가 발생 지점까지만 전개
CONNECT_BY_ISCYCLE 순환 구조 발생 지점을 표시

오라클이 기준이니 CONNECT BY를 사용한 계층형 쿼리를 암기하는 것이 좋다. PostgreSQL 과 같은 데이터베이스에서는 WITH RECURSIVE로 표현되는 쿼리다.


SELECT * FROM person;

 last_name  | first_name | id | parent_id
------------+------------+----+-----------
 Dunstan    | Andrew     |  1 |    (null)
 Roybal     | Kirk       |  2 |         1
 Riggs      | Simon      |  3 |         1
 Eisentraut | Peter      |  4 |         1
 Thomas     | Shaun      |  5 |         3
  • Oracle
SELECT id, parent_id
FROM person
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id;

 id | parent_id
----+-----------
  1 |    (null)
  4 |         1
  3 |         1
  2 |         1
  5 |         3
  • PostgreSQL
WITH RECURSIVE a AS (
    SELECT id, parent_id
    FROM person
    WHERE parent_id IS NULL
    UNION ALL
    SELECT d.id, d.parent_id
    FROM person d
        JOIN a ON a.id = d.parent_id
)
SELECT id, parent_id 
FROM a;

 id | parent_id
----+-----------
  1 |    (null)
  4 |         1
  3 |         1
  2 |         1
  5 |         3

WITH RECURSIVE a AS (
    SELECT id, parent_id, 1::integer recursion_level
    FROM person
    WHERE parent_id IS NULL
    UNION ALL
    SELECT d.id, d.parent_id, a.recursion_level +1
    FROM person d
        JOIN a ON a.id = d.parent_id 
)
SELECT * FROM a;

 id | parent_id | recursion_level
----+-----------+-----------------
  1 |    (null) |               1
  4 |         1 |               2
  3 |         1 |               2
  2 |         1 |               2
  5 |         3 |               3

11. Sub Query 👩‍💻

Sub query 는 3가지로 나뉜다.

  • Scala Sub query : SELECT 에 사용된 sub query
  • Inline Sub query : FROM 에 사용된 sub query
  • Sub query : WHERE 에 사용된 sub query
SELECT user.name
     , (SELECT grade FROM membership WHERE id = u.id) AS grade  -- Scala Sub query
     , user.last_purchase_date
FROM (
        SELECT u.id
             , u.name
             , o.last_purchase_date
        FROM user u
                LEFT OUTER JOIN order o
                        ON u.id = o.id
) user  -- Inline view
WHERE user.id NOT IN (SELECT id FROM employee)  -- Sub query


1 ) Single Row Sub Query

  • 결과는 반드시 한 행.
  • 비교 연산자인 =, <, <=, … 를 사용.


2 ) Multi Rows Sub Query

  • 결과는 여러 행.
  • IN, ANY, ALL, EXISTS를 사용.
조건 설명
IN Main query, Sub query 결과 중 하나만 동일하면 참 (OR 조건)
ALL 모두 동일하면 참 (AND 조건)
ANY 하나 이상 동일하면 참
EXISTS 결과가 하나라도 존재하면 참


3 ) Correlated(연관) Sub Query

Sub query 내에서 Main query 내의 컬럼을 사용하는 것을 말한다.

12. Group Functions 👩‍💻

1. ROLLUP

  • GROUP BY 컬럼에 대해서 Sub total을 만들어준다.
  • GROUP BY 컬럼구에 두 개 이상이 오면 순서에 따라 결과가 달라진다.
SELECT DECODE(DEPTNO, NULL, '전체합계', DEPTNO) AS '부서'
     , SUM(SAL) AS '합계'
FROM EMP
GROUP BY ROLLUP (DEPTNO);

부서    | 합계
-------------
10     | 1300
20     | 1220
30     | 1500
전체합계 | 3020

2. GROUPING

  • ROLLUP, CUBE, GROUPING SETS에서 생성되는 합계값을 구분하기 위해 만들어진 함수.
  • GROUPING은 소계, 합계 일 때 1, 아니면 0.

Grouping

3. GROUPING SETS

  • GROUP BY 컬럼 순서와 관계 없이 다양한 소계를 만들 수 있다.
  • 순서가 달라도 결과는 같다.

Grouping Sets

4. CUBE

  • 조합할 수 있는 경우의 수를 모두 조합.

Cube

13. Window Functions 👩‍💻

행의 위치를 조작하는 함수를 Window Functions라 한다.

1. Structure of Window Functions

함수 설명
ARGUMENTS 0 ~ N 개의 인수를 설정
PARTITION BY 전체 집합을 기준에 의해 소그룹으로 나눈다.
ORDER BY 정렬
WINDOWING 행 기준 범위를 정한다.
ROWS : 물리적 결과의 행 수
RANGE : 논리적인 값에 의한 범위

2. WINDOWING

조건 설명
ROWS 물리적 단위로 행 집합을 지정
RANGE 논리적인 주소에 의해 행 집합을 지정
BETWEEN ~ AND 윈도우의 시작과 끝의 위치를 지정
UNBOUNDED PRECEDING 윈도우의 시작 위치가 첫 번째 행임을 의미
UNBOUNDED FOLLOWING 윈도우의 마지막 위치가 마지막 행임을 의미
CURRENT ROW 윈도우 시작 위치가 현재 행임을 의미

3. Window Function - RANK Function

순위 함수 결과 예시 설명
RANK 1, 2, 2, 2, 5, 6, 7 동일 순위는 동일한 값
DENSE_RANK 1, 2, 2, 2, 3, 4, 5 동일 순위를 하나의 건수로 계산
ROW_NUMBER 1, 2, 3, 4, 5, 6, 7 고유의 순위를 부여

4. Window Function - AGGREGATE Function

집계 함수 쿼리 예시 설명
SUM SUM(SAL) OVER ( PARTITION BY MGR ) 파티션 별 합
AVG AVG(SAL) OVER ( PARTITION BY MGR ) 파티션 별 평균
COUNT COUNT(SAL) OVER ( PARTITION BY MGR ) 파티션 별 행 수
MAX, MIN MAX(SAL) OVER ( PARTITION BY MGR ) 파티션 별 최댓값, 최솟값

5. Window Function - 행 순서 관련 함수

Window Functions

Window Function FIRST_VALUE

Window Function LAST_VALUE

Window Function LAG

Window Function LEAD

6. Window Function - 비율 관련 함수

비율 함수 설명
CUME_DIST 파티션 별로 최댓값 대비 값의 백분율 조회.
0 ~ 1 사이를 반환.
PERCENT_RANK 파티션 별로 행의 시작을 0, 행의 마지막을 1로 해서 행 순서로 등수의 백분율 조회(값에 의한 백분율 아님).
0 ~ 1 사이를 반환.
NTILE(n) 특정 컬럼을 n 등분한 결과를 조회.
1 ~ n 등급 정수 반환.
RATIO_TO_REPORT 파티션 별로 SUM 에 대한 값의 지분 백분율 조회.
0 ~ 1 사이를 반환.

Window Function CUME_DIST

Window Function PERCENT_RANK

같은 부서에서 자신의 급여 퍼센트(등수)를 구한다.

Window Function NTILE

NTILE(4), 즉 4개로 등분하여 분류한다. (i.e. 급여가 높은 순으로 4개로 등분한다)

Window Function RATIO_TO_REPORT

14. Optimizer 👩‍💻

1. Optimizer

  • 옵티마이저는 Data Dictionary에 있는 오브젝트 통계, 시스템 통계 등의 정보를 사용해 예상되는 비용을 산정하고 Execution Plan(실행 계획)을 수립하고 SQL을 실행한다.
  • 최저 비용을 가지는 계획을 산정해서 SQL을 실행한다.
  • PLAN_TABLE을 조회해 실행 계획을 확인할 수 있다.
  • 옵티마이저가 비효율적인 실행 계획을 수립하면 힌트(HINT) 를 사용해 실행 계획 변경을 요청할 수 있다.

Q) A 테이블, B 테이블이 있고 AND 연산(INNER JOIN)을 하려고 한다. 이때 A 테이블이 B 테이블보다 더 크다. 어떤 순서로 연산을 해야 비용이 더 적을까?
A) 더 작은 B 테이블을 먼저 읽고 A 테이블과 비교 해야 비용이 적게 든다.

2. Optimizer Procedures

SQL > Parsing > Optimizer from Data Dictionary > Execution Plan on PLAN_TABLE > SQL execute

Fetch

3. Optimizer Engines

엔진 설명
Query Transformer SQL을 효율적으로 실행하기 위해 옵티마이저가 변환
Estimator 통계 정보를 사용해 실행 비용을 계산
Plan Generator SQL 실행 계획(Execution Plan) 수립


  • 규칙 기반 옵티마이저 : 15개 우선 순위에 따라 처리한다.
순서 설명
1 ROWID(데이터 고유 주소)를 사용한 단일행인 경우
13 인덱스가 구성된 컬럼에서 MAX, MIN 을 구하는 경우
14 인덱스가 구성된 컬럼에서 ORDER BY 를 실행하는 경우
15 전체 테이블을 스캔(FULL TABLE SCAN)하는 경우

(시험 합격을 위해서는 15개를 전부 외울 필요는 없다)

  • 비용 기반 옵티마이저 : 오브젝트 통계 및 시스템 통계를 사용해서 총 비용을 계산한다.

💡최신 오라클은 규칙 기반 옵티마이저보다 비용 기반 옵티마이저를 default로 사용한다.

15. Index 👩‍💻

  • 기본키(Primary Key)는 자동으로 인덱스가 만들어지고 인덱스의 이름은 SYSXXXX이다.
  • 인덱스 키로 정렬되어 있기 때문에 빠른 조회가 가능하다.
  • 인덱스는 오름차순 및 내림차순 탐색이 가능하다.
  • 하나의 테이블에 여러 개의 인덱스를 생성할 수 있고 하나의 인덱스는 여러 개의 컬럼으로 구성될 수 있다.
  • Index 는 Root Block, Branch Block, Leaf Block 으로 구성
    Branch Block : Pointer로 되어있다.
    Leaf Block : 정렬된 인덱스 키와 ROWID로 구성. Double Linked List로 되어 양방향 탐색이 가능.
  • 오라클은 null을 인덱스 맨 뒤에 저장하고, SQL Server는 맨 앞에 저장한다.
CREATE INDEX IND_EMP ON EMP (ENAME ASC, SAL DESC);

IND_EMP : 인덱스 이름
EMP : 테이블 이름
ENAME ASC, SAL DESC : ENAME은 오름차순, SAL은 내림차순으로 인덱스 생성

16. Performance Characteristics 👩‍💻

1. Index Scan

  • Index Unique SCAN(인덱스 유일 스캔) : 인덱스의 키 값이 중복되지 않는 경우 발생.
  • Index Range SCAN(인덱스 범위 스캔) : LIKE, BETWEEN 등을 이용해 Leaf Block 의 특정 범위를 스캔.
  • Index Full SCAN(인덱스 전체 스캔) : 검색되는 인덱스 키가 많은 경우 Leaf Block 의 처음부터 끝까지 전체를 읽는다.

High Watermark : 테이블에 데이터가 저장된 블록에서 최상위 위치를 의미한다.

2. Optimizer Join

  • Nested Loop 조인
  • 하나의 테이블에서 데이터를 먼저 찾고 다음 테이블을 조인.
  • 먼저 조회하는 테이블을 Outer table, 나중에 조회하는 테이블을 Inner table 이라고 한다.
  • Random Access가 많이 발생.
  • Sort Merge 조인
  • SORT_AREA라는 메모리 공간에 모두 로딩 하고 Sort를 수행.
  • Sort 완료 후 두 테이블을 Merge.
  • 데이터 양이 많아지면 성능이 저하된다.
  • Hash 조인
  • 두 테이블 중 작은 테이블을 Hash 메모리에 로딩하고 두 테이블을 조인키를 이용해 해시 테이블을 생성.
  • CPU 연산을 많이 한다.
  • 선행 테이블이 충분히 메모리에 로딩되는 크기여야한다.
  • 조인 컬럼 인덱스가 존재하지 않을 경우에도 사용 가능.
  • = 동등 조건에서만 사용 가능.
  • 해시 테이블 저장시 메모리 초과 ⇒ 임시 영역(디스크)에 저장.



Reference

  1. 임호진. SQL 개발자. 영진닷컴, 2021.
  2. “ERD Editor.” Egovframe. Apr. 04, 2015, ERD Editor.
  3. “ERD.” 해시넷. Jun. 02, 2022, ERD.
  4. “SQL Natural Join.”Aug. 19, 2022, SQL Natural Join.