정보처리기사 : SQL 응용 - 응용 SQL / SQL 활용 및 최적화 (1)

<02. 응용 SQL>

1. 집계성 SQL

1) 다중 행 연산자: 다중 행 연산자는 서브 쿼리의 결과가 여러 개의 튜플을 반화낳는 다중 행 서브쿼리에서 사용되는 연산자

다중 행 연산자 종류

• IN: 리턴되는 값 중에서 조건에 해당하는 값이 있으면 참
• ANY: 하나 이상 만족하면 참
• ALL: 모든 값과 조건 값 만족하면 참
• EXISTS: 메인 쿼리의 비교 조건이 서브쿼리의 결과 중에서 만족하는 값이 하나라도 존재하면 참

IN 연산자:

SELECT EMP_ID, EMP NAME, DEPT ID
FROM EMP
WHERE EMP_ID IN (SELECT MGR_ID
				FROM DEPT);

ANY 연산자:

SELECT *
FROM EMP A
WHERE SALARY > ANY (SELECT SALARY
					FROM EMP
                    WHERE JOB_TITLE='과장');

ALL 연산자

SELECT * FROM EMP A
WHERE SALARY > ALL (SELECT SALARY
					FROM EMP
                    WHERE JOB_TITLE='과장');

EXIST 연산자

SELECT A.EMP_ID,
	A.EMP_NAME,
    A.SALARY
FROM EMP A
WHERE EXISTS (SELECT 1 FROM EMP B
				WHERE A.SALARY=B.SALARY
                AND B.EMP_NAME="홍길동")
//홍길동의 SALARY와 같은 SALARY를 받는 사람들을 PRINT

---

2) 집계함수: 여러 행 또는 테이블 전체 행으로부터 하나의 결괏값을 반환하는 함수

• WHERE 조건으로 지정된 데이터 집합으로부터 그룹화된 집합에 대한 조건 선택 시 HAVING을 사용
• GROUP BY 구문 뒤에는 테이블을 구분하는 컬럼을 기재하여 그룹화한다.
• HAVING 구문은 그룹화된 집합에 대한 조건 지정 시 사용하고, 상수나 집약 함수, 집약 키를 사용할 수 있다.

1] GROUP BY 구문

• WHERE 구문을 활용하여 조건별 대상 ROW를 선택한다.
• 복수 ROW 대상의 데이터 분석 시 그룹핑 대상이 되는 부분을 선별할 필요가 있다.
• NULL값을 가지는 ROW는 제외한 후 산출한다.
• SELECT에서 사용되는 ALIAS는 사용 불가.
• WHERE 구문은 GROUP BY보다 먼저 실행되고, 대상이 되는 단일행을 사전에 선별한다.

2] HAVING 구문

• WHERE 구문 내에 사용할 수 없는 집계 함수의 구문을 적용하여 복수 행의 계산 결과를 조건별로 적용하는 데 사용
• 일반적으로 GROUP BY 뒤에 기재. GROUP BY 구문 기준의 항목이나 소그룹 집계 함수를 할용하여 조건 적용

집계함수의 종류

• COUNT
• SUM
• AVG
• MAX
• MIN
• STDEV: 표준편차
• VARIANCE: 분산

---

<03.SQL 활용 및 최적화>

1. 절차형 SQL

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

2) 종류:

• Procedure: 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합
• User-Defined Function (사용자 정의 함수): 일련의 SQL 처리를 수행하고, 수행 결과를 단일값으로 반환할 수 있는 절차형 SQL
• Trigger: DB 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

2.SQL 최적화

1) 튜닝(쿼리 성능 개선):

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

2) 옵티마이저:

• SQL을 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS 속 핵심엔진
• 옵티마이저가 생성한 SQL 처리경로 = 실행계획(Execution Plan)

옵티마이저 유형

• RBO:
  - Rule Based Optimizer
  • 규칙(우선순위) 기반
  • 인덱스 구조, 연산자, 조건절 형태로 평가
  • 사용자가 원하는 처리경로로 유도하기 쉬움
• CBO:
  - Cost Based Optimizer
  • 비용(수행시간) 기반
  • 레코드, 블록 개수, 평균 행 길이, 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터 등
  • 옵티마이저의 이해도가 낮아도 성능 보장 가능