[데이터베이스개론·SQL] 250124

이슬비·2025년 1월 24일

데이터베이스개론·SQL

목록 보기

9/9

`WITH` 문

CTE(Common Table Expressions)를 정의하기 위한 구문으로 서브쿼리를 재사용 가능하고 가독성 높게 작성할 수 있도록 지원한다.
복잡한 SQL 쿼리를 읽기 쉽고 관리 가능하게 구조화하며, 동일한 서브쿼리를 반복적으로 재작정 하지 않아도 된다. 재귀 쿼리 작성 시 활용된다.

WITH DepartmentSalary AS (
  SELECT DEPARTMENT_ID, ROUND(AVG(SALARY), 2) AS AVG_SALARY
  FROM EMPLOYEES
  GROUP BY DEPARTMENT_ID
  ORDER BY DEPARTMENT_ID NULLS LAST
)
SELECT *
FROM DepartmentSalary
WHERE AVG_SALARY > 5000;

다중 CTE

WITH CTE1 AS (
  SELECT ...
),
CTE2 AS (
  SELECT ...
  FROM CTE1
)
SELECT ...
FROM CTE2;

재귀 CTE

WITH NUMBERS (NUM) AS (
  SELECT 1 AS NUM
  FROM DUAL
  UNION ALL
  SELECT NUM + 1 AS NUM
  FROM NUMBERS
  WHERE NUM < 10
)
SELECT B.NUM
FROM NUMBERS B;

`WITH` 문 사용 시 유의사항

CTE는 임시적으로 데이터를 메모리에 저장하여 처리하며 PGA를 이용.
PGA 부족 시 Temp Tablespace 공간 사용.
CTE의 결과를 도출하는 데에 복잡한 계산이 이루어져야 하고 데이터량도 많은 경우에는 초기 비용이 들더라도 Temp 영역에 저장해 두는 것이 유리할 수 있음.
데이터량이 적고 결과 도출의 비용이 크지 않을 경우 Temp 영역 저장 비용이 더 커버릴 가능성 존재(PGA로 해결이 되는지 확인 필요).

Temp Tablespace 사용 확인

데이터 정렬 작업(ORDER BY, GROUP BY, DISTINCT 등)에서 메모리(PGA)를 다 사용하면 Temp Tablespace 데이터를 기록.
실행 계획에서 SORT(GROUP BY), SORT(ORDER BY), SORT(UNIQUE)와 같은 키워드로 나타남.
HASH JOIN 또는 GROUP BY를 처리하는 과정에서 메모리가 부족하면 Temp Tablespace를 사용하며, 실행 계획에서 HASH JOIN, HASH GROUP BY 등으로 표시.

JOIN 최적화

Nested Loop Join

중첩 for 문과 유사한 형태.
driving 테이블의 처리 범위에 의해 전체 일량이 결정.
join 컬럼이 driven 테이블의 index로 설정되어 있어야 유리.
OLTP성 쿼리에 적합.

Hash Join

Join 컬럼에 인덱스가 없어 NL Join이 효과적이지 못한 상황에 대한 대안.
Join 컬럼에 인덱스가 있지만 driving table에서 driven table에서 driven table로의 액세스 량이 많아 Random Access 부하가 심한 경우.
두 테이블 중 작은 테이블을 메모리에 생성.
큰 테이블이 driving table이 되어 join 데이터의 hash 값을 메모리의 hash 값과 비교, 동일한 레코드를 결과 목록에 추가.
Table Random Access 부하가 없음.
수행빈도가 낮고 시간이 오래 걸리는 OLAP성 쿼리에 적합.
Hash Join의 빠른 속도 때문에 모든 Join을 Hash Join으로 처리하려는 유혹 존재.
Index는 한 번 생성해 놓으면 계속해서 사용할 수 있는 영구적인 오브젝트인 반면, Hash Table은 단 하나의 쿼리를 위해 생성하고 Join이 끝나면 바로 소멸하는 자료구조.
수행시간이 짧으면서 수행빈도가 매우 높은 OLTP성 쿼리를 Hash Join으로 처리한다면 CPU와 메모리 사용률 크게 증가.
수행 빈도가 낮고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 Join할 때 유용.

실행 계획

SQL로 요청한 데이터를 어떻게 꺼내 올 것인가에 대한 Plan이다. 성능 병목 현상을 파악하는 데 유용하다.

높은 확률로 Optimizer가 결정한 실행 계획이 최선인 경우가 많으며, 이따금씩 Optimizer의 실행 계획이 잘못되었다 판단될 경우 적절한 Hint를 사용하여 튜닝한다.

실행 계획 확인 XPLAN

DBMS_XPLAN 패키지를 사용하여 실행 계획을 읽기 쉽게 출력한다.

EXPLAIN PLAN FOR
SELECT * FROM employees;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

Operation : 작업 유형.
Name : 테이블 또는 인덱스 이름.
E-Rows : 옵티마이저가 예측한 row 수.
A-Rows : 실제로 처리된 row 수.
Buffers : 해당 단게에서 I/O한 Block 수.

들여쓰기가 많이 들어간 Id 순으로 읽는다.

Access 조건 vs Filter 조건

Access 조건
- 인덱스나 테이블에 접근할 때 사용하는 조건.
- 검색 제한.
- 데이터 액세스를 효율적으로 제한하여 불필요한 I/O를 줄임.
Filter 조건
- 데이터를 반환한 후 추가적으로 필터링하는 조건.
- Access 조건보다 덜 효율적.

실행 계획 분석

TABLE ACCESS FULL
- 테이블의 모든 행을 스캔하는 작업(Table Full Scan).
TABLE ACCESS BY INDEX ROWID
- 인덱스를 통해 식별된 행(Row ID)을 기반으로 테이블 데이터를 읽는 작업.
INDEX RANGE SCAN
- 범위 조건(BETWEEN, >=, <= 등)에 따라 인덱스를 검색하는 작업.
INDEX UNIQUE SCAN
- 고유 인덱스를 사용하여 단일 행을 검색하는 작업.
NESTED LOOPS
- 중첩 반복을 사용해 두 소스를 결합하는 작업(Nested Loop Join).
HASH JOIN
- Hash 알고리즘을 사용하여 데이터를 결합하는 작업.
SORT AGGREGATE
- 집계 함수를 계산하기 위한 작업.
- GROUP BY 없이 집계 함수가 실행될 때 사용.
- 데이터를 정렬하지 않고 집계 연산만 수행.
SORT GROUP BY
- GROUP BY 조건을 처리하기 위해 데이터를 정렬하는 작업.
SORT ORDER BY
- 데이터를 정렬하는 작업.
- 메모리 또는 임시 테이블 공간을 사용하여 데이터 정렬.
- 정렬 작업 비용이 많이 드는 경우 적절한 인덱스 활용.
HASH GROUP BY
- Hash 알고리즘을 사용하여 데이터를 그룹화하는 작업.
- 대량 데이터를 그룹화하는 경우 SORT GROUP BY보다 효율적.
VIEW PUSHED PREDICATE
- 쿼리에서 뷰를 사용할 때 필터 조건을 뷰 내부로 이동시켜 처리 성능을 높임.
MATERIALIZE
- 뷰나 서브쿼리 결과를 메모리에서 임시로 저장하는 작업.
WINDOW SORT
- 윈도우 함수를 처리하기 위한 정렬 작업.

사용자 정의 함수

사용자가 특정 작업을 수행하기 위해 정의한 PL/SQL 코드 블록이다. 반복적인 작업을 자동화하고 코드 재사용성을 높이기 위해 사용하며, 주로 입력 값을 받아 처리 후 결과를 반환한다.

사용자 정의 함수의 특징으로는 다음과 같다.

특정 작업을 수행한 후 값을 반환.
SQL 문에서 호출 가능.
재사용 가능.
복잡한 논리를 캡슐화하여 유지보수 용이.

사용자 정의 함수의 구성 요소로는 다음과 같다.

함수 이름 : 작업의 의미를 나타냄.
매개변수 : 함수로 전달되는 입력값.
반환값 : 함수가 실행 후 반환하는 값.
PL/SQL 코드 : 작업을 수행하는 논리.

사용자 정의 함수 작성 팁

명확하고 간결한 함수 이름 사용.

함수는 단일 작업에 집중.

매개변수와 반환값에 대한 데이터 타입 명확히 지정.

EXCEPTION 블록으로 오류 처리 강화.

CREATE OR REPLACE FUNCTION 함수이름 (매개변수 IN 데이터타입)
RETURN 반환타입 IS
BEGIN
  -- 작업 수행
  RETURN 결과값;
END;

-- 사용자 정의 함수 예시
CREATE OR REPLACE FUNCTION get_discount_price (price IN NUMBER)
RETURN NUMBER IS
BEGIN
  RETURN price * 0.9;
END;

-- SQL 문에서 호출
SELECT get_discount_price(1000) AS discounted_price
FROM dual;

-- PL/SQL 블록에서 호출
DECLARE
  discounted_price NUMBER;
BEGIN
  discounted_price := get_discount_price(1000);
  DBMS_OUTPUT.PUT_LINE('할인가:' || discounted_price);
END;

사용자 정의 함수가 너무 느릴 경우, 스칼라 서브쿼리를 통해 캐싱 기능을 이용한다.
SELECT (
  SELECT get_discount_price(1000)
  FROM dual
)
FROM DUAL;

오류 처리

사용자 정의 함수에서 오류가 발생하면 EXCEPTION 구문으로 처리가 가능하다.

CREATE OR REPLACE FUNCTION 함수이름 (매개변수 IN 데이터타입)
RETURN 반환타입 IS
BEGIN
  -- 작업 수행
  RETURN 결과값;
 EXCEPTION
   -- 오류 처리
   RETURN 결과값;
END;

사용자 정의 함수 장점

재사용성 : 반복 작업을 함수로 정의하여 재사용 가능.
유지보수 용이성 : 복잡한 로직을 캡슐화하여 코드 관리 간소화.
성능 최적화 : 서버 측에서 실행되므로 클라이언트-서버 간 데이터 이동 최소화.

사용자 정의 함수 단점

복잡성 : 지나치게 많은 함수를 사용하면 코드가 복잡할 수 있음.
디버깅 어려움 : 서버 측에서 실행되므로 디버깅이 어려울 수 있음.
의존성 문제 : 데이터베이스 스키마 변경 시 함수 수정 필요.

사용자 정의 함수와 보완

권한 제한 : 함수 생성 및 실행 권한을 관리해야 함.
SQL Injection 방지 : 매개변수 값을 안전하게 처리.
데이터 무결성 : 함수가 데이터 무결성을 해치지 않도록 설계.

사용자 정의 함수 변경 및 삭제

-- 변경
CREATE OR REPLACE FUNCTION 함수이름 ...;

-- 삭제
DROP FUNCTION 함수이름;

REDO와 UNDO

REDO : 데이터베이스의 변경 내용을 복구하기 위한 로그.
UNDO : 데이터 변경 이전 상태를 저장하여 롤백하거나 트랜잭션을 취소할 수 있게 함.
데이터 무결성과 복구 기능을 제공.

REDO의 역할

데이터베이스가 비정상적으로 종료되었을 때 복구.
커밋된 트랜잭션의 변경 내용을 재생하여 데이터 무결성 보장.
REDO 로그는 디스크에 지속적으로 기록됨.

UNDO의 역할

트랜잭션 롤백 시 변경 내용을 원래 상태로 복구.
읽기 일관성을 제공하여 다른 세션이 데이터 변경 전 상태를 조회 가능.
복구 또는 취소 작업을 위해 UNDO 세그먼트를 사용.

REDO vs UNDO

REDO : 변경 내용을 재생(재적용).
- 커밋된 트랜잭션 대상.
UNDO : 변경 내용을 취소(원래 상태 복구).
- 롤백 또는 읽기 일관성 대상.

REDO 로그 구조

REDO 로그 파일 : 데이터 변경 내용을 저장.
로그 버퍼 : 메모리 내 변경 내용을 저장.
로그 파일 스위칭 : REDO 로그가 가득 차면 다음 로그 파일로 이동.

UNDO 세그먼트

UNDO 데이터는 UNDO 테이블 스페이스에 저장.
각 트랜잭션은 UNDO 세그먼트를 사용하여 데이터 변경 이전 상태를 저장.
UNDO 데이터는 트랜잭션 완료 후 재사용 가능.

REDO와 UNDO 동작 원리

트랜잭션 수행 시
- 변경 내용은 REDO 로그로 기록.
- 변경 전 데이터는 UNDO 세그먼트에 저장.
커밋 시
- REDO 로그는 영구 저장.
- UNDO 데이터는 재사용 가능.

COMMIT 된 데이터 복구 방법

플래시백 쿼리(Flashback Query)
- 오라클 데이터베이스는 UNDO 테이블 스페이스를 사용하여 과거 데이터 조회 가능.
- 플래시백 쿼리는 특정 시점의 데이터를 복구하는 데 유용.

-- AS OF TIMESTAMP 절을 사용하여 5분 전 데이터를 조회
-- 이 데이터를 기반으로 복구 작업 수행
SELECT *
FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE);

SQL Injection

공격자가 입력 필드나 URL에 악의적인 SQL 코드를 삽입하여 데이터베이스를 조작하는 공격 기법이다. 주로 민감한 데이터 유출, 데이터 변경, 시스템 권한 탈취가 목적이다.

이슬비

이전 포스트

[데이터베이스개론·SQL] 250124