SQLD 자격증 문제 풀이 정리 7-8

1. 윈도우 함수: 레코드(행) 사이의 관계를 쉽게 정의하기 위한 함수

 

1) 구조

SELECT WINDOW_FUNCTION(ARGUMENTS) OVER ([PARTITION BY 컬럼명][ORDER BY 컬럼명][WINDOWING])
FROM 테이블명;

a. WINDOW_FUNCTION: 윈도우 함수

b. ARGUMENTS: 인수(컬럼명 등 함수의 작업이 이뤄지는 대상)

c. PARTITON BY: 테이블의 레코드를 쪼개는 기준

d. ORDER BY: 쪼개진 레코드 내에서 혹은 전체 테이블에서 레코드 정렬 기준

e. WINDOWING: 함수의 연산 대상이 되는 레코드 범위를 정함

 

2) WINDOW_FUCTION 종류

a. 그룹 내 집계함수: COUNT, SUM, MIN, MAX, AVG 등

b. 그룹 내 순위(RANK)함수

함수명	설명
RANK	동순위에 대해 동일한 순위 부여 동순위를 하나의 건수로 계산하지 않음
DENSE_RANK	동순위에 대해 동일한 순위 부여 동순위를 하나의 건수로 계산
ROW_NUMBER	동순위에 대한 고유한 순위 부여

c. 그룹 내 비율 관련 함수

함수명	설명
PERCONET_RANK	값이 아닌 순서를 대상으로 파티션 내에서의 순서별 백분율을 조회함
NTILE(n)	파티션별로 전체 건수를 n등분 한 값을 반환(ex. n=4, 4등분한 것 내에서 몇인지 조회)
CUME_DIST	파티션 내 전체에서 현재 행의 값 이하인 레코드 건수에 대한 누적 백분율 조회 누적 분포 상에 0~ 1값을 가짐

d. 그룹 내 행 순서 함수

함수명	설명
FIRST_VALUE	파티션 내에서 가장 처음 나오는 값 반환 MIN과 동일한 결과
LAST_VALUE	파티션 내에서 가장 마지막에 나오는 값 반환 MAX와 동일한 결과
LAG(컬럼명, 레코드 위치 차이값)	이전 행을 가져온다
LEAD(컬럼명, 레코드 위치 차이값, null일 시 대체 값)	다음(특정 위치의)행을 가져온다 default는 1

 

3) WINDOWING함수: 함수의 연산 대상이 되는 레코드의 범위를 정함

함수명	설명
RANGE	범위를 지정할 때 사용(ex. RANGE BETWEEN 100 AND 200)
BETWEEN a AND b	a부터 b까지 윈도우가 적용
UNBOUNDED PRECEDING	윈도우의 시작 위치 = 첫 번째 행
UNBOUNDED FOLLOWING	윈도우의 마자믹 위치 = 마지막 행
CURRENT ROW	윈도우 시작 위치가 현재 행

 

2. 테이블 파티션: 대용량의 테이블을 여러개의 데이터 파일에 분리하여 저장하는 것

물리적으로 분리된 데이터 파일에 저장되어 입력, 수정, 삭제, 조회 성능이 향상되고 독립적 관리가 가능하며 조회의 범위를 줄이는 효과가 있어 성능이 향상 됨

함수명	설명
RANGE PARTITON	값의 범위를 기준으로 파티션을 나눠 저장하는 방법 ex. 매출액이 50,000이상인 레코드와 미만인 레코드 별도 저장
LIST PARTITON	특정 값을 기준으로 분할 ex. 특정 값이 100일 때와 250일 때 각각 다른 데이터 파일에 저장
HASH PARTITON	데이터베이스 관리 시스템이 자체적으로 해시함수를 사용해 분할하고 관리하는 방식

 

3. 옵티마이저(Optimizer): SQL 실행계획을 수립하고 SQL을 실행하는 데이터베이스 관리 시스템의 소프트웨어

 

1) SQL순서

a. SQL문 작성

b. Parsing: 문법 검사, 구문 분석

c. 옵티마이저: 비용기반, 규칙기반

d. 실행계획: PLAN_TABLE 저장

e. SQL 실행

 

2) 옵티마이저 구분

a. 비용기반 옵티마이저: 시스템 통계와 오브젝트 통계를 통해 해당 SQL문 실행에 대한 총 비용을 계산하고 총 비용이 가장 적은 쪽으로 실행 계획을 수립함

cf) 최신 Oracle은 비용기반을 default로 사용

 

b. 규칙기반 옵티마이저: 15가지 우선순위를 기준으로 실행 계획 수립

 

3) 옵티마이저 조인

SELECT/*ordered use _nl(B)*/ A.*
FROM table1 A JOIN table2 B ON A.id = B.id;

*ordered use_hash, ordered use_merge

JOIN을 수행하는 과정에서 성능을 최적화하기 위해 옵티마이저 조인 방식을 선택하여 hint로 기입해줌

 

종류

Nested Loop JOIN

중첩된 반복문과 동일한 형식 선행 테이블의 조건을 만족하는 경우의 수만큼 반복적으로 수행

- 선행 테이블(=외부 테이블, Driving Table)을 먼저 조회하여 연결 대상 데이터를 찾고, 그 다음 테이블(내부 테이블)을 연결 함

->  먼저 처리되는 데이터의 양 - 선행 테이블(외부 테이블, Driving Table)의 처리 범위에 따라 처리량이 결정되 때문에 선행 테이블의 크기가 작은 것을 찾아야함

- 로우들 간의 처리, 테이블 간의 처리 모두 순차적으로 발생

-> 최적의 순서를 찾아주는 것이 중요

- RANDOM ACCESS 발생(선행 테이블에서 두번째 테이블을 참조할 때 발생함)

- > 성능 지연을 줄이기 위해 RANDOM ACCESS가 적은 양이 발생하도록 해야함

- INDEX 필수, Unique Index시 유리함 

- 온라인 트랜잭션 처리(OLTP)에 유용함

- 선행테이블 처리 범위가 많거나 연결 테이블에서 RANDOM ACCESS 범위가 많다면 SORT MERGE JOIN보다 불리해지는 경우 발생

 

SORT MERGE JOIN

- 두 테이블을 각각 정렬하고, 완료되면 병합함

-> 정렬이 발생하기 때문에 데이터 양이 많을 경우 느려짐

- 정렬 대상 데이터 양이 많을 경우 임시 디스크를 사용하기 때문에 성능 저하됨

- EQIU JOIN, non-EQUL JOIN 모두 사용 가능함

 

HASH JOIN

순서 1. 선행 테이블 결정 2. 선행 테이블에서 주어진 조건(where)에 해당하는 레코드 선택 3. 해당 행이 선택되면 JOIN key 를 기준으로 해시 함수를 하용해서 해시테이블을 메인 메모리에 생성 4. 후행 테이블에서 주어진 조건에 만족하는 행을 찾음 5. 후행 테이블의 JOIN key를 사용해서 해시 함수를 적용하여 해당 버킷 검색

- 두 테이블 중 작은 테이블을 HASH 메모리에 로딩하고, 두 테이블의 조인 키를 사용하여 해시 테이블을 생성함

-> 선행 테이블에는 작은 데이터가 먼저 와야함

- 두 테이블을 동시에 스캔함

- 시스템 자원을 최대한 활용 가능하며, 너무 많이 사용될 우려도 있음

- 대용량 처리에 빠른 처리 속도를 보임

- EQIU JOIN에서만 가능

- INDEX를 사용하지 않음

 

 

4. INDEX: 데이터의 색인(목차) 와 동일, 원하는 데이터를 빠르게 조회할 수 있음

1) 인덱스의 특징 

a. 인덱스 키를 기준으로 정렬되어 있음 -> 탐색이 빨라짐

b. Primary Key(기본키)가 자동으로 INDEX가 됨

c. 하나의 테이블의 여러개의 인덱스 생성 가능, 하나의 인덱스는 여러 컬럼으로 구성 가능

 

2) 인덱스의 종류

a. Index Unique SCAN:  인데스 키값이 중복되지 않을 때 해당 키를 통해 탐색

b. Index Range SCAN: 특정 범위를 조회하는 where문을 사용하여 해당 영역 스캔

c. Index Full SCAN: 인덱스의 처음부터 끝까지 모두 스캔

 

 

5. PL/SQL: SQL을 확장시켜 다양한 절차적 프로그래밍을 가능하게 한 언어

1) 특징

a. Block 구조로 되어 있어 기능 별 모듈화 가능

b. Declare문으로 시작하며, 변수 및 상수를 선언하여 사용 가능

-> DECLARE, BEGIN~END는 필수, EXCEPTION은 선택

c. DML, IF, LOOP문 등 다양한 절차적 언어 사용

d. Oracle에 내장되어 있음, 동일한 언어를 사용하는 프로그램과 호환 가능

e. 응용 프로그램의 성능을 향상 시킴

f. Procedure, User Defined Function, Trigger 객체를 작성할 수 있음

-> Procedure 내부에 작성된 절차적 코드는 PL/SQL엔진이 처리하고, 일반적 SQL문장은 SQL실행기가 처리함

 

6. 분산 데이터베이스: 하나의 데이터베이스 시스템이 네트워크를 통해 물리적으로 분리된 데이터베이스들을 제어하는 형태의 DB

성능 향상: 분산 데이터베이스가 병렬 작업을 하기 때문에 속도가 빠름

모듈화가 되어 있어 다른 모듈에 영향을 주지않고 시스템 갱신 가능

분산 데이터베이스 추가를 통한 용량 확장 용이

중요 데이터 보호 용이

신뢰성이 높음

관리와 통제가 어려움

보안관리, 무결성 통제가 어려움

복잡한 구조를 가지고 있음