[License] 07. SQL응용 [정보처리기사 실기]

# 트랜잭션

데이터베이스 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위

 

트랜잭션의 특성

원자성 (Atomicity)

연산 전체가 성공 또는 실패, commit/rollback, 회복성보장

 

일관성 (Consistency)

트랜잭션이 실행 성공 후 항상 일관된 데이터베이스 상태를 보존해야하는 특성

 

격리성 (Isolation)

트랜잭션 실행중 연산의 중간 결과를 다른 트랜잭션이 접근 불가한 특성

 

영속성 (Durability)

성공이 완료된 트랜잭션의 결과는 영속적으로 데이터베이스에 저장하는 특성

 

트랜잭션의 상태 변화

활동 상태 (Active)

초기상태, 트랜잭션이 실행중일때 가지는 상태

 

부분 완료 상태 (Partially Committed)

마지막 명령문이 실행된 후에 가지는 상태 / commit은 아님

 

완료 상태 (Committed)

트랜잭션이 성공적으로 완료된 후 가지는 상태 / commit 상태

 

실패 상태 (failed)

정상적인 실행이 더 이상 진행될 수 없을때 가지는 상태

 

철회 상태 (Aborted)

트랜잭션 시작 전 상태로 황원된 상태 / Rollback 상태

 

트랜잭션 제어

트랜잭션 제어언어는 TCL (Transaction Control Language) 라고 하며

트랜잭션의 결과를 허용하거나 취소하는 목적으로 사용되는 언어이다

 

커밋 (Commit)

트랜잭션 확정 / 트랜잭션을 메모리에 영구적으로 저장하는 명령어

 

롤백 (Rollback)

트랜잭션 취소 / 트랜잭션 내역을 저장 무효화 시키는 명령어

 

체크포인트 (CheckPoint)

저장 시기 설정 / Rollback을 위한 시점을 저장하는 명령어

 

병행제어

다수 사용자 환경에서 여러 트랜잭션을 수행할 때 데이터베이스 일관성을 유지하기 위해

상호작용을 제어하는 기법

 

병행제어의 목적

데이터베이스 공유를 최대화하고

시스템 활용도를 최대화한다

일관성을 유지하고 응답시간을 최소화한다

 

병행 제어 미보장 시 문제점

갱신 손실 (Lost Update)

먼저 실행된 트랜잭션의 결과를 나중에 실행된 트랜잭션이 덮어쓸때 발생하는 오류

 

현황 파악오류 (Dirty Read)

트랜잭션의 중간 수행 결과를 다른 트랜잭션이 참조하여 발생하는 오류

 

모순성 (Inconsistency)

두 트랜잭션이 동시에 실행되어 데이터베이스의 일관성이 결여되는 오류

 

연쇄복귀 (Cascading Rollback)

복수의 트랜잭션이 데이터 공유 시 특정 트랜잭션이 처리를 취소할때

트랜잭션이 처리한 곳을 취소하지 못하는 오류

 

병행 제어 기법의 종류

로킹 (Locking)

다중 트랜잭션 환경에서 DB의 일관성과 무결성을 유지하기 위해

트랜잭션의 순차적 진행을 보장하는 직렬화 기법

 

낙관적 검증 (Validation)

트랜잭션이 어떠한 검증도 수행하지 않고 일단 트랜잭션을 수행하고

트랜잭션 종료시 검증하여 데이터베이스에 반영하는 기법

 

타임 스탬프 순서 (Time Stamp Ordering)

트랜잭션이 실행을 시작하기 전에 타임스탬프를 부여하여

부여된 시간에따라 트랜잭션 작업을 수행하는 기법

 

다중버전 동시성 제어 (MVCC : Multi Version Concurrency Control)

트랜잭션의 타임스탬프와 접근하려는 데이터의 타임스탬프를 비교하여

직렬가능성이 보장되는 적절한 버전을 선택하여 접근하도록 하는 기법

 

고립화수준

다른 트랜잭션이 현재의 데이터에 대한 무결성을 해치지 않기위해

잠금을 성정하는 정도이다

 

Read Uncommitted

한 트랜잭션에서 연산중인 데이터를 다른 트랜잭션이 읽는것을 허용하는 수준

 

Read Committed

한 트랜잭션에서 연산을 수행할 때 연산이 완료될때까지 읽기를 제한하는 수준

 

Repeatable Read

선행 트랜잭션이 특정 데이터를 읽을때 트랜잭션 종료 시까지

해당 데이터에 대한 갱신 / 삭제를 제한하는 수준

 

Serializable Read

선행 트랜잭션이 특정 데이터 영역을 순차적으로 읽을때

해당 데이터 영역 전체에 대한 접근을 제한하는 수준

 

회복기법

트랜잭션을 수행하는 도중 장애로 인해 손상된 데이터 베이스를 손상되기

이전의 상태로 복구시키는 작업

 

회복기법의 종류

로그 기반 회복기법

1) 지연 갱신 회복 기법 (Deferred Update)

트랜잭션이 완료되기 전까지 데이터베이스에 기록하지 않는 기법

 

2) 즉각 갱신 회복 기법 (Immediate Update)

트랜잭션 수행 중 갱신 결과를 DB에 반영하는 기법

 

체크 포인트 회복기법 (CheckPoint Recovery)

장애 발생 시 검사점 이후에 처리된 트랜잭션에 대해서만

장애 발생 이전의 상태로 복원시키는 회복 기법

 

그림자 페이징 회복 기법 (Shadow Paging Recovery)

트랜잭션 수행 시 복제본을 생성하여 데이터베이스 장애 시 이를 이용해 복구하는 기법

 

데이터정의어 (DDL : Data Definition Language)

데이터를 정의하는 언어로서 데이터를 담는 그릇을 정의하는 언어이다

생성, 변경, 삭제 등과 관련된 명령어들을 데이터정의어 라고 한다

 

DDL의 대상

도메인

하나의 속성이 가질 수 있는 원자값들의 집합

 

스키마

데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조

1) 외부 스키마 (External Schema)

사용자나 개발자의 관점에서 필요로 하는 데이터베이스의 논리적 구조

 

2) 개념 스키마 (Conceptual Schema)

데이터베이스의 전체적인 논리적 구조, 전체적인 뷰를 나타냄

 

3) 내부 스키마 (Internal Schema)

물리적 저장장치의 관점에서 보는 데이터베이스 구조

 

테이블 (Table)

데이터 저장공간

데이터를 저장하는 항목인 필드 들로 구성된 데이터 집합체

 

튜플 (Tuple) / 행 (Row) / 카디널리티 (Cardinality)

테이블 내의 행을 의미하며 레코드 (Record) 라고도 한다

튜플은 릴레이션에서 같은 값을 가질 수 없다

행의 개수를 카디널리티 (Cardinality) 라고 한다

 

애트리뷰트 (Atrribute) / 열 (Column) / 차수 (Degree)

테이블 내의 열을 의미하며 열의 개수를 차수 (Degree)라고 한다

 

식별자 (Identifier)

여러 개의 집합체를 담고 있는 관계형 데이터베이스에서 각각의 구분할 수 있는 논리적인 개념

 

도메인 (Domain)

하나의 애트리뷰트가 취할 수 있는 같은 타입의 원자값 들의 집합

 

뷰 (View)

하나 이상의 물리 테이블에서 유도되는 가상의 테이블

논리 테이블로서 사용자에게 테이블과 동일하다

 

뷰의 특징

논리적 데이터 독립성 제공

데이터베이스에 영향을 주지 않고 애플리케이션이 원하는 형태로 데이터에 접근 가능하다

 

데이터 조작 연산 간소화

애플리케이션이 원하는 형태의 논리적 구조를 형성하여 데이터 조작 연산을 간호화

 

보안 기능 제공

뷰를 생성할때 선택되지 않은 필드의 조회 및 접근이 불가하다

 

뷰 변경불가

Alter문을 이용하여 변경할 수 없다 오직 Drop후 다시 Create 해야한다

 

인덱스 (Index)

검색을 빠르게 하기 위한 데이터 구조 / 조회 속도를 높여주는 자료구조 이다

 

인덱스의 종류

순서 인덱스 (Ordered Index)

데이터가 정렬된 순서로 생성되는 인덱스

 

해시 인덱스 (Hash Index)

해시 함수에 의해 직접 데이터에 키 값으로 접근하는 인덱스

 

비트맥 인덱스 (Bitmap Index)

각 컬럼에 적은 개수 값이 저장된 경우 선택하는 인덱스

 

함수 기반 인덱스 (Functional Index)

수식이나 함수를 적용하여 만든 인덱스

 

단일 인덱스 (Singled Index)

하나의 컬럼으로만 구성한 인덱스

 

결합 인덱스 (Concatenated Index)

두 개 이상의 컬럼으로 구성한 인덱스

 

클러스터드 인덱스 (Clutered Index)

기본키 (PK) 기준으로 레코드를 묶어서 저장하는 인덱스

 

인덱스 스캔 방식

인덱스 범위 스캔 (Index Range Scan)

필요한 범위만 스캔하는 방식

 

인덱스 전체 스캔 (Index Full Scan)

처음부터 끝까지 수평적으로 탐색하는 방식

 

인덱스 단일 스캔 (Index Unique Scan)

수직적 탐색만으로 데이터를 찾는 스캔 방식

 

인덱스 생략 스캔 (Index Skip Scan)

선두 컬럼이 조건 절에 빠졌어도 인덱스를 활용하는 스캔방식

 

DDL 명령어

생성 (Create)

생성하는 명령어

 

CREATE TABLE 테이블명 { 컬럼명 데이터타입 [제약조건] }

ex) CREATE TABLE 과일 { 사과 VARCHAR(2) PRIMARY KEY }

 

Primary Key

테이블의 기본키를 정의

 

Foreign Key

외래 키를 정의

 

Unique

테이블 내에서 얻은 유일한 값을 갖도록 하는 제약조건

 

Not null

Null값을 포함하지 않도록 하는 제약조건

 

Check

개발자가 정의하는 제약조건 / 참 이어야 하는 조건을 지정

 

Default

기본값으로 설정해 주는 제약조건

 

수정 (Alter)

변경하는 명령어

 

추가

ALTER TABLE 테이블명 ADD 컬럼명 데이터 타입 [제약조건];

ex) ALTER TABLE 과일 ADD 이름 VARCHAR(10) UNIQUE;

 

수정

ALTER TABLE 테이블명 MODIFY 컬럼명 데이터 타입 [제약조건];

ex) ALTER TABLE 과일 MODIFY이름 VARCHAR(10) NOT NULL;

 

삭제

ALTER TABLE 테이블명 DROP 컬럼명;

ex) ALTER TABLE 과일 DROP 이름;

 

삭제 (Drop)

삭제하는 명령어

 

DROP TABLE 테이블명 [CASCADE | RESTRICT]

ex) DROP TABLE 과일;

 

CASCADE

참조하는 테이블까지 연쇄적으로 제거하는 옵션

 

RESTRICT

다른 테이블이 삭제할 테이블을 참조 중이면 제거하지 않는 옵션

 

삭제 (Truncate)

내용을 삭제하는 명령어

 

TRUNCATE TABLE 테이블명;

ex) TRUNCATE TABLE 과일;

 

VIEW 관련 DDL

CREATE VIEW 뷰이름 AS 조회쿼리;

ex) CREATE VIEW 과일뷰 AS SELECT 색깔, 크기 FROM 과일 WHERE 색깔 = '빨강';

 

CREATE OR REPLACE VIEW 뷰이름 AS 조회쿼리;

뷰를 교체하는 명령어

 

DROP VIEW 뷰이름

ex) DROP VIEW 과일뷰

 

INDEX 관련 DDL

CREATE INDEX 인덱스명 ON 테이블명 (컬럼명, 컬럼명, ...);

ex) CREATE INDEX 과일인덱스 ON 과일 (색깔);

 

ALTER INDEX 인덱스명 ON 테이블명 (컬럼명, 컬럼명, ...);

ex) ALTER INDEX 과일인덱스 ON 과일 (색깔);

 

DROP INDEX 인덱스명;

ex) DROP INDEX 과일인덱스;

 

데이터 조작어 (DML : Data Manipulation Language)

데이터베이스에 저장된 자료들을 입력, 수정, 삭제, 조회 하는 언어이다

 

조회 (SELECT)

데이터를 조회

 

삽입 (INSERT)

데이터를 추가

 

INSERT INTO 테이블명 (속성명 ...) VALUE (데이터 ...);

ex) INSERT INTO 과일 (이름, 색깔, 크기) VALUE ('사과', '빨간색', '30'); 

 

갱신 (UPDATE)

데이터를 수정

 

UPDATE 테이블명 SET 속성명 = 데이터 WHERE 조건;

ex) UPDATE 과일 SET 이름 = '사과' WHERE 색깔 = '노란색';

 

삭제 (DELETE)

데이터를 삭제

 

DELETE FROM 테이블명 WHERE 조건;

ex) DELETE FROM 과일 WHERE 이름 = '사과';

 

SELECT 속성명 FROM 테이블명 WHERE 조건 GROUP BY 속성명 HAVING 그룹조건 ORDER BY 속성

 

ALL

모든 튜플을 검색할 때 사용

 

DISTINCT

중복된 속성이 조회될 경우 그중 한 개만 검색

 

조인 (JOIN)

두 개 이상의 테이블을 연결하여 데이터를 검색하는 방법

 

내부 조인 (INNER JOIN)

양쪽 테이블에 공통으로 가지고있는 값을 추출한다

 

외부 조인 (OUTER JOIN)

왼쪽 외부 조인 (LEFT OUTER JOIN)

왼쪽 테이블의 모든 값 추출하고 오른쪽 테이블은 왼쪽과 공통으로 가진 값을 추출한다

 

오른쪽 외부 조인 (RIGHT OUTER JOIN)

오른쪽 테이블의 모든 값 추출하고 왼쪽 테이블은 오른쪽과 공통으로 가진 값을 추출한다

 

완전 외부 조인 (FULL OUTER JOIN)

양쪽테이블의 모든 값 추출한다

 

교차 조인 (CROSS JOIN)

조인 조건 없이 모든 데이터 조합을 추출한다

 

셀프 조인 (SELF JOIN)

자기 자신에게 별칭을 지정한 후 다시 조인하는 기법

 

교차조인을 제외한 모든 조인문

SELECT A.컬럼, B.컬럼 FROM 테이블 A INNER JOIN 테이블 B ON 조인조건 WHERE 검색 조건;

 

교차조인은 ON이 없다

SELECT A.컬럼, B.컬럼 FROM 테이블 A CROSS JOIN 테이블 B;

 

서브쿼리 (Sub-Query)

SQL문 안에 포함된 또 다른 SQL 문 이다

 

SELECT 절 서브쿼리

서브쿼리가 SELECT절 안에 들어있는 형태

스칼라 서브쿼리 라고도 불린다

반드시 단일 행을 리턴해야 한다

 

SELECT (SELECT SUM(가격) as 가격

                 FROM 과일 A

                 WHERE A.과일번호 = B.과일번호

                 AND 과일명 = '사과')

FROM 과일가격 B;

 

FROM 절 서브쿼리

서브쿼리가 FROM절 안에 들어있는 형태

인라인 뷰 라고도 불린다

뷰 처럼 결과가 동적으로 생성된 테이블 형태로 사용할 수 있다

 

SELECT SUM(가격) as 가격

FROM 과일 가격 A, ( SELECT 과일번호

                                  FROM 과일

                                  WHERE 과일명 = '사과') B

WHERE A.과일 번호 = B.과일번호;

 

WHERE 절 서브쿼리

서브쿼리가 WHERE절 안에 들어있는 형태

중첩 서브쿼리 라고도 불린다

 

SELECT SUM(가격) as 가격

FROM 과일 가격

WHERE 과일번호 IN ( SELECT 과일번호

                                     FROM 과일

                                     WHERE 과일명 = '사과');

 

집합 연산자 (Set Operator)

테이블을 집합 개념으로 보고 두 테이블 연산에 집합 연산자를 사용하는 방식이다

 

합집합 (UNION)

중복 행이 제거된 쿼리 결과를 반환하는 집합 연산자

 

합집합 + 중복포함 (UNION ALL)

중복 행이 제거되지 않은 쿼리 결과를 반환하는 집합 연산자

 

교집합 (INTERSECT)

두 쿼리 결과에 공통적으로 존재하는 결과를 반환하는 집합 연산자

 

차집합 (MINUS)

첫 쿼리에는 있고 두 번째 쿼리에는 없는 결과를 반환하는 집합 연산자

 

데이터 제어어 (DCL : Data Control Language)

데이터 베이스 관리자가 데이터 보안, 무결성 유지, 병행제어, 회복을 위해

관리자 (DBA)가 사용하는 제어용 언어이다

 

사용 권한 부여 (GRANT)

권한을 부여하는 명령어

 

GRANT 권한 ON 테이블 TO 사용자;

ex) GRANT SELECT ON 과일 TO 사과;

 

사용 권한 취소 (REVOKE)

권한을 회수하는 명령어

 

REVOKE 권한 ON 테이블 FROM 사용자;

ex) REVOKE SELECT ON 과일 FROM 사과;

 

WITH GRANT OPTION

사용자에게 준 권한을 그 사용자도 다른 사용자에게 줄 수 있게 권한을 주는것 

 

GRANT CONSTRAINT

권한을 회수할때 사용자가 다른 사용자에게 준 권한까지 회수하는것

 

# 집계성 SQL

총합, 평균 등의 데이터 분석을 위해서 복수행 기준의 데이터를 모아서

처리하는 것을 목적으로 하는 다중 행 함수

 

집계 함수 (Aggregate Function)

여러 행 또는 테이블 전체 행으로부터 하나의 결과값을 반환하는 함수

ex) 합계, 평균

 

COUNT

행의 수를 반환하는 함수

 

SUM

컬럼 간의 합계를 계산하는 함수

 

AVG

컬럼 간의 평균을 계산하는 함수

 

MAX

컬럼 중 최댓값을 계산하는 함수

 

MIN

컬럼 중 최솟값을 계산하는 함수

 

STDDEV

컬럼 간의 표준편차를 계산하는 함수

 

VARIAN

컬럼 간의 분산을 계산하는 함수

 

그룹 함수 (Group Function)

소그룹 간의 소계 및 중계 등의 중간 합계 분석 데이터를 산출하는 함수

 

ROLLUP

필요한 소계만 출력한다

지정 컬럼의 수 보다 하나 더 큰 레벨만큼의 중간 집계 값을 생성

ex) 부서별 연봉 을 줄세우고 부서의 마지막에 소계(합계)로 총 연봉을 나타내고

마지막에 총 소계(합계)를 나타낸다

 

CUBE

소계란 소계는 다 출력한다

결합 가능한 모든 값에 대해 다차원 집계를 생성

ex) ROLLUP에서 했듯이 부서별 연봉은 물론이고 더 나아가 각 직급별 소계(합계) 도 나타낸다

 

GROUPING SETS

소계만 있는것

개별 집계를 구할 수 있으며

ROLLUP과 CUBE와 달리 컬럼 간 순서와 무관한 결과를 얻을 수 있다

ex) 각 부서별 직급별 소계(합계) 만 나타낸다 즉 개개인별 각각 부서별 값은 생략한다

 

윈도 함수 (Window Function)

온라인 분석 처리 용도로 사용하기 위해서 표준 SQL에 추가된 기능

 

순위 함수

레코드 순위를 계산하는 함수

 

RANK

공동 순위 반영

ex) 1위, 1위, 1위, 4위, 5위

 

DENSE_RANK

공동 순위 무시

ex) 1위, 1위, 1위, 2위, 3위

 

ROW_NUMBER

공동 순위 완전 무시

ex) 1위, 2위, 3위, 4위, 5위

 

SELECT 이름, 크기,

               RANK( ) OVER (ORDER BY 가격 DESC) A,

               DENSE_RANK( ) OVER (ORDER BY 가격 DESC) B,

               ROW_NUMBER( ) OVER (ORDER BY 가격 DESC) C

FROM 과일;

 

행 순서 함수

레코드에서 가장 먼저 나오거나 가장 뒤에 나오는 값, 이전 값 이후 값 출력하는 함수

 

FIRST_VALUE

파티션별 윈도에서 가장 먼저 나오는 값을 찾는다

내림차순 일때는 MAX와 동일한 결과

오름차순 일때는 MIN과 동일한 결과

 

LAST_VALUE

파티션별 윈도에서 가장 늦게 나오는 값을 찾는다

내림차순 일때는 MIN과 동일한 결과

오름차순 일때는 MAX와 동일한 결과

 

LAG

파티션별 윈도에서 이전 로우의 값 반환

 

LEAD

파티션별 윈도에서 이후 로우의 값 반환

 

SELECT 이름, 크기,

               FIRST_VALUE(이름) OVER (ORDER BY 가격 DESC) A,

               LAST_VALUE(이름) OVER (ORDER BY 가격 DESC) B,

               LAG(이름) OVER (ORDER BY 가격 DESC) C,

               LEAD(이름) OVER (ORDER BY 가격 DESC) D

FROM 과일;

 

그룹 내 비율 함수

백분율을 보여주거나 행의 순서별 백분율 등 비율과 관련된 통계를 보여주는 함수

 

RATIO_TO_REPORT

주어진 그룹에 대해 합을 기준으로 각 로우의 상대적 비율을 반환하는 함수

결괏값은 0~1의 범위 값을 가진다

 

PERCENT_RANK

주어진 그룹에 대해 제일 먼저 나오는 것을 0으로

제일 늦게 나오는 것을 1로 하여 값이 아닌 행의 순서별 백분율을 구하는 함수

결괏값은 0~1의 범위 값을 가진다

 

SELECT 이름, 크기,

               RATIO_TO_REPORT(가격) OVER ( ) A,

               PERCENT_RANK( ) OVER (ORDER BY 가격 DESC) B

FROM 과일;

 

# 절차형 SQL

일반적인 개발 언어처럼 SQL 언어에서도 절차 지향적인 프로그램이 가능하도록 하는 트랜잭션 언어이다

 

프로시저 (Procedure)

일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합

 

선언부 (DECLARE)

시작/종료부 (BEGIN/END)

제어부 (CONTROL)

SQL

예외부 (EXCEPTION)

실행부 (TRANSACTION)

 

사용자 정의함수 (User-Delined Function)

일련의 SQL 처리를 수행하고 수행결과를 단일 값으로 반환할 수 있는 절차형 SQL

리턴 함수가 필요하다

 

선언부 (DECLARE)

시작/종료부 (BEGIN/END)

제어부 (CONTROL)

SQL

예외부 (EXCEPTION)

반환부 (RETURN)

 

트리거 (Trigger)

데이터베이스 시스템에서 삽입, 삭제, 갱신 등의 이벤트가 발생할 때마다

관련 작업이 자동으로 수행되는 절차형 SQL

 

행 트리거

데이터 변화가 생길 때마다 실행

 

문장 트리거

트리거에 의해 단 한 번 실행

 

선언부 (DECLARE)

이벤트부 (EVENT)

시작/종료부 (BEGIN/END)

제어부 (CONTROL)

SQL

예외부 (EXCEPTION)

 

DBMS_OUTPUT 패키지

메시지를 버퍼에 저장하고 버퍼로부터 메시지를 읽어오기 위한 인터페이스 패키지이다

 

DBMS_OUTPUT(문자열);

개행 없이 문자열을 출력하는 프로시저

 

DBMS_OUTPUT.PUT_LINE(문자열);

문자열을 출력 후 개행하는 프로시저

 

IF문

참인지 거짓인지에 따라 경로를 선택하는 조건문

 

IF문 조건 THEN

   문장;

ELSIF 조건 THEN

   문장;

ELSE

   문장;

END IF;

 

간단한 케이스 문 (Simple Case Expression)

명확한 값을 가지는 조건에 따라 여러개의 선택 경로중 하나를 취하고자 할때 사용하는 조건문

 

CASE 컬럼명

   WHEN 값 1 THEN

      SET 명령어;

   WHEN 값 2 THEN

      SET 명령어;

   ELSE

      SET 명령어;

END CASE;

 

검색된 케이스 문 (Searched Case Expression)

명확한 값 및 범위를 가지는 조건에 따라 여러 개의 선택 경로 중 하나를 취하고자 할 때 사용하는 조건문

 

CASE 컬럼명

   WHEN 값 1 THEN

      SET 명령어;

   WHEN 값 2 THEN

      SET 명령어;

   ELSE

      SET 명령어;

END CASE;

 

반복문

LOOP문

특정 조건이 만족될 때까지 반복해서 문장을 실행하는 반복문

 

   문장;

   EXIT WHEN 탈출조건;

END LOOP;

 

WHILE문

시작과 종료 조건을 지정하여 참인 동안에는 해당 문장을 반복해서 실행하는 명령문

 

WHILE 반복 조건 LOOP

   문장;

EXIT WHEN 탈출조건;

END LOOP;

 

FOR LOOP문

시작 값과 끝값을 지정하여 해당 값이 그 구간 내에 있을때 반복하는 반복문

 

FOR 인덱스 IN 시작값 ... 종료값

LOOP

   문장;

END LOOP;

 

예외부 (Exception)

실행 중 발생 가능한 예외상황을 수행하는 부분이다

 

EXCEPTION

   WHEN 조건 THEN

      SET 명령어;

 

# 쿼리 성능 개선(튜닝)

데이터베이스에서 프로시저에 있는 SQL 실행 계획을 분석, 수정을 통해

최소의 시간으로 원하는 결과를 얻도록 프로시저를 수정하는 작업이다

 

SQL 성능 개선 절차

1) 문제있는 SQL 식별

2) 옵티마이저 통계 확인

3) SQL문 재구성

4) 인덱스 재구성

5) 실행계획 유지관리

 

옵티마이저 (Optimizer)

SQL을 가장 빠르고 효율적으로 수행할 최적의 처리 경로를 생성해주는

DBMS 내부의 핵심엔진

 

규칙기반 옵티마이저 (RBO : Rule Based Optimizer)

통계 정보가 없는 상태에서 사전 등록된 규칙에 따라

질의 실행 계획을 선택하는 옵티마이저

 

비용기반 옵티마이저 (CBO : Cost Based Optimizer)

통계 정보로부터 모든 접근 경로를 고려한

질의실행 계획을 선택하는 옵티마이저

 

SQL수행 시 옵티마이저 역할

쿼리변환 (Query Transformer)

SQL을 좀 더 일반적이고 표준화된 형태로 변환

 

비용 산정 (Estimator)

쿼리 명령어 각 단계의 신뢰도, 카디널리티, 비용을 계산

 

계획 생성 (Plan Generator)

하나의 쿼리를 수행 시 후보군이 될 만한 실행계획들을 생성해내는 역할

 

힌트 (Hint)

실행하려 하는 SQL문에 사전에 정보를 주어서 SQL 문 실행에

빠른 결과를 가져오는 효과를 만드는 문법이다

 

힌트 작성법

/*+ */

 

주요 옵티마이저 힌트

/*+ RULE */

규칙 기반 접근 방식을 사용하도록 지정

 

/*+ CHOOSE */

오라클 옵티마이저 디폴트 값에 따름

 

/*+ INDEX(테이블명 인덱스명) */

지정된 인덱스를 강제적으로 사용하도록 지정

 

/*+ USE_HASH(테이블명) */

지정된 테이블들의 조인이 Hash Join 형식으로 일어나도록 유도

 

/*+ USE_MERGE(테이블명) */

지정된 테이블들의 조인이 Sort Merge 형식으로 일어나도록 유도

 

/*+ USE_NL(테이블명) */

지정된 테이블들의 조인이 Nested Loop 형식으로 일어나도록 유도

 

해시조인 (Hash Join)

해싱 함수 기법을 활용하여 조인을 수행하는 방식

 

정렬 합병 조인 (Sort Merge Join)

조인의 대상범위가 넓을 경우 발생하는 임의 접근을 줄이기 위한 경우나

연결고리를 마땅한 인덱스가 존재하지 않을 경우 해결하기 위한 존인 방법

 

중첩 반복 조인 (Nested Loop Join)

2개 이상의 테이블에서 하나의 집합을 기준으로 순차적으로

상대방 Row를 결합하여 원하는 결과를 조합하는 방식