SQL 응용

1. 트랜잭션

• 트랜잭션의 개념
  • 인가받지 않은 사용자로부터 데이텁를 보장하기 위해 DBMS가 가져야 하는 특성이자, 데이터베이스 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위

• 트랜잭션의 특성
  • 원자성
    • 분해가 불가능한 작업의 최소단위
  • 일관성
    • 트랜잭션이 실행 성공 후 항상 일관된 데이터베이스 상태를 보존해야하는 특성
  • 격리성
    • 트랜잭션 실행 중 생성하는 연산의 중간 결과를 다른 트랜잭션이 접근 불가능한 특성
  • 영속성
    • 성공이 완료된 트랜잭션의 결과는 영속적으로 데이터베이스에 저장하는 특성

• 트랜잭션의 상태 변화
  • 활동 상태
  • 부분 완료 상태
  • 완료 상태
  • 실패 상태
  • 철회 상태

• 트랜잭션 제어
  • 커밋 COMMIT
  • 롤백 ROLLBACK
  • 체크포인트 CHECKPOINT

• 병행제어
  • 다수 사용자 환경에서 여러 트랜잭션을 수행할 때, 데이터베이스 일관성 유지를 위해 상호작용을 제어하는 기법

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

• 데이터베이스 고립화 수준
  • Read Uncommitted
    • 한 트랜잭션에서 연산 중인 데이터를 다른 트랜잭션이 읽는 것을 허용하는 수준
  • Read Committed
    • 하나 트랜잭션에서 연산을 수행할 때, 연산이 완료될 때까지 연산 대상 데이터에 대한 읽기를 제한하는 수준
  • Repeatable Read
    • 선행 트랜잭션이 특정 데이터를 읽을 때, 트랜잭션 종료 시까지 해당 데이터에 대한 갱신, 삭제를 제한하는 수준
  • Serializable Read
    • 선행 트랜잭션이 특정 데이터 영역을 순차적으로 릭을 때, 해당 데이터 영역 전체에 대한 접근 제한하는 수준

• 회복 기법
  • 지연 갱신 회복 기법
  • 즉각 갱신 회복 기법
  • 체크 포인트 회복 기법
  • 그림자 페이징 회복 기법

• DDL
  • 테이블과 같은 데이터 구조를 정의하는 데 사용되는 명령어들
  • DDL 대상
    • 도메인
    • 스키마
      • 외부 스키마
      • 개념 스키마
      • 내부 스키마
    • 테이블
    • 뷰
    • 인덱스

• 인덱스의 종류
  • 순서 인덱스
  • 해시 인덱스
  • 비트맵 인덱스
  • 함수기반 인덱스
  • 단일 인덱스
  • 결합 인덱스
  • 클러스터드 인덱스

• DDL 명령어
  • CREATE
  • ALTER
  • DROP
  • TRUNCATE

• DCL
  • 데이터베이스 관리자가 데이터 보안, 무결성 유지, 병행 제어, 회복을 위해 관리자가 사용하는 제어용 언어

• DCL 명령어
  • GRANT
  • REVOKE

2. 집계성 SQL 작성

• 데이터 분석 함수의 종류
  • 집계 함수
  • 그룹 함수
  • 윈도 함수

• 집계 함수 종류
  • COUNT
  • SUM
  • AVG
  • MAX
  • MIN
  • STDDEV
  • VARIAN

• 그룹 함수의 유형
  • ROLLUP
  • CUBE
  • GRUPING SETS

• 윈도 함수의 분류
  • 순위 함수
    • RANK, DENSE_RANK, ROW_NUMBER
  • 행 순서
    • FIRST_VALUE, LAST_VALUE, LAG, LEAD
  • 그룹 내 비율 함수
    • RATIO_TO_REPORT, PERCENT, RANK

3. 절차형 SQL

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

• 절차형 SQL 종류
  • 프로시저
  • 트리거
  • 사용자 정의함수

• 프로시저 구성
  • 선언부 DECLARE
  • 시작/종료부 BEGIN/END
  • 제어부 CONTROL
  • SQL
  • 예외부 EXCEPTION
  • 실행부 TRANSACTION

• 사용자 정의함수 구성
  • 선언부 DECLARE
  • 시작/종료부 BEGIN/END
  • 제어부 CONTROL
  • SQL
  • 예외부 EXCEPTION
  • 반환부 RETURN

• 트리거 구성
  • 선언부 DECLARE
  • 이벤트부 EVENT
  • 시작/종료부 BEGIN/END
  • 제어부 CONTROL
  • SQL
  • 예외부 EXCEPTION

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

• 쿼리 성능 개선의 개념
  • 데이터베이스에서 프로시저에 있는 SQL 실행 계획을 분석, 수정을 통해 최소의 시간으로 원하는 결과를 얻도록 프로시저를 수정하는 작업

• 옵티마이저의 개념
  • SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS 내부의 핵심엔진

• 옵티마이저의 유형
  • RBO 규칙기반 옵티마이저
  • CBO 비용기반 옵티마이저

• 힌트 사용
  • 힌트
    • 실행하려 하는 SQL 문에 사전에 정보를 주어서 SQL 문 실행에 빠른 결과를 가져오는 효과를 만드는 문법