정처기 실기 파트7

7  SQL 응용

I.    데이터베이스 기본

1.   트랜잭션

(1)  트랜잭션(Transaction)

-     개념 : 데이터를 보장하기 위한 특성이자, 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위

-     특성 : ACID

①  Atomicity : 분해가 불가능한 작업의 최소단위 / 연산 전체가 성공 또는 실패 / 하나라도 실패 시 전체가 취소

②  Consistency : 트랜잭션이 실행 성공 후 항상 일관된 데이터베이스 상태를 보존

③  Isolation : 트랜잭션 실행 중 생성하는 연산의 중간 결과를 다른 트랜잭션이 접근 불가능한 특성

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

-     상태 변화 : 활부완실철(활동 상태, 부분 완료 상태, 완료 상태, 실패 상태, 철회 상태)

-     제어 : TCL명령어(커롤체) : Commit, Rollback, Checkpoint

-     병행 제어(Concurrency Control) : 일관성 주요 기법

①  개념 : DB 일관성 유지를 위한 상호작용 제어 기법

②  목적 : DB 공유 최대화, 시스템의 활용도 최대화, DB 일관성 유지, 사용자에 대한 응답시간 최소화

③  병행 제어 미보장 시 문제점 : 갱현모연(갱신 손실, 현황 파악오류, 모순성, 연쇄복귀)

④  병행 제어 기법의 종류 : 로낙타다(로킹, 낙관적 검증, 타임 스탬프 순서, 다중버전 동시성 제어)

-     데이터베이스 고립화 수준(Isolation Level) : 격리성 주요 기법

①  개념 : 무결성을 해치지 않게 잠금 설정

②  종류

Read Uncommitted : 연산(갱신)중인 데이터를 읽을 수는 있으나 연산은 불가
Read Committed : 연산 완료 전까지 데이터 읽기 제한
Repeatable Read : 트랜잭션 종료 시 까지 해당 데이터 갱신, 삭제 제한
Serializable Read : 해당 데이터 영역 전체에 대한 접근 제한

-     회복 기법(Recovery) : 영속성 주요 기법

①  개념 : 수행 도중 장애로 손상된 데이터베이스를 손상 되기 전 상태로 복구

②  종류 : 회로체크

로그 기반 회복 기법 : 지연 갱신 회복 기법(Deferred Update), 즉각 갱신 회복 기법(Immediate Update)
체크 포인트 회복 기법(CheckPoint Recovery)
그림자 페이징 회복 기법(Shadow Paging Recovery)

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

-     개념 : 데이터 구조 정의에 사용되는 명령어

-     DDL의 대상 : 도스테뷰인(도메인, 스키마, 테이블, 뷰, 인덱스)

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

②  Schema : DB의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조
  External Schema : 사용자나 개발자의 관점에서 필요로 하는 DB의 논리적 구조, ‘서브스키마’라고도 함
  Conceptual Schema : DB 전체적인 논리적 구조, 개체 간 관계, 제약조건, 접근 권한, 무결성, 보안에 대해 정의
  Internal Schema : 물리적 저장장치 관점에서 보는 DB 구조

③ Table

1. Table (= Relation = Entity)
2. Field : 데이터 저장 항목
3. Column (= Attribute) : 열
4. Row (=Tuple) : 행
5. Identifier : 식별자
6. Cardinarlity(카디널리티) : Tuple의 개수
7. Degree(차수) : Attribute의 개수
8. Domian : 하나의 애트리뷰트가 취할 수 있는 같은 타입의 원자값들 집합

④  View : 논리(=가상)의 테이블
특징 : 논리적 데이터 독립성 제공, 데이터 조작 연산 간소화, 보안 기능(접근제어) 제공, 뷰 변경 불가

⑤  Index : 데이터를 빠르게 찾을 수 있는 수단
특징 : 기본키(PK;Primary Key) 컬럼은 자동으로 인덱스 생성
종류 : 순해비함단결클(순서 인덱스;Ordered Index, 해시 인덱스;Hash Index, 비트맵 인덱스;Bitmap Index, 함수기반 인덱스;Functional Index, 단일 인덱스;Single Index, 결합 인덱스;Concatenated Index, 클러스터드 인덱스;Clusted Index)
스캔방식 : 인덱스 범위 스캔, 인덱스 전체 스캔, 인덱스 단일 스캔, 인덱스 생략 스캔

-     DDL 명령어 : Create, Alter, Drop, Truncate

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

-     개념 : DB에 저장된 자료를 입력, 수정, 삭제, 조회하는 언어

-     Select Insert, Update, Delete

(4)  데이터 제어어(DCL;Date Control Language)

-     개념 : DB관리자가 데이터 보안, 무결성 유지, 병행제어, 회복을 위한 제어용 언어

-     Grant, Revoke

II.   응용 SQL 작성하기

1.   집계성 SQL 작성

(1)  집계함수(Aggregate Function)

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

-     구문

SELECT 컬럼1, 컬럼2, …, 집계함수
  FROM 테이블명
[WHERE 조건]
GROUP BY 컬럼1, 컬럼2, …
[HAVING 조건식(집계함수 포함)]

-     종류

①  COUNT : 복수 행의 줄 수 반환

②  SUM : 복수 행의 해당 컬럼 간의 합계를 계산

③  AVG : 복수 행의 해당 컬럼 간의 평균을 계산

④  MAX : 복수 행의 해당 컬럼 중 최대값을 계산

⑤  MIN : 복수 행의 해당 컬럼 중 최소값을 계산

⑥  STDDEV : 복수 행의 해달 컬럼 간의 표준편차를 계산

⑦  VARIAN : 복수 행의 해당 컬럼 간의 분산을 계산

(2)  그룹함수(Group Function)

-     개념 : 하나 이상의 컬럼을 기준으로 컬럼 값에 따라 그룹화 하여 그룹별로 출력

-     유형

①  ROLLUP : 지정 컬럼 수보다 하나 더 큰 레벨만큼의 중간 집계 값 생성 / 계층별로 구성되어 순서가 바뀌면 결과가 바뀜

②  예시

부서명(DEPT)	직위(JOB)	연봉(SALARY)
마케팅부	부장	4,000
마케팅부	차장	2,000
마케팅부	과장	1,500
기획부	부장	3,800
기획부	차장	1,800

SELECT DEPT, JOB SUM(SALARY)
  FROM DEPT_SALARY
[WHERE …]
GROUP BY [컬럼, …] ROLLUP (DEPT,JOB)
[HAVING  …]
[ORDER BY …];

부서명(DEPT)	직위(JOB)	연봉(SALARY)
마케팅부	부장	4,000
마케팅부	차장	2,000
마케팅부	과장	1,500
마케팅부		7,500
기획부	부장	3,800
기획부	차장	1,800
기획부		5,600
		13,100

 

③  CUBE함수 : 결합 가능한 모든 값에 대한 다차원 집계 생성 그룹 함수, 연산이 많이 시스템에 부담, 컬럼 간 순서에 따라 결과 다름

④  예시

부서명(DEPT)	직위(JOB)	연봉(SALARY)
마케팅부	부장	4,000
마케팅부	차장	2,000
마케팅부	과장	1,500
기획부	부장	3,800
기획부	차장	1,800

SELECT DEPT, JOB SUM(SALARY)
  FROM DEPT_SALARY
[WHERE …]
GROUP BY [컬럼, …] CUBE (DEPT,JOB)
[HAVING  …]
[ORDER BY …];

부서명(DEPT)	직위(JOB)	연봉(SALARY)
		13,100
	부장	7,800
	차장	3,800
	과장	1,500
마케팅부		7,500
마케팅부	부장	4,000
마케팅부	차장	2,000
마케팅부	과장	1,500
기획부		5,600
기획부	부장	3,800
기획부	차장	1,800

 

⑤  GROUPING SETS함수 : 집계 대상 컬럼들에 대한 개별 집계 구할 수 있음, 컬럼 간 순서와 무관한 결과

⑥  예시

부서명(DEPT)	직위(JOB)	연봉(SALARY)
마케팅부	부장	4,000
마케팅부	차장	2,000
마케팅부	과장	1,500
기획부	부장	3,800
기획부	차장	1,800

SELECT DEPT, JOB SUM(SALARY)
  FROM DEPT_SALARY
[WHERE …]
GROUP BY [컬럼명1, 컬럼명2, …] GROUPING SETS (DEPT,JOB,())
[HAVING  …]
[ORDER BY …];

부서명(DEPT)	직위(JOB)	연봉(SALARY)
		13,100
	부장	7,800
	차장	3,800
	과장	1,500
마케팅부		7,500
기획부		5,600

 

(3)  윈도 함수

-     개념 : 온라인 분석 처리 용도, OLAP 함수 라고도 함

-     구문

SELECT 함수명(파라미터)
  OVER
  ([PARTITION BY 컬럼1, …]
  [ORDER BY 컬럼A, …])
  FROM 테이블명

-     분류 : 순행비(순위 함수, 행 순서 함수, 그룹 내 비율 함수)

①  순위 함수 : RANK(2위, 2위, 2위, 5위, 6위) / DENSE_RANK(2위, 2위, 2위, 3위, 4위) / ROW_NUMBER(2위, 3위, 4위, 5위, 6위)

②  행 순서 함수 : FIRST_VALUE(가장 먼저 나오는 값) / LAST_VALUE(가장 마지막 나오는 값) / LAG(이전 로우 값) / LEAD(이후 로우 값)

③  그룹 내 비율 함수 : RATIO_TO_REPORT(각 로우의 상대적 비율, 0~1 값, OVER안 컬럼 생략시 테이블 전체) / PERCENT_RANK(행의 순서별 백분율로 빠르면 0 느리면 1 , 0~1 값)

III.  절차형 SQL 활용하기

1.   절차형 SQL(Procedural Language)

(1)  개념 : 절차 지향적인 프로그램이 가능하도록 하는 트랜잭션 언어

(2)  종류

-     프로시저(Procedure) : 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합

-     사용자 정의함수(User-Defined Function) : 일련의 SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL

-     트리거(Trigger) : 삽입, 갱신, 삭제등의 이벤트가 발생할 때 마다 관련 작업이 자동으로 수행되는 절차형 SQL

(3)  출력부

-     DBMS_OUTPUT 패키지 개념 : 메시지 버퍼에 저장 후 버퍼로부터 메시지 읽어오는 인터페이스 패키지

-     종류 : DBMS_OUTPUT.PUT(문자열); / DBMS_OUTPUT.PUT_LINE(문자열); [개행0]

(4)  제어부(Control)

-     조건문

①  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문

LOOP
  문장1;
  EXIT WHEN 탈출조건;
END LOOP;

②  WHILE문

WHILE 반복 조건 LOOP
  문장;
EXIT WHEN 탈출조건;
END LOOP;

③  FOR LOOP문

FOR 인덱스 IN 시작값, …, 종료값
LOOP
  문장;
END LOOP;

(5)   예외부(EXCEPTION)

EXCEPTION
  WHEN 조건 THEN
    SET 명령어;

2.   프로시저(Procedure)

(1)  개념 : 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합

(2)  구성 : 디비컨SET

-     DECLARE : 선언부

-     BEGIN/END : 실행 프로그램의 기본 단위

-     CONTROL : 순차적으로 처리, 조건문과 반복문 이용 처리

-     SQL : 주로 DML 사용, TRUNCATE 사용도 가능

-     EXCEPTION : BEGIN/END절 실행 중 예외처리

-     TRANSACTION : DBMS적용 또는 취소 여부 결정

(3)  문법

CREATE [OR REPLACE] PROCEDURE 프로시저_명 (파라미터_명 [IN | OUT | INOUT] 데이터_타입, …) IS   변수선언 BEGIN   명령어; [COMMIT | ROLLBACK] END;
[OR REPLACE]	기존 프로시저 존재 시 덮어씀
모드	IN     : OS에서 프로시저로 값 전달 OUT   : 프로시저에서 OS로 값 전달 INOUT : IN, OUT 동시 수행
BEGIN	시작
COMMIT	성공적으로 끝난 프로시저 값 반영
ROLLBACK	트랜잭션 발생 이전으로 돌림
END	종료

l  호출 : EXCUTE 프로시저_명(파라미터1, 파라미터2, …) / EXEC프로시저_명(파라미터1, 파라미터2, …)

3.   사용자 정의함수(User-Defined Function)

(1)  개념 : 사용자가 생성하는 절차형 SQL

(2)  구성 : 프로시저의 구성에서 TRANSCATION이 RETURN으로 변경, 나머지는 동일

-     RETURN : 함수값 반환

(3)  문법

CREATE [OR REPLACE] FUNCTION 함수명 (파라미터_명 [IN | OUT | INOUT] 데이터_타입, …) RETURN 데이터_타입 IS   변수선언 BEGIN   명령어;   RETURN 변수; END;
[OR REPLACE]	기존 함수 존재 시 덮어씀
모드	IN : OS에서 프로시저로 값 전달
RETURN 데이터_타입	반환되는 데이터 타입 정의
BEGIN	시작
RETURN 데이터값	반환되는 단일 값
END	종료

 

4.   트리거(Trigger)

(1)  개념 : DB에서 삽입, 삭제, 갱신 등의 이벤트가 발생시 작업을 자동 수행

(2)  종류 : 행 트리거 / 문장 트리거

(3)  구성 : 프로시저에서 선언 후 EVENT부 추가, TRANSACTION부 제외

-     EVENT : 트리거가 실행되는 타이밍, 이벤트를 명시

(4)  문법

CREATE [OR REPLACE] TRIGGER 트리거명 [BEFORE | AFTER] 유형 ON 테이블명 [FOR EACH ROW] BEGIN END;
[OR REPLACE]	기존 트리거 존재 시 덮어씀
순서	BEFROE : 이벤트 발생 전 AFTER  : 이벤트 발생 후
유형	INSERT, UPDATE, DELETE의 조합 (INSERT or DELETE 같이 사용 가능)
FOR EACH ROW	매번 변경되는 행 수만큼 실행
BEGIN	시작
END	종료

 

(5)  주의사항 : TCL(COMMIT, ROLLBACK)사용 불가 / 연쇄오류 발생 가능성 주의

IV.  데이터 조작 프로시저 최적화

1.   데이터 조작 프로시저 성능개선

(1)  쿼리 성능 개선(튜닝)의 개념 : 프로시저에 존재하는 SQL실행 계획을 분석, 수정하여 시간 단축

(2)  쿼리 성능 개선 절차 : 문제 있는 SQL 식별 / 옵티마이저 통계 확인 / SQL 문 재구성 / 인덱스 재구성 / 실행계획 유지관리

(3)  옵티마이저(Optimizer) 통계 확인

-     개념 : 최적의 처리경로를 설정해주는 DBMS 내부의 핵심 엔진

-     유형

	RBO(Rule Based Optimizer)	CBO(Cost Based Optimizer)
개념	통계 정보가 없는 상태에서 사전 등록된 규칙에 따라 경로 고려	통계 정보로부터 모든 접근 경로 고려
핵심	규칙(우선 순위) 기반	비용(수행 시간) 기반
평가 기준	인덱스 구조, 연산자, 조건절 형태 등	레코드 개수, 블록 개수, 평균 행 길이, 컬럼 값의 수, 컬럼 값 분포 등
장점	사용자가 원하는 처리경로로 유도가 용이	옵티마이저의 이해도가 낮아도 성능보장 가능

 

-     역할

①  쿼리 변환(Query Transformer) : 좀 더 일반적이고 표준화된 형태로 변환

②  비용 산정(Estimator) : 각 단계의 선택도, 카디널리티, 비용을 계산 / 궁극적 : 총비용 계산

③  계획 생성(Plan Generation) : 하나의 쿼리를 수행 시 후보군이 될 만한 실행계획들을 생성

-     힌트 사용 : 옵티마이저에 명시적인 힌트 제공

①  /*+RULE*/ : 규칙 기반 접근 방식 사용

②  /*+CHOOSE*/ : 오라클 옵티마이저 디폴트 값에 따름

③  /*INDEX(테이블명 인덱스명)*/ : 지정된 인덱스를 강제적으로 사용

④  /*+USE_HASH(테이블명)*/ : 지정된 테이블들의 조인이 Hash Join으로 일어나게 유도

⑤  /*+USE_MERGE(테이블명)*/ : 지정된 테이블들의 조인이 Sort Merge로 일어나게 유도

⑥  /*+USE_NL(테이블명)*/ : 지정된 테이블들의 조인이 Nested Loop 형식으로 일어나게 유도

(4)  SQL 문 재구성

-     특정 값 지정 : 조건 절에 범위가 아닌 특정 값 지정

-     별도의 SQL 사용 : 하나의 SQL문 사용시 UNION ALL 연산자 사용

-     힌트 사용

-     HAVING 미사용 : 인덱스가 걸린 컬럼은 HAVING 사용시 인덱스 해제

-     인덱스만 진의 사용 : 가능한 인덱스만을 이용해 질의

(5)  인덱스 재구성

-     자주 쓰는 컬럼 선정

-     SORT 명령어 생략

-     분포도를 고려

-     변경이 적은 컬럼 선정

-     결합 인덱스 사용