SQL 최적화 윈도우 함수, 테이블 파티션, 옵티마이저

안녕하세요! 데이터베이스를 다룰 때 성능은 중요한 요소입니다. 잘 작성된 SQL 쿼리는 빠르고 효율적인 데이터 조회를 가능하게 하고, 데이터베이스 서버의 부담을 줄일 수 있습니다.

이번 글에서는 SQL 최적화의 기본 원리와 함께 윈도우 함수, 테이블 파티션, 그리고 옵티마이저의 역할에 대해 다뤄보겠습니다.
이를 통해 SQL 쿼리를 작성할 때 성능을 고려하는 방법을 이해하고, 데이터베이스의 효율성을 극대화하는 방법을 조금이나마 아셨으면 좋겠습니다.

---

1. 윈도우 함수

윈도우 함수는 레코드(행) 간의 관계를 쉽게 정의하기 위한 강력한 도구이다. 주로 집계, 순위, 비율 등의 연산을 수행할 때 사용된다.

• 사용 예시:

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

• WINDOW_FUNCTION: 윈도우 함수 (뒤에 OVER와 함께 나옴).
• ARGUMENTS: 함수의 작업 대상이 되는 컬럼명 등.
• PARTITION BY: 데이터를 그룹화하는 기준. 각 파티션별로 윈도우 함수가 적용됨.
• ORDER BY: 각 파티션 내에서 혹은 전체 테이블에서 데이터를 정렬하는 기준.
• WINDOWING: 윈도우 함수의 연산 대상이 되는 행의 범위를 지정한다.
  • RANGE: 특정 범위 지정. 예시: RANGE BETWEEN 100 AND 200.
  • BETWEEN a AND b: a부터 b까지 윈도우가 적용됨.
  • UNBOUNDED PRECEDING: 첫 번째 행을 윈도우의 시작으로 지정.
  • UNBOUNDED FOLLOWING: 마지막 행을 윈도우의 마지막으로 지정.
  • CURRENT ROW: 현재 행을 윈도우의 시작으로 지정.

• 그룹 내 집계 함수: COUNT, SUM, MIN, MAX, AVG 등.
• 그룹 내 순위(RANK) 함수:
  • RANK: 동일한 순위에 대해 동일한 순위를 부여하나, 동일한 순위는 하나의 건수로 계산되지 않기 때문에 등수가 생략될 수 있다.
  • DENSE_RANK: 동일한 순위에 대해 동일한 순위를 부여하며, 하나의 건수로 계산된다. 등수가 생략되지 않는다.
  • ROW_NUMBER: 동일한 순위에 대해 고유한 순위를 부여한다.
• 그룹 내 비율 관련 함수:
  • PERCENT_RANK: 파티션 내에서 각 행의 순서별 백분율을 조회한다.
  • NTILE(n): 파티션별로 전체 건수를 n등분한 값을 반환한다. 예: n=4이면 4등분 내에서 몇 번째인지 조회.
  • CUME_DIST: 파티션 내 전체에서 현재 행의 값 이하인 레코드 건수에 대한 누적 백분율을 조회한다. (0~1 값을 가짐).
• 그룹 내 행 순서 함수:
  • FIRST_VALUE: 파티션 내에서 가장 처음 나오는 값을 반환 (결과적으로 MIN과 동일).
  • LAST_VALUE: 파티션 내에서 가장 마지막에 나오는 값을 반환 (결과적으로 MAX와 동일).
  • LAG(컬럼명, 레코드위치차이값): 이전 행의 값을 가져온다 (앞의 값을 가져옴).
  • LEAD(컬럼명, 레코드위치차이값, NULL 대체값): 다음 행의 값을 가져온다. 기본값은 1이다 (뒤의 값을 가져옴).

---

2. 테이블 파티션

파티션은 대용량의 테이블을 여러 개의 데이터 파일에 분리하여 저장하는 방법이다. 물리적으로 분리된 데이터 파일에 저장되어 입력/수정/삭제/조회 성능이 향상되며, 독립적으로 관리 가능하다.

• 종류:
  • RANGE PARTITION: 값의 범위를 기준으로 파티션을 나눠 저장하는 방식.
  • LIST PARTITION: 특정 값을 기준으로 파티션을 나눠 저장.
  • HASH PARTITION: 데이터베이스 관리 시스템이 자체적으로 해시함수를 사용해 데이터를 분할하고 관리하는 방식.

---

3. 옵티마이저 (Optimizer)

옵티마이저는 SQL 실행 계획을 수립하고 SQL을 실행하는 데이터베이스 관리 시스템의 소프트웨어이다. 동일한 SQL문이라도 어떻게 실행되는지에 따라 성능이 크게 달라질 수 있다.

• SQL 실행 프로세스:
  1. SQL문 작성
  2. Parsing: 문법 검사, 구문 분석 수행.
  3. 옵티마이저: 비용 기반 및 규칙 기반으로 실행 계획 수립.
  4. 실행 계획 수립 및 실행: PLAN_TABLE에 저장된 계획에 따라 SQL 실행.
• 비용기반 옵티마이저 (Cost-Based Optimizer):
  • 최신 오라클은 기본적으로 비용기반 옵티마이저를 사용한다.
  • 시스템 통계와 오브젝트 통계를 통해 SQL문 실행에 대한 총 비용을 계산하고, 총 비용이 가장 적은 쪽으로 실행 계획을 수립한다.
  • 비용기반 옵티마이저는 항상 인덱스 스캔이 유리하다고 판단하지 않으며, 때로는 전체 테이블 스캔이 더 유리할 수 있다.
• 규칙기반 옵티마이저 (Rule-Based Optimizer):
  • 15가지의 우선순위를 기준으로 실행 계획을 수립한다.
  • ROWID를 기반으로 스캔하는 것이 가장 높은 우선순위를 가진다.

---

혹시 이해가 어려우시다면, 이전 글을 먼저 참고해보시면 좋을 것 같습니다.

SQL 활용 GROUP 함수와 테이블 연결 기법

SQL 활용 JOIN과 다양한 결합 방식

SQL 활용 서브쿼리(SubQuery), 계층형 조회