SQLD 요약 4 SQL 최적화 기본 원리

ROLE 

= 많은 DBMS 사용자에게 개별적으로 많은 권한을 부여하는 번거로움과 어려움을 해소하기 위해 다양한 권한을 하나의 그룹으로 묶어놓은 논리적인 권한의 그룹이다.

 

절차형 SQL

- 분기, 반복이 가능한 모듈화 된 프로그램, DBMS에서 직접 실행됨

- 프로시저 (Procedure), 사용자 정의 함수 (User Defined Function), 트리거(Trigger)

- PL/SQL, [SQL Server] T-SQL

 

PL/SQL의 특징

- BLOCK 구조로 되어있어 각 기능별로 모듈화가 가능하다

- 변수/상수등을 선언해 SQL문장간 값을 교환한다.

- IF/LOOP 등의 절차형 언어 등을 사용해 절차적 프로그램이 가능하도록 한다.

- DBMS 정의 에러나 사용자 정의 에러를 정의해 사용할 수 있다.

+ PL/SQL 은 응용 프로그램의 성능을 향상시킨다.

+ PL/SQL은 여러 SQL 문장을 BLOCK으로 묶고 한 번에  BLCOK전체를 서버로 보내기에 통신량을 줄일 수 있다

+ PL/SQL은 동적 SQL 또는 DDL문장을 실행할 때 Execute Immediate 를 사용해야 한다.

+ Trigger는 Procedure과 다르게 Commit과  Rollback을 사용할 수 없다.

 

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

- 규칙(우선순위)을 기반으로 실행계획을 생성

- 이용가능한 인덱스가 있다면 항상 인덱스를 사용하는 규칙 계획 생성

* 규칙 기반 우선순위

1. 행에 대한 고유 주소(ROWID)를 사용하는 방법 (=Single row by rowid)

2. 인덱스를 이용한 방법

3. 전체 테이블 액세스 방법

 

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

- 비용 (예상되는 소요시간, 자원 사용량)이 가장 적은 실행계획을 선택

- 비용에 따라 Full Scan이 유리하다고 판단할 수 있음.

- CBO의 실행계획에는 단계별 예상 비용 및 건수가 표현된다.

 

실행계획

- SQL 처리를 위한 실행 절차와 방법을 표현한 것

- 조인방법, 조인순서, 액세스 기법등이 표현된다.

- 동일 SQL문에 대해 실행계획이 달라진다 해서 , 실행결과가 달라지지 않는다.

 

SQL 처리 흐름도 (Access Flow Diagram)

- SQL문의 처리 절차를 시각적으로 표현한 것으로 인덱스 스캔 및 전체 테이블 스캔등의 액세스 기법을 표현할 수 있으며 성능적인 측면도 표현할 수 있다.

+ 인덱스 범위 스캔을 결과 건수만큼 반환하지만, 결과가 없으면 한건도 반환하지 않을 수 없다.

 

 

조인종류

- NL (Nested Loop) Join, Sort Join, Merge Join, Hash join

 

Nested Loop Join (NL Join)

- 중첩 for문과 같은 원리

- 비효율적, 오리 걸린다.

- 조인 조건의 인덱스 유무에 영향을 많이 받음

- OLPT 환경 (온라인 환경)의 목록 처리 업무에 많이 사용된다.

- 조인 컬럼에 적당한 인덱스가 없어서 자연조인(Natural Join)이 효율적일 때 유용하다.

- 유니크 인덱스를 활용해 수행 시간이 적게 걸리는 소량 테이블을 온라인으로 조회하는 경우 유용

 

Hash Join

- Equi Join에서만 동작한다.

- 칼럼의 인덱스를 사용하지 않기 때문에 조인 칼럼에 인덱스가 존재하지 않을 경우에도 사용할 수 있는 조인 기법이다.

- 정렬할 작업이 필요 없어 정렬이 부담되는 대량 배치 작업에 유리

- Driving Table(outer Table)이 성능에 매우 중요한 요인.

 

Sort Merge Join

- 조인칼럼에 적당한 인덱스가 없어 NL(Nested Loop)가 비효율적일 때 사용 가능

- NL과 차이점, 두 테이블을 조인 칼럼을 기준으로 데이터를 정렬시킬 후 JOIN 하는 방식

- Driving Table 개념이 필요하지 않은 조인 방식

- 조인 조건의 인덱스 유무에 영향을 받지 않는다.

 

+ DW등의 데이터 집계 업무에서 많이 사용되는 Join은 Hash Join, Sort Merge Join이다.

 

NL Join	Sort Merge Join	Hash Join
OLPT 환경	PGA 환경	DW환경
	Non-Equi Join	Equi Join
인덱스 유무에 영향 O	인덱스 유무에 영향 X	정렬작업 필요 X 대량 데이터 배치 작업에 유리
Outer Table(Driving Table) 중요	Outer Table 중요 X	Outer Table (Driving Table) 중요

 

기출문제 

https://selgii.tistory.com/44#google_vignette