[바미] 새로 쓴, 대용량 데이터 베이스 솔루션 Vol.1 - 2부 - 1장 부분범위처리

새롭게 안 사실들 

부분 범위 처리

처음 이 개념을 접했을 때, 오라클의 부분범위 처리가 MariaDB에서 사용하는 페이징 처리와 같은 역할을 하는 것으로 이해했다. 실제로 두 방식 모두 전체 데이터 중 일부만 가져오거나, 사용자에게 빠른 응답을 제공하며, 처리량을 줄여 성능을 개선하는 목적은 공통적이였기 때문이다. 이런 측면에서 보면 MariaDB의 페이징 처리도 일종의 부분 범위 추출로 볼 수 있다.

 

그러나 오라클에서 말하는 부분범위 처리는 SQL 실행 계획 차원에서의 근본적인 DB 엔진 레벨 최적화 방식이라는 점에서 차이가 있었다. 오라클은 WHERE 절 조건을 만족하는 전체 데이터를 한 번에 읽지 않고, 설정된 Array Size만큼만 먼저 처리한 뒤, 사용자로부터 Fetch 요청이 들어올 때까지 다음 처리를 미루는 구조를 가진다. 이는 처리 자체를 지연시켜 빠른 응답을 가능하게 하는 방식이다.

 

반면 MariaDB의 페이징은 애플리케이션 또는 SQL 쿼리에서 LIMIT과 OFFSET을 사용해 명시적으로 필요한 범위를 지정하는 방식이다. 이 방식은 전체 실행 계획과는 별개로 사용자가 직접 필요한 범위를 제한하는 것으로, 성능 확보를 위해서는 WHERE, ORDER BY, INDEX 등을 적절히 활용해야 한다는 점이 오라클의 방식과 달랐다.

공부한 내용들

제 1장 부분범위 처리

부분범위 처리란, WHERE절에 주어진 조건을 만족하는 전체 범위를 모두 처리하는 것이 아니라, 일반 운반단위(Array size)까지만 먼저 처리하여 결과를 추출하고, 이후 사용자의 요청이 있을 때까지 잠정적으로 수행을 멈추는 방식이다. 이는 원래 SQL 처리방식을 넘어, 필요한 집합만 우선 처리하여 효율을 높이기 위한 처리 방법이다.

 

DBMS는 이런 실행 방식을 지원하는 기능을 가지고 있으며, 일부 고급 DBMS는 실행속도 향상을 위해 이를 특별히 지원하고 있다. 그러나 개발 툴에서는 이 기능을 제공하지 못하는 경우도 있다.

 

실제 시스템에서는 전체 범위를 처리하지 않고 원하는 데이터 일부만 먼저 액세스해서 결과를 제공하고자 하는 요구가 많다. 예를 들어 사용자가 특정 조건에 대한 일부 결과만 먼저 확인하고자 할 때 전체 데이터를 액세스할 필요는 없다. 이처럼 전체가 아닌 일부 결과만 제공하고 다음 처리를 유보하는 방식은 실제로 처리량을 줄이는 데 큰 효과를 가져올 수 있다.

 

이러한 처리방식은 실행 속도에 상당한 영향을 미친다. 만약 부분범위 처리를 할 수 없다면, 처리범위가 넓어져 수많은 데이터를 스캔하게 되므로, 반드시 사전에 적절한 조치가 필요하다. 설계단계부터 이를 고려하지 않으면, 이후 애플리케이션 전체에 영향을 줄 수 있다.

 

부분범위 처리의 핵심은 결과의 정확성과 처리의 효율성이다. 전체를 처리하지 않고 일부만 처리해도 원하는 결과를 추출할 수 있어야 하며, 이를 위해서는 옵티마이저가 해당 실행계획을 제대로 수립해야 한다. 또한 어떤 경우에 부분범위 처리가 가능한지를 이해하고, 어떻게 하면 이를 실현할 수 있는지를 아는 것이 중요하다.

 

이 장에서는 부분범위 처리의 개념과 원리, 적용 원칙, 유도하는 기술적 방법 등을 구체적으로 설명할 예정이다. 이 개념은 모든 액세스 형태에 영향을 미치므로, 이후의 내용에도 계속해서 연계될 것이다.

 

현재 사용하는 DBMS가 부분범위 처리를 지원하고 있는지 확인하고, 이를 위해 물리적 설계단계에서부터 필요한 준비가 되어 있어야 한다. 그래야만 온라인 애플리케이션의 성능을 극대화할 수 있다.

1.1 부분범위 처리의 개념

데이터 처리에서 가장 고민스러운 요소는 처리 범위가 넓을 때 이를 어떻게 해결할지에 대한 것이다. 소량의 데이터는 큰 문제가 되지 않지만, 넓은 범위를 처리하려는 순간부터 성능과 효율에 대한 고민이 생긴다. 그러나 만약 전체 범위에 영향을 주지 않고도 원하는 결과를 얻을 수 있다면, 매우 효율적인 처리가 가능하다.

 

책에서는 이를 설명하기 위해 ‘총알택시’ 비유를 사용한다. 한 택시가 여러 손님이 모여야 출발하는 구조에서, 각 손님의 입장이 아닌 택시 입장에서 보면 손님이 많을수록 빨리 출발할 수 있다. 즉, 전체 데이터를 처리하지 않고, 필요한 일부만 빠르게 추출할 수 있다면 실행 성능은 매우 좋아질 수 있다.

 

부분범위 처리는 SQL의 WHERE 절 조건을 만족하는 데이터를 모두 처리하지 않고, 일단 운반단위(Array size)만큼만 먼저 처리해 그 결과를 추출하고, 사용자의 요청이 있는 경우에만 나머지 처리를 계속 수행하는 방식이다. 이 방식은 많은 데이터를 처리하면서도 빠른 응답을 보장할 수 있는 큰 장점을 가진다.

 

택시와 버스의 차이로 비유하자면, 버스는 많은 사람이 탈 수 있지만 출발까지 시간이 오래 걸리고, 택시는 소수만 태우고 빨리 출발할 수 있다. 이때 버스의 좌석 수가 곧 운반단위 크기를 의미한다. 택시는 부분만 처리해도 바로 결과를 내보낼 수 있지만, 버스는 일정 조건이 충족되어야 출발한다.

 

이러한 개념은 데이터의 처리 효율성에 큰 영향을 주며, 드라이빙 조건(Driving Condition)과 체크 조건(Check Condition)의 구분으로 이어진다. 체크 조건은 단순히 조건을 만족하는지를 확인하는 것이고, 드라이빙 조건은 실질적으로 운반단위의 범위를 결정하는 조건이다.

 

예를 들어 특정 조건을 만족하는 사람만 태우는 택시는 일일이 묻지 않아도 승객들이 표지판을 통해 목적지를 알고 줄을 서 있는 구조와 같다. 이는 마치 드라이빙 조건이 명확히 설정되어 있는 상황이며, 효율적인 부분범위 처리가 가능한 상태이다.

 

또한 부분범위 처리가 효율적인 이유 중 하나는 SQL 실행계획의 구조 때문이다. 실행 계획에서 SORT, GROUP BY, AGGREGATE, MERGE JOIN 등이 나타나는 경우는 대부분 전체 범위 처리를 의미하며, INDEX RANGE SCAN 등은 부분범위 처리를 시사한다. 따라서 실행계획을 통해 전체범위 또는 부분범위 처리를 판별할 수 있다.

 

SQL 실행툴의 기본 설정이 OFF 상태인 경우도 많으므로, 실제로는 부분범위 처리가 가능한 환경이지만 설정이 잘못되어 전체범위로 처리되는 경우도 있으니 확인이 필요하다.

 

끝으로, 테이블이 클수록 명확한 테스트와 SQL_TRACE 기능을 활용한 분석이 필요하며, WHERE 절이 없는 SQL을 실행한 후 FETCH 통계 등으로 전체 범위 처리 여부를 판단할 수 있다.

1.2. 부분범위처리의 적용원칙

전체범위로 처리되던 SQL을 부분범위 처리로 바꾸면, 경우에 따라 수십에서 수백 배 이상의 성능 향상을 기대할 수 있다. 하지만 모든 SQL이 부분범위 처리로 전환될 수 있는 것은 아니므로, 이를 위해 필요한 필수 조건과 유도 방법을 이해하는 것이 중요하다.

1.2.1. 부분범위의 처리의 자격

부분범위 처리를 할 수 있는 자격은 간단하게 논리적으로 반드시 전체범위를 읽고 가공해야 하는 경우를 제외하면 모두 가능하다는 것이다. 즉, 논리적으로 전체 데이터를 봐야만 의미 있는 경우가 아니라면 대부분 가능하다는 것이다.

 

아래의 예제를 보자.

-- 예제 1: 부분범위 처리 불가능한 경우
SELECT SUM(ordqty)
  FROM order
 WHERE ord_date LIKE '201512%'
 GROUP BY ord_dept;

-- 예제 2: 부분범위 처리 불가능한 경우
SELECT ord_dept, COUNT(*)
  FROM order
 WHERE ord_date LIKE '201512%';

위의 SQL문은 SELELCT 절에 SUM, COUNT 같은 집계 함수가 포함되어 있어 주어진 조건을 일부만 엑세스해서 결과를 얻는 것은 논리적으로 불가능하다.

 

하지만, 집계함수가 사용되지 않은 경우 다음과 같이 부분범위 처리가 가능하다.

-- 예제 3: 부분범위 처리 가능
SELECT ord_date, ordqty * 1000
  FROM order
 WHERE ord_date LIKE '201512%'
 ORDER BY ord_date;

여기서 ORDER BY는 전체범위 처리를 유도할 수 있는 요소이지만, 옵티마이저가 선택한 인덱스의 순서와 ORDER BY가 일치한다면 부분범위 처리가 가능하다.

ORDER BY의 처리 원칙

ORDER BY로 인해 전체범위 처리가 유도되기도 하지만, 인덱스 순서와 일치할 경우 바로 리턴 가능하므로 부분범위 처리 방식으로 실행계획이 수립된다. 

 

예를 들어, 인덱스가 ORD_DATE에 존재하고 ORDER BY ord_date로 정렬을 요청한다면 정렬 없이도 결과를 순차 출력할 수 있다.
단, DESC를 사용한 역순 정렬은 인덱스가 역순 스캔 가능한 구조여야 한다.

집합 연산자와 부분범위 처리

UNION, MINUS, INTERSECT 등은 기본적으로 전체범위 처리를 필요로 한다. 이 연산자들은 결과에 대해 유일성 보장(SORT, DISTINCT)을 요구하기 때문이다. 

아래 예시를 보자.

SELECT deptno, empno
  FROM emp1
 WHERE sal > 1000000
UNION
SELECT deptno, empno
  FROM emp2
 WHERE hiredate BETWEEN '01-JAN-2015' AND SYSDATE;

이 경우는 중복 제거 작업이 생략되므로 각 집합을 독립적으로 부분범위 처리할 수 있다.

EXISTS와 IN 서브쿼리

EXISTS 또는 IN을 사용하는 서브쿼리는 부분범위 처리를 유도할 수 있다. 하지만 EXISTS의 실행 방식이 Sort Merge Join이면 전체범위 처리가 될 수 있다. 항상 부분범위 처리를 보장하진 않지만, 조건이 잘 설계된 EXISTS 서브쿼리는 상당히 빠르게 동작할 수 있다.

1.2.2. 옵티마이저 모드에 따른 부분범위 처리

SQL 문이 논리적으로는 부분범위 처리가 가능하더라도, 옵티마이저(Optimizer) 모드에 따라 실제 실행계획이 달라질 수 있다. 일반적으로 비용기준(Cost-based)의 FIRST_ROWS 모드에서는 가능한 한 빠르게 결과를 반환하도록 부분범위 처리 전략을 선호하지만, ALL_ROWS 모드는 전체 범위를 모두 처리하여 처리량(Throughput)을 최적화하려 하므로 전체범위 처리가 자주 나타난다.

 

예제 SQL은 다음과 같다.

SELECT ord_dept, ordqty
FROM order
WHERE ord_dept > '1000';

이 SQL은 논리적으로 부분범위 처리를 제한하는 함수가 없으므로 부분범위 처리가 가능하다. 그러나 옵티마이저의 목표가 ALL_ROWS인 경우에는 실행계획이 달라져 전체범위 처리가 될 수 있다.

 

예를 들어 ord_dept 컬럼이 인덱스로 존재한다면 옵티마이저는 이 인덱스를 사용하지 않고 전체 테이블을 액세스하는 실행계획을 수립할 수 있다. 이는 비용기준상 효율적이라 판단했기 때문이다.

 

만약 인덱스를 사용하도록 유도하려면 ORDER BY ord_dept를 명시하는 것이 좋다. 다만 ORDER BY를 무조건 사용하는 것이 최선은 아니며, 옵티마이저가 실행계획에서 전제범위 처리를 하는지를 반드시 확인해야 한다. 인덱스를 활용하는 힌트로는 INDEX나 FIRST_ROWS를 사용할 수 있다.

 

옵티마이저 모드가 ALL_ROWS인 경우는 전체범위를 모두 처리해 최적의 처리량(BEST THROUGHPUT)을 추구하고, FIRST_ROWS는 빠른 응답시간을 목표로 하기 때문에 가능한 한 빨리 결과를 반환하려 한다.

 

결론적으로 동일한 SQL이라도 옵티마이저 모드에 따라 실행계획이 달라질 수 있으며, 특히 온라인 시스템에서는 FIRST_ROWS 모드를 사용하는 것이 권장된다. 다만, 이 또한 SQL의 성격에 따라 달라지므로 FIRST_ROWS라고 하여 항상 부분범위 처리를 수행하는 것은 아니다. 쿼리 자체가 전체범위 처리를 요구하는 구조라면, ALL_ROWS 모드와 동일한 결과를 얻는다.

 

1.3. 부분범위처리의 수행속도 향상원리 요약

다음 SQL은 조건이 없어 전체 테이블을 스캔하는 대신, 첫 블록부터 로우를 순차적으로 추출하여 운반단위로 처리함.

SELECT * FROM ORDER;

위 쿼리를 실행하면 전체 테이블을 스캔하지 않고 첫 번째 블록부터 순차적으로 로우를 추출하여 운반단위 단위로 처리하게 된다. 이와 같은 경우, 옵티마이저는 WHERE 절이 없어도 전체 데이터를 읽는 대신 필요한 만큼만 읽고 결과를 추출한다. 

실제로 SQL*Plus에서 실행해 보면, 수십만 건의 데이터를 0.01초 이내에 추출할 수 있을 정도로 매우 빠른 수행속도를 보여준다.

 

다음 SQL에서 ORDER BY가 추가되면 수행속도는 현저히 느려짐.

SELECT * FROM ORDER
ORDER BY item;

이는 정렬 그 자체 때문이 아니라, 전체 테이블의 범위를 모두 메모리에 저장하고 정렬을 수행한 뒤, 다시 결과를 추출하는 방식이기 때문이다. 즉, 정렬 작업은 빠르더라도, 전체 데이터를 다 읽어야 하므로 성능 저하가 발생하게 된다.

 

아래처럼 WHERE 절을 추가하여 조건을 걸면 옵티마이저는 인덱스를 활용하여 부분범위처리 수행 가능하다.

SELECT * FROM ORDER
WHERE ITEM > ' ';

ITEM 컬럼이 정렬된 인덱스를 가지고 있다면 ORDER BY를 사용하지 않아도 정렬된 결과를 얻을 수 있으며, 빠른 처리속도를 기대할 수 있다. 단, 옵티마이저의 목표가 ALL_ROWS로 설정되어 있다면 전체 테이블을 스캔하는 실행계획이 수립될 수 있으므로 주의가 필요하다.

 

명시적으로 인덱스를 활용하고 싶다면 힌트를 사용할 수 있다. 예를 들어, 다음과 같이 작성하면 옵티마이저가 item_index 인덱스를 활용하도록 유도할 수 있다.

SELECT /*+ index(order item_index) */ *
FROM ORDER
WHERE ITEM > ' ';

 

범위를 좁혀 빠르게 처리하고자 할 때는 다음과 같이 두 조건을 조합할 수 있다.

SELECT *
FROM ORDER
WHERE ordno BETWEEN 1 AND 1000
  AND custno LIKE 'DN%';

SELECT /*+ INDEX(order custno_index) */ *
FROM ORDER
WHERE ordno BETWEEN 1 AND 1000
  AND custno LIKE 'DN%';

 

하지만 옵티마이저는 조건이 많다고 항상 모든 인덱스를 사용하는 것은 아니며, 필요에 따라 하나의 인덱스만 사용해 처리할 수 있다. 이럴 경우에는 힌트 등을 통해 실행 계획을 유도해야 한다.

액세스 주관조건 범위	검증조건 범위	수행속도	조치사항
좁다	좁다	양호	-
	넓다	양호	-
넓다	좁다	불량	주관조건과 검증조건 역할 교체
	넓다	양호	-

1.4 부분범위처리로의 유도

부분범위처리를 효율적으로 활용하면, 기존에는 전체 범위를 처리해야 했던 작업에서도 빠른 수행 속도를 기대할 수 있다. 물론 모든 상황에서 이를 적용할 수 있는 것은 아니지만, RDBMS가 제공하는 다양한 기능을 최대한 활용하면 더 많은 처리를 부분범위처리 방식으로 유도할 수 있다.

 

부분범위처리를 유도할 수 있는 대표적인 기법은 다음과 같다.

• 액세스 경로를 이용한 SORT의 대체
• 인덱스를 통한 부분범위 처리
• MIN, MAX 처리
• FILTER 형태의 부분범위 처리
• ROWNUM을 활용한 부분범위 처리
• 인라인 뷰를 통한 처리
• 저장형 함수 활용
• 커리의 이원화
• 웹 게시판에서의 응용 처리

해당 절에서는 개념 수준의 설명에 머물렀지만, 실제 업무에서는 이러한 기법들이 다양하게 활용된다. 또한 향후 다른 액세스 형태와 비교될 때에도 우선적으로 검토되어야 할 중요한 처리 방식이다.

SELECT /*+ INDEX_DESC(a ord_dept_index) */
       ord_dept, ordqty * 1000
FROM   order a
WHERE  ord_date LIKE '2015%'
  AND  ord_dept > ' ';

위 쿼리는 ord_date 조건만 사용했을 때보다 ord_dept 조건을 추가함으로써, 액세스를 주관하는 컬럼과 정렬 조건이 일치하게 되어 보다 빠른 수행속도를 얻을 수 있다. 이는 “액세스 주관 컬럼의 범위가 넓어도 다른 조건이 좁으면 빠르게 처리할 수 있다”는 원리를 보여준다.

 

부분범위처리를 유도할 때 주의해야 할 점 중 하나는 액세스 주관 컬럼을 명시적으로 지정하는 것이다. 예를 들어 ord_dept_index와 같은 인덱스를 힌트를 통해 지정하면 옵티마이저가 원하는 실행 계획을 수립하는 데 도움이 된다.

 

하지만 ord_date와 같은 조건이 바인드 변수 :date로 설정되어 있다면 주의가 필요하다. 바인드 변수는 실행 시점까지 값이 정해지지 않기 때문에, 해당 조건의 처리 범위가 좁아질 수도 있지만 반대로 넓어질 가능성도 있어 부분범위처리에 불리하게 작용할 수 있다. 특히 한 달치처럼 범위가 넓은 상수값이 입력되면, 오히려 전체범위처리로 전환되어 성능 저하가 발생할 수 있다.

 

이러한 문제를 해결하기 위해서는 다음과 같은 방법이 있다.

• 동적 SQL을 사용하여 실행 시점에 정확한 값을 기준으로 최적의 실행 계획을 수립할 수 있도록 한다.
• SQL*Forms 환경에서는 PRE_QUERY를 통해 조건값에 따라 적절한 튜닝을 적용할 수 있다.
• 프리컴파일러(Pre_Compiler)를 사용하는 경우에는 동적 SQL을 사용하거나 커서를 별도로 분리하여 SQL을 적용해야 한다.

1.4.2. 인덱스만 액세스하는 부분범위처리

옵티마이저는 사용자 의도와 관계없이, 인덱스만으로 처리할 수 있다고 판단되면 테이블을 액세스하지 않고 인덱스만을 활용한 실행계획을 수립한다. 이처럼 인덱스만 사용하는 처리 방식은 처리범위가 넓더라도 랜덤 액세스를 하지 않기 때문에 매우 효율적인 방식이다.

 

인덱스만으로 실행계획이 수립되기 위해선 다음 조건 중 하나를 충족해야 한다.

• 쿼리에 사용된 모든 컬럼이 하나의 인덱스에 포함되어 있어야 한다.
• 인덱스 머지(Merge)가 가능한 경우, 두 개 이상의 인덱스를 결합해 모든 컬럼을 포함해야 한다.
• 쿼리에 사용된 전체 컬럼이 여러 개의 인덱스에 모두 포함되어 인덱스 조인(Index Join)으로 처리될 수 있어야 한다.

이러한 조건을 충족하면 옵티마이저는 테이블을 전혀 액세스하지 않고 인덱스만으로 실행계획을 구성할 수 있다.

 

후보 컬럼, 즉 SELECT 대상 컬럼의 선택은 인덱스만 액세스 가능 여부에 큰 영향을 미치므로 인덱스 설계 시 전략적 판단이 필요하다.

SELECT ord_date, SUM(qty)
FROM order
WHERE ord_date LIKE '201510%'
GROUP BY ord_date;

이 경우 ORD_DATE 인덱스만 존재하면 테이블 액세스가 발생하지만, ORD_DATE + QTY 복합 인덱스를 구성하면 옵티마이저는 인덱스만으로 처리할 수 있다. 이로 인해 처리 속도가 200~500% 향상될 수 있다.

 

SELECT ord_dept, COUNT(*)
FROM order
WHERE ord_date LIKE '201510%'
GROUP BY ord_dept;

위 쿼리는 ORD_DATE + AGENT_CD 인덱스를 사용할 경우 전체 테이블을 스캔하여 랜덤으로 데이터를 추출하고, 이후 GROUP BY 결과를 출력한다.

 

반면 다음 쿼리는

SELECT agent_cd, COUNT(*)
FROM order
WHERE ord_date LIKE '201510%'
GROUP BY agent_cd;

같은 인덱스를 사용하지만 옵티마이저는 인덱스만으로 처리할 수 있어 훨씬 빠른 성능을 보인다.

1.4.3. MIN, MAX의 처리

대량의 데이터에서 최소값(MIN), 최대값(MAX) 또는 기본키 생성을 위한 최대값 + 1 형식의 처리는 흔히 사용되며, 특히 기본키의 마지막 값을 찾아 다음 값을 부여하는 방식은 많은 시스템에서 사용된다. 그러나 실무에서는 다음과 같은 문제들이 발생할 수 있다

• 마지막 값을 가져오는 작업은 동시에 여러 사용자가 수행할 경우 락(Lock)이 걸릴 수 있다.
• 전체 테이블 스캔 또는 정렬 필요로 인해 속도가 저하된다.

이를 해결하기 위한 첫 번째 방법은 별도 테이블을 이용하는 방식이다. 각 범위마다 별도의 테이블을 생성하고, 트랜잭션마다 해당 테이블에서 값을 읽고 사용하는 방식이다. 이는 현재 많은 사용자들이 채택하고 있는 방식이지만, 트랜잭션이 겹치는 경우 충돌이 발생하고 락(Lock)이 걸릴 수 있으며, 전체적인 성능 저하의 원인이 된다.

 

두 번째 방법은 시퀀스(Sequence)를 사용하는 것이다. Oracle과 같은 RDBMS는 자동으로 증가하는 시퀀스 객체를 제공하며, 이를 활용하면 별도의 락 없이도 빠른 처리 속도를 얻을 수 있다. 시퀀스는 메모리 내에서 관리되므로 매우 효율적이고, 동시에 여러 사용자가 접근해도 락이 발생하지 않는다.

 

시퀀스는 세션 단위로 관리되며, NEXTVAL을 먼저 호출한 후에야 CURRVAL을 사용할 수 있다. 만약 한 세션에서 NEXTVAL을 호출하지 않고 CURRVAL을 요청하면 오류가 발생한다. 또한 시퀀스는 CACHE 기능을 통해 일정 수의 값을 메모리에 미리 확보해 놓고 사용한다. 예를 들어 CACHE 20으로 설정된 경우, 20개의 값을 메모리에 미리 로드하고 순차적으로 제공한다.

 

하지만 CACHE 기능을 사용할 경우, 시스템이 비정상적으로 종료되면 아직 사용되지 않은 일부 값은 손실될 수 있다. 이로 인해 누락된 번호가 생기는데, 이 현상을 문제로 간주해 시퀀스를 회피하는 경우도 있다. 그러나 성능 면에서는 여전히 시퀀스가 가장 우수한 방식 중 하나이다.

 

세 번째 방법은 기존 테이블에서 MAX 값을 구하고 여기에 1을 더하는 방식이다. 이 방식은 간단하며, 특히 특정 부서나 조건에 따라 일련번호를 생성할 때 자주 사용된다. 그러나 과거에는 전체 범위를 스캔하고 정렬해야 했기 때문에 성능 저하가 있었다. 최근에는 옵티마이저가 RANGE SCAN(MIN/MAX)과 FIRST ROW 방식으로 처리하여, 해당 조건을 만족하는 인덱스를 빠르게 순회하며 첫 번째 로우만 가져오는 방식으로 성능을 극대화할 수 있다.

 

이 때 ROWNUM = 1 조건을 추가하면, 옵티마이저는 첫 로우만 추출하고 즉시 작업을 종료하므로, 전체 테이블을 스캔할 필요 없이 빠르게 원하는 값을 얻을 수 있다. 이 방식은 NVL 함수를 함께 사용하면 NULL 값을 방지하고 항상 값이 리턴되도록 구성할 수 있다.

 

실제 업무에서는 복합 인덱스를 활용해 효율적인 처리를 유도할 수 있다. 예를 들어 ORD_DATE와 SAL_AMOUNT로 구성된 인덱스가 있을 경우, ORD_DATE 조건을 기반으로 인덱스를 역순으로 스캔하여 AGENT_CD 조건을 만족하는 첫 번째 로우를 찾는다. 만족하지 않으면 다음 로우로 넘어가며 조건을 반복적으로 확인한다.

 

이러한 방식은 조건을 만족하는 로우가 인덱스 상에서 앞부분에 존재할 가능성이 높을 경우, 성능상 매우 유리하다. 또한 AGENT_CD가 인덱스에 포함되어 있지 않더라도, WHERE 조건에서 해당 값을 필터링하는 데에는 영향을 주지 않는다. 결국 인덱스 구성과 액세스 전략을 잘 결합하면, 부분범위처리를 통해 전체 범위를 스캔하지 않고도 원하는 결과를 빠르게 추출할 수 있다.

1.4.4 FILTER형 부분범위

애플리케이션을 개발하는 과정에서 특정 조건을 만족하는 데이터가 존재하는지만 확인하고, 그 결과에 따라 이후 처리를 진행해야 하는 상황이 자주 발생한다. 예를 들어 존재 여부에 따라 다음 단계를 수행하거나, 존재하지 않으면 대체 처리를 해야 하는 경우가 그렇다.

 

이때 존재 여부만 확인하는 작업은 결과가 몇 건인지보다 조건을 만족하는 첫 번째 로우만 찾으면 되므로, 조건에 맞는 로우를 하나만 찾아도 처리를 끝낼 수 있다. 그럼에도 불구하고 많은 개발자들이 아무런 고려 없이 전제범위를 처리하는 SQL을 사용하여 성능 저하를 초래하는 경우가 많다.

 

예를 들어 집합의 존재 여부를 확인하기 위해 COUNT(*) 등의 집계함수를 사용하는 방식은, 조건을 만족하는 데이터가 1건이라도 존재하면 충분함에도 불구하고 전체 범위의 정렬 및 COUNT 작업을 수행하는 매우 비효율적인 방식이다. 반면, EXISTS 구문을 활용하면 첫 번째 조건을 만족하는 로우를 찾는 즉시 처리를 중단할 수 있으므로, 성능이 훨씬 우수하다.

 

EXISTS는 서브쿼리의 조건을 만족하는지 여부만 판단하는 불린(Boolean) 함수로, 서브쿼리 내에서 조건을 만족하는 로우가 존재하면 즉시 true를 반환하고 수행을 중단한다. 따라서 EXISTS를 활용하면 부분범위 처리를 유도할 수 있으며, 불필요한 전체 범위 접근을 방지할 수 있다.

 

실행계획을 살펴보면 EXISTS를 사용한 경우, 내부적으로는 FILTER 연산이 수행되며, 이는 NESTED LOOPS 조인과 유사한 형태로 처리된다. DUAL 테이블을 이용하여 서브쿼리의 결과 여부를 판별하고, 해당 결과가 존재하면 다음 로직을 실행하는 방식이다. 이는 서브쿼리와 메인쿼리가 함께 실행되더라도 실제로는 단 한 번만 수행된다는 점에서 성능상 이점이 있다.

 

EXISTS를 활용한 방식은 MINUS와 같은 집합 연산보다도 성능상 우위를 보일 수 있다. 예를 들어 MINUS는 두 집합 각각에 대해 전체 범위를 처리한 후 결과를 비교하여 결과를 반환하지만, EXISTS는 특정 조건을 만족하는 로우가 존재하는지만 체크하기 때문에 더 빠른 처리가 가능하다.

 

단, NOT EXISTS를 사용하는 경우에는 조건에 맞는 값이 존재하지 않을 때를 확인하므로, 드라이빙 조건의 범위가 넓어질 수 있다. 이 경우에는 실행 계획의 범위를 더욱 넓게 잡게 되어 EXISTS에 비해 성능상 저하가 발생할 수 있으므로, 조건에 맞는 데이터가 많지 않을 때에만 사용해야 한다.

 

결론적으로, 단순 존재 여부를 확인할 때는 COUNT 같은 집계 함수보다는 EXISTS를 활용해 조건을 만족하는 첫 로우를 빠르게 찾아내는 FILTER형 부분범위 처리가 성능 최적화에 효과적이다. 이와 같은 전략적 사용을 통해 불필요한 자원 낭비를 줄이고 대용량 데이터 환경에서 효율적인 처리를 유도할 수 있다.

1.4.5. ROWNUM의 활용

ROWNUM은 테이블에 물리적으로 존재하는 컬럼이 아니라 SYSDATE나 USER처럼 SQL 실행 시점에 자동 생성되는 가상(Pseudo) 컬럼이다. 이 값은 SQL 실행 과정 중에 생성되며, 추출된 결과에 대해 일련번호를 부여하는 방식으로 작동한다.

 

이러한 특성 덕분에 ROWNUM은 조건절에 활용되어 원하는 개수만큼만 처리하도록 제어할 수 있으며, 전체 범위를 모두 처리하지 않고 일부만 처리할 수 있는 부분범위 처리 방식으로 활용 가능하다.

 

하지만 ROWNUM이 언제, 어떤 순서로 결정되는지 정확히 이해하지 못하고 사용할 경우 의도하지 않은 결과가 발생할 수 있다. 특히 ROWNUM = N과 같은 직접 비교는 원하는 결과를 얻기 어렵다.

다음은 기본적인 ROWNUM 조건 사용 예제이다.

SELECT *
FROM   ITEM_TAB
WHERE  DEPT = '101'
AND    SEQ > 100
AND    ROWNUM <= 10;

이 쿼리는 DEPT와 SEQ 조건을 만족하는 로우 중에서 최대 10건까지만 추출한다. 인덱스를 이용해 조건을 만족하는 로우가 발견되면 ROWNUM이 부여되며, 10건에 도달하면 SQL 실행이 멈춘다.

SELECT ROWNUM, item_cd, category_cd
FROM   product
WHERE  deptno LIKE '1208%'
AND    qty > 0
AND    ROWNUM <= 10
ORDER BY item_cd;

이 쿼리는 정렬 전에 ROWNUM <= 10이 먼저 적용되기 때문에, 정렬이 되지 않은 상태의 10건이 추출된다. 즉, 우리가 원하는 "정렬 후 상위 10건"이 아닌 결과가 나올 수 있다.

 

이것을 해결하기 위해 아래와 같이 서브쿼리를 활용하면 된다.

SELECT ROWNUM, item_cd, category_cd
FROM (
  SELECT item_cd, category_cd
  FROM   product
  WHERE  deptno LIKE '1208%'
  AND    qty > 0
  ORDER BY item_cd
)
WHERE ROWNUM <= 10;

 

그리고 GROUP BY 결과에서 특정 순서의 로우를 추출하려면 서브쿼리 내에서 ROWNUM을 부여하고 외부에서 필터링한다.

SELECT deptno, totqty
FROM (
  SELECT ROWNUM AS RNUM, deptno, totqty
  FROM (
    SELECT deptno, SUM(qty) AS totqty
    FROM   product
    WHERE  deptno LIKE '120%'
    AND    ROWNUM <= 100
    GROUP BY deptno
  )
)
WHERE RNUM = 5
AND   ROWNUM = 1;

이 쿼리는 GROUP BY 결과에서 5번째 로우만 추출한다. 단, 내부의 ROWNUM <= 100은 GROUP BY 실행 전에 필터링되므로, 원하는 대상 범위를 넓히거나 정확한 제어가 필요할 경우 주의해야 한다.

 

뷰를 정의하고 그 결과에 대해 ROWNUM을 적용할 수 있는데

CREATE VIEW PROD_VIEW (deptno, totqty) AS
SELECT deptno, SUM(qty)
FROM   product
GROUP BY deptno;

SELECT ROWNUM, deptno, totqty
FROM   PROD_VIEW
WHERE  deptno LIKE '1208%'
AND    ROWNUM <= 10;

이 방법은 GROUP BY 결과에 대한 조건과 ROWNUM을 분리해 명확한 로직 구성을 가능하게 한다.

 

사용자가 설정한 조건이 애매하거나 부정확할 경우, ROWNUM으로 검색 범위를 제한하면 지나친 액세스를 방지할 수 있다.

SELECT select_list
FROM   access_tables
WHERE  conditions
AND    ROWNUM <= 1000;

이 쿼리는 예상치 못한 과다한 결과 추출을 방지하는 데 유용하며, 일종의 안전장치로 작용한다.

1.4.6. 인라인뷰를 이용한 부분범위 처리

인라인뷰를 활용하여 SQL의 부분범위 처리를 유도하는 기법은 전체범위 처리를 방지하고, 필요한 대상에만 집중하여 효율적인 실행 계획을 유도하는 데 목적이 있다. 이는 전체 범위로 처리될 수 있는 테이블을 인라인뷰로 감싸서 처리 순서를 조정하거나, 데이터의 양이 많은 테이블을 나중에 조회되도록 분리함으로써 실행 속도를 향상시키는 방식이다.

 

예를 들어, 서울에 근무하는 매니저들의 월별 급여 정보를 조회하는 SQL을 보자. 해당 쿼리는 DEPARTMENT, EMPLOYEE, SALARY 세 테이블이 조인되며, 특히 SALARY 테이블은 최종 정렬 대상이 되어 전체범위 처리로 이어질 수 있다.

SELECT a.dept_name, b.empno, b.emp_name, c.sal_ym, c.sal_tot
  FROM department a, employee b, salary c
 WHERE a.deptno = b.deptno
   AND b.empno = c.empno
   AND a.location = 'SEOUL'
   AND b.job = 'MANAGER'
   AND c.sal_ym = '201512'
 ORDER BY a.dept_name, b.hire_date, c.sal_ym;

이 SQL은 논리적으로는 문제가 없지만 실행 계획상 SALARY 테이블까지 포함한 전체 조인을 처리하게 되므로 비효율적이다.

이 때 인라인뷰를 사용하면 DEPARTMENT와 EMPLOYEE 테이블을 먼저 필터링한 결과를 미리 정렬해두고, 그 결과를 SALARY 테이블과 조인하게 유도할 수 있다.

SELECT /*+ ORDERED, USE_NL(x y) */
       a.dept_name, b.empno, b.emp_name, c.sal_ym, c.sal_tot
  FROM (SELECT a.dept_name, b.hire_date, b.empno, b.emp_name
          FROM dept a, employee b
         WHERE a.deptno = b.deptno
           AND a.location = 'SEOUL'
           AND b.job = 'MANAGER'
         ORDER BY a.dept_name, b.hire_date) x,
       salary y
 WHERE y.empno = x.empno
   AND y.sal_ym = '201512';

이 방식은 SALARY처럼 데이터량이 많고 집합이 큰 테이블을 나중에 접근하도록 하여 필요한 부분만 처리하게 유도하고, 실행 계획상 Nested Loops 조인 방식으로 최적화되도록 만든다.

 

또한, GROUP BY 집계를 사용하는 경우에도 마찬가지로 인라인뷰를 활용하여 먼저 처리할 수 있다. 예시는 아래와 같다.

SELECT a.product_cd, product_name, avg_stock
  FROM product a,
       (SELECT product_cd, SUM(stock_qty) / (:b2 - :b1) avg_stock
          FROM prod_stock
         WHERE stock_date BETWEEN :b1 AND :b2
         GROUP BY product_cd) b
 WHERE b.product_cd = a.product_cd
   AND a.category_cd = '20';

이 쿼리는 특정 제품군의 평균 재고를 계산하면서 불필요한 PRODUCT 테이블의 전체 스캔을 피하기 위해 인라인뷰로 PROD_STOCK을 미리 집계 처리하도록 유도한 것이다. 이를 통해 데이터 범위를 축소하고 선택적 액세스가 가능한 구조로 최적화할 수 있다.

1.4.7 저장형 함수를 이용한 부분범위 처리

저장형 함수의 가장 큰 특징은 SQL 내부에서 절차형 처리를 수행할 수 있다는 점이다. SQL은 본질적으로 비절차형 언어이며, 복잡한 데이터 처리나 반복적인 작업을 구현하는 데 한계가 있기 때문에 저장형 함수를 이용하면 이를 보완할 수 있다.

 

특히 복잡한 로직을 집합 연산 없이 절차적으로 처리해야 하는 경우, 저장형 함수는 매우 유용하다. 예를 들어, 중첩된 반복문이나 조건문, 변수 사용 등을 통해 특정 조건에 맞는 데이터를 동적으로 처리할 수 있으며, 이러한 절차는 일반적인 SQL 문장만으로는 구현이 어렵다.

 

다만, 저장형 함수는 단점도 존재한다. 예를 들어 함수가 한 번 호출될 때마다 한 개의 결과만 리턴하므로, 다수의 로우를 처리해야 하는 경우 반복적으로 호출되어 성능에 부담을 줄 수 있다. 또한 저장형 함수는 SQL 내에서 숨겨진 루프를 발생시키기 때문에 전체범위 처리를 야기할 가능성이 크다.

 

이로 인해 처리 시간이 상당히 길어질 수 있으며, 온라인에서의 대량 조회에는 부적합할 수 있다. 특히 단위 처리 시간이 0.01초에 불과하더라도 반복 호출되면 전체 처리 시간이 수 초를 넘길 수 있기 때문에, 온라인 시스템에서는 사용을 지양해야 한다.

 

배치 처리에서는 이러한 단점이 어느 정도 완화될 수 있다. 절차형 언어를 기반으로 하는 배치 환경에서는 저장형 함수의 사용이 오히려 유리할 수 있으며, 실제로 이미 많은 곳에서 이러한 방식이 활용되고 있다.

 

아래는 저장형 함수를 이용하여 특정 상품의 기간 내 평균 재고량을 계산하는 예제이다.

CREATE OR REPLACE FUNCTION GET_AVG_STOCK
(
    v_start_date IN DATE,
    v_end_date IN DATE,
    v_product_cd IN VARCHAR2
)
RETURN NUMBER IS
    RET_VAL NUMBER(14);
BEGIN
    SELECT SUM(stock_qty) / (v_start_date - v_end_date)
    INTO RET_VAL
    FROM PROD_STOCK
    WHERE product_cd = v_product_cd
      AND stock_date BETWEEN v_start_date AND v_end_date;
    RETURN RET_VAL;
END GET_AVG_STOCK;

이 함수를 이용한 SQL 예시는 다음과 같다.

SELECT product_cd, product_name,
       GET_AVG_STOCK(product_cd, :b1, :b2) AS avg_stock
FROM   PRODUCT
WHERE  category_cd = '20';

이 예제는 어느 조건에서도 부분범위 처리가 되지 않으며, 모든 PRODUCT 로우에 대해 저장형 함수를 호출해야 하므로 전체범위 처리를 수반하게 된다. 이는 성능 저하의 원인이 될 수 있으며, 저장형 함수를 쿼리 내에 삽입할 때에는 이러한 점을 반드시 고려해야 한다.

 

가) 확인자 역할의 M 집합 처리를 위한 부분범위처리

우리가 처리하고자 하는 집합이 1:M 관계에 있을 경우, M 측 집합의 결과는 일반적으로 M개의 결과가 존재하게 된다. 만약 이 결과를 단지 확인자 역할(존재 유무 판단)을 위해 사용하고자 한다면, 다시 원래의 집합으로 돌아오기 위한 GROUP BY나 DISTINCT 등 불필요한 부가 처리까지 요구되게 된다. 따라서 이러한 경우에는 서브쿼리를 활용하는 방식이 효율적이다.

 

서브쿼리를 활용하면 M 집합의 내용을 직접적으로 결합하지 않고도 존재 여부 등을 확인할 수 있어 효율적이지만, 만약 금액 등의 구체적인 값을 리턴받아야 한다면 서브쿼리 방식은 적합하지 않다. 특히, 복잡한 다중 테이블 조인이나 다양한 조건이 포함된 상황에서는 서브쿼리 적용이 어려워지고, 이때 저장형 함수가 유효한 대안으로 떠오른다.

아래는 서브 쿼리를 활용한 기본 SQL이다.

SELECT y.cust_no, y.cust_name, x.bill_tot, ...
FROM (
    SELECT a.cust_no, SUM(b.bill_amt) bill_tot
    FROM account a, charge b
    WHERE a.acct_no = b.acct_no
      AND b.bill_cd = 'FEE'
      AND b.bill_ym BETWEEN :b1 AND :b2
    GROUP BY a.cust_no
    HAVING SUM(b.bill_amt) >= 1000000
) x,
customer y
WHERE y.cust_no = x.cust_no
  AND y.cust_status = 'ARR'
  AND ROWNUM <= 30;

이 SQL은 체납 상태('ARR')인 고객 중에서 특정 청구 조건에 부합하는 고객들을 추출하는 쿼리로, 고객 테이블에서 조건을 만족하는 서브쿼리 결과와 조인한 후 30명까지 제한하여 출력한다. 그러나, 전체 CUSTOMER를 다 조인하고 나서 30명을 선택하게 되므로 효율성이 떨어진다.

 

다음은 EXISTS를 활용한 시도의 예이다.

SELECT y.cust_no, y.cust_name, ...
FROM customer y
WHERE y.cust_status = 'ARR'
  AND EXISTS (
    SELECT 'x'
    FROM account a, charge b
    WHERE a.cust_no = y.cust_no
      AND a.acct_no = b.acct_no
      AND b.bill_cd = 'FEE'
      AND b.bill_ym BETWEEN :b1 AND :b2
    GROUP BY a.cust_no
    HAVING SUM(b.bill_amt) >= 1000000
  )
  AND ROWNUM <= 30;

이 방식은 서브쿼리를 사용하지만 청구 금액을 리턴받지 못한다는 단점이 있다. 단순히 존재 여부만 확인하고 조건을 만족하는 고객 30명을 리턴할 수는 있지만, bill_amt 총액 등 결과를 활용해야 할 경우 적합하지 않다.

 

다음은 저장형 함수 활용한 예이다.

CREATE OR REPLACE FUNCTION CUST_ARR_FEE_FUNC (
  v_costno IN VARCHAR2,
  v_start_ym IN VARCHAR2,
  v_end_ym IN VARCHAR2
) RETURN NUMBER IS
  RET_VAL NUMBER(14);
BEGIN
  SELECT SUM(bill_amt) INTO RET_VAL
  FROM account a, charge b
  WHERE a.acct_no = b.acct_no
    AND a.cust_no = v_costno
    AND b.bill_cd = 'FEE'
    AND b.bill_ym BETWEEN v_start_ym AND v_end_ym;
  RETURN RET_VAL;
END;

SELECT cust_no, cust_name,
       CUST_ARR_FEE_FUNC(cust_no, :b1, :b2) AS bill_tot, ...
FROM customer
WHERE cust_status = 'ARR'
  AND CUST_ARR_FEE_FUNC(cust_no, :b1, :b2) >= 1000000
  AND ROWNUM <= 30;

이 방법은 실제로 bill_amt 금액을 리턴받을 수 있어 유용하며, 조건에 부합하는 고객만 선별적으로 조회가 가능해진다. 다만 SELECT-List와 WHERE 절에 동일한 저장형 함수가 중복 호출되어 중복 수행 문제가 발생할 수 있다.

 

그리고 다음은 인라인뷰와 저장형 함수 병합을 사용한 예이다.

SELECT cust_no, cust_name, bill_tot, ...
FROM (
    SELECT ROWNUM, cust_no, cust_name,
           CUST_ARR_FEE_FUNC(cust_no, :b1, :b2) AS bill_tot, ...
    FROM customer
    WHERE cust_status = 'ARR'
) 
WHERE bill_tot >= 1000000
  AND ROWNUM <= 30;

이 방법은 인라인뷰 내부에서 먼저 저장형 함수를 호출하여 결과를 미리 준비해두고, 외부 쿼리에서 조건을 적용하기 때문에 저장형 함수가 한번만 수행되는 효과를 얻을 수 있다.

인라인뷰에 ROWNUM이 있는 경우, 실행계획 상에 COUNT STOPKEY가 포함되어 30건을 만족하면 실행을 중지하는 효과가 있다.

하지만 서브쿼리 내에서만 ROWNUM을 사용하는 것과 메인쿼리에서 쓰는 것은 차이가 있으므로 주의가 필요하다.

그리고 ROWNUM 외에도 ORDER BY나 GROUP BY 같은 연산을 통해서도 비슷한 부분범위처리 효과를 얻을 수 있다.

나) 특정 부분만 부분범위처리로 유도

실무에서 온라인 처리를 수행할 때, 전체 범위를 처리해야 하는 경우는 많지만, 실행 속도나 처리 성능의 문제로 인해 전 범위 처리를 감당할 수 없는 상황이 자주 발생한다. 원래 처리 범위는 작지만, 액세스 방법이나 인덱스 설계의 한계 등으로 처리 성능이 저하되면 단순한 튜닝만으로는 해결되지 않는 경우가 많다.

 

이러한 경우, 미리 가공된 집계 테이블을 사용하는 방식이 흔히 쓰인다. 하지만 이는 일시적인 해결책일 뿐이며, 많은 경우는 '부분범위처리'를 통해 해결 가능하다는 점이 간과되고 있다. 특히 처리 대상 중 핵심 데이터만 먼저 처리하고, 이후 필요한 데이터에 대해서만 추가 처리를 유도할 수 있다면 효율적인 해결책이 될 수 있다.

 

예를 들어 3,000개의 대리점을 관리하는 회사에서 지난 1년간 월 매출을 기준으로 특정 월(예: 1월)의 매출 역순 정렬 결과에서 상위 몇 개 대리점을 추출한 뒤, 해당 대리점에 대해서만 나머지 11개월 데이터를 조회하도록 구성할 수 있다. 이 경우, 전체 월 데이터를 다 처리하지 않고 일부에 대해서만 집중함으로써 전체 처리 건수를 50,000건에서 11,000건 수준으로 줄일 수 있다.

 

이를 위해 저장형 함수를 정의해 필요한 월의 매출 총액을 반환하도록 구성하고, 메인 쿼리에서는 이 저장형 함수를 활용하여 조건을 만족하는 데이터만 추출하게 하면, 전체 범위가 아닌 특정 부분에 대해서만 처리하는 방식으로 성능을 향상시킬 수 있다.

SELECT sal_dept,
       agent_name,
       jan_tot,
       GET_AGENT_SALAMT(sal_dept, :year||'02') feb_tot,
       GET_AGENT_SALAMT(sal_dept, :year||'12') dec_tot
FROM (
    SELECT sal_dept,
           NVL(SUM(sal_amt), 0) jan_tot,
           MAX(agent_name) agent_name
    FROM sales s, agent a
    WHERE s.sal_dept = a.agent_no
      AND a.loc = :bl
      AND s.sal_date LIKE :year||'01'
    GROUP BY sal_dept
    ORDER BY jan_tot DESC
);

또한, 저장형 함수 내에서 결과값을 리턴할 때 단일 값만 리턴 가능하므로, 여러 값을 조합해 하나의 컬럼으로 만들어 반환하거나, 고정 길이로 맞추어 SUBSTR 함수 등으로 나중에 다시 분해해 활용할 수 있도록 설계할 수 있다.

 

이 방식은 실제로 테이블을 추가로 만들지 않고도 부분범위처리를 가능하게 하며, 특히 온라인 처리에서 성능 저하를 방지하고, 불필요한 전체 범위 접근을 줄이는 데 큰 도움이 된다.

 

단, 저장형 함수의 설계는 전략적이어야 하며, 단발성이나 특정 SQL만을 위해 함수를 만드는 것은 바람직하지 않다. 저장형 함수는 독립적으로 작동 가능한 오브젝트로, 여러 애플리케이션에서 공통적으로 재사용될 수 있도록 설계하는 것이 이상적이다.

1.4.8 쿼리의 분리를 이용한 부분범위처리

SQL은 실행되는 순간 전체 데이터 범위에 접근하려는 경향이 있어, 일부 조건만 필요한 경우에도 전 범위 처리가 이뤄지는 문제가 있다. 특히 주부(Master)와 종속부(Detail) 관계를 가지는 1:M 구조의 경우, 주부의 조건만으로 전체 종속 데이터를 처리하게 되면 불필요하게 많은 데이터를 조회하게 되어 성능에 영향을 줄 수 있다.

 

이러한 경우, 쿼리를 분리하여 먼저 주부 조건만을 선별한 후, 해당 조건에 맞는 종속부 데이터를 별도로 조회하도록 하면, 실행 성능을 크게 향상시킬 수 있다. 예를 들어 특정 부서 코드로 시작하는 사원들의 연간 급여 정보를 조회한다고 할 때, 전체 부서에 대한 급여를 한번에 조회하기보다 먼저 부서 목록만 조회한 후, 해당 부서 코드에 해당하는 급여만 별도로 조회하도록 구성할 수 있다.

 

아래는 그 예시이다.

-- 1단계: 조건에 해당하는 부서 코드 조회
SELECT deptno INTO :v_deptno
FROM dept
WHERE deptno LIKE '12%';

-- 2단계: 해당 부서의 사원 연간 급여 조회
SELECT x.deptno, y.ename, y.empno, y.job, x.sal_tot, x.comm_tot
FROM (
    SELECT empno,
           SUM(sal_amt) sal_tot,
           SUM(comm) comm_tot
    FROM salary
    WHERE deptno = :v_deptno
      AND sal_date BETWEEN '20150101' AND '20151231'
    GROUP BY empno
) x, employee y
WHERE y.empno = x.empno;

이와 같이 쿼리를 분리하면, 조건이 적은 테이블(DEPT)을 먼저 조회한 후, 필터된 조건을 바탕으로 상세 테이블(SALARY)을 조회하게 되어 전체 범위에 접근하지 않고 필요한 부분만 효율적으로 처리할 수 있다.

 

이 방법은 다음 두 가지 방식으로 활용될 수 있다.

• 버퍼 방식 - 화면에서 한 번에 처리할 수 있는 양만큼만 데이터를 조회하여 메모리에 버퍼링하고, 사용자가 추가 요청 시 다음 데이터를 다시 조회한다.
• 순차 조회 방식 - 사용자 입력에 따라 특정 부서코드를 하나씩 조회하며, 다음 조건의 데이터는 필요할 때 추가로 로딩하는 구조이다.

이러한 방식은 특히 LIKE '12%'처럼 인덱스가 잘 작동하는 경우 매우 효과적이며, SALARY처럼 데이터 양이 많은 테이블에 전체 접근하지 않아도 되어 실행 비용을 크게 줄일 수 있다. 물론 애플리케이션에서 약간의 처리 비용은 발생할 수 있으나, 사용자 조건에 따라 빠르게 응답할 수 있는 장점이 있다.

 

결론적으로, 쿼리 분리를 통해 전체 범위 처리를 피하고 조건에 맞는 최소 데이터만 조회함으로써 성능을 최적화할 수 있으며, 인덱스 변경 없이도 효과적인 설계를 할 수 있는 유용한 방법이다.

 

1.4.9. 웹 게시판에서의 부분범위처리

웹 기반 애플리케이션에서는 전체 범위 데이터를 처리하는 방식보다 부분범위 처리가 더욱 요구된다. 웹 환경에서는 세션 유지 시간이 제한되기 때문에, 전체 범위를 한 번에 처리하고 나면 다음 페이지로 이어서 데이터를 보여주는 것이 어려워진다.

 

이러한 제약 속에서, 한 페이지에 필요한 데이터만 우선 액세스하고 그 결과를 보여준 후 다음 페이지 요청이 있을 때 추가 액세스를 통해 데이터를 제공하는 방식이 가장 효율적이다. 예를 들어, 첫 페이지에서 1만 건 중 20건만 정렬해 보여준 후, 이후 페이지 요청이 오면 다음 20건을 보여주는 형태다.

 

만약 전체 데이터를 정렬한 후 필요할 때마다 이전 정렬 결과를 재사용하지 못하고 새로 정렬해야 한다면, 동일한 작업이 반복되어 비효율이 커진다. 이를 방지하기 위해 실무에서는 보통 커서 기반 처리나 세션 캐시 처리, 정렬 조건에 적합한 인덱스 활용 등을 통해 부분범위 처리를 수행한다.

 

특히 검색 결과가 많은 웹 게시판에서는 인덱스를 기반으로 정렬된 데이터를 처음 20건만 추출하고, 이후 페이지 요청에 따라 그 다음 건을 이어서 처리하면 성능상 매우 유리하다. 만약 ‘정렬 기준’에 적합한 인덱스가 없다면 옵티마이저 힌트를 통해 인덱스 활용을 유도할 수도 있다.

 

예를 들어, SAL_DEPT와 SEQ 컬럼이 키가 되는 경우 다음과 같이 WHERE 조건을 구성해 부분범위 처리를 유도할 수 있다.

SELECT * 
FROM SALES 
WHERE ( SAL_DEPT > '1230' ) 
   OR ( SAL_DEPT = '1230' AND SEQ >= 111 );

이 쿼리는 SAL_DEPT = '1230' AND SEQ = 111부터 시작하여 그 이후 건을 순차적으로 가져오도록 유도하며, 인덱스 범위 스캔이 가능한 조건을 만든다.

 

이처럼 전체 범위를 한 번에 처리하는 것이 아니라, 사용자의 요청에 따라 필요한 범위만 처리하는 방식을 통해 성능을 최적화할 수 있다. 특히, 연쇄 실행계획(concatenation execution plan)을 활용하면 여러 독립적인 액세스 단위를 조합하여 실행되기 때문에, 불필요한 전체 범위 액세스를 피하면서도 원하는 결과를 효율적으로 도출할 수 있다.

 

웹 게시판의 구조에 따라 댓글이나 대댓글 등의 계층적 구조가 있는 경우에는 단순한 부분범위 처리가 어려울 수 있지만, 일반적인 리스트 구조의 게시판이라면 인덱스를 활용한 정렬과 범위 지정으로 효율적인 부분범위 처리가 가능하다.

 

가) 웹 게시판 부분범위처리 사례 1 (NON-UNIQUE INDEX)

웹 게시판과 같은 애플리케이션에서 고객 이름을 기준으로 검색할 때, 전체 데이터를 처리하지 않고 필요한 부분만 효율적으로 액세스하기 위해 비고유 인덱스(non-unique index)를 활용한 부분범위처리 기법이 사용된다.

 

사용자가 특정 고객명을 조건으로 검색하는 경우, 예를 들어 "고객명 LIKE '김%' AND 이름 >= '김길동'"처럼 조건이 주어졌다고 가정하면, 원하는 페이지 분량만큼(예: 25건)만 추출되도록 설계할 수 있다. 인덱스는 cust_name을 기준으로 구성되어 있고, 인덱스명은 CUST_NAME_IDX라 한다. 이 인덱스는 고유(UNIQUE)가 아니며, 중복값이 있을 수 있다.

 

SQL 쿼리는 다음과 같이 3개의 SELECT 문으로 나뉘고, UNION ALL로 결합된다. 이는 각각 아래의 조건을 처리하기 위함이다.

 

처음 페이지 요청 시 (:v2 = 'FIRST')

SELECT /*+ index(w cust_name_idx) */
ROWIDTOCHAR(rowid) rid, cust_name, ...
FROM cust_table w
WHERE :v2 = 'FIRST'
  AND cust_name LIKE :v1 || '%'
  AND ROWNUM <= 25;

이렇게 하면 고객명 인덱스를 이용해 처음 25건만 추출하며, 효율적이고 빠른 액세스가 가능하다.

 

다음 페이지 요청 시 (:v2 = 'NEXT')

SELECT /*+ use_concat index(y cust_name_idx) */
...
WHERE :v2 = 'NEXT'
  AND (
       cust_name > :v3 OR
       (cust_name = :v3 AND rowid > CHARTOROWID(:v4))
      )
  AND cust_name LIKE :v1 || '%'
  AND rownum <= 25;

현재 페이지의 마지막 값(:v3, :v4)을 기준으로 다음 데이터를 추출한다. 그렇게 되면 인덱스를 순방향으로 탐색하게 되는데 조건절에 OR이 포함되므로 옵티마이저가 이를 적절히 처리할 수 있도록 힌트(USE_CONCAT)가 추가된다.

 

이전 페이지 요청 시 (:v2 = 'PREV')

SELECT /*+ use_concat index_desc(y cust_name_idx) */
...
WHERE :v2 = 'PREV'
  AND (
       cust_name < :v3 OR
       (cust_name = :v3 AND rowid < CHARTOROWID(:v4))
      )
  AND cust_name LIKE :v1 || '%'
  AND rownum <= 25
ORDER BY cust_name, rid;

이전 페이지 데이터를 역순으로 조회하기 위해 인덱스를 역방향(INDEX_DESC)으로 접근한다. 그 후 마지막에 다시 정렬해서 정방향 결과를 반환한다.

 

이 방식은 비고유 인덱스 환경에서도 페이지 단위의 정밀한 부분범위처리를 가능하게 해주는 기본적인 페이징 처리 기법이다. 

이후 설명될 다른 사례에서는 이 방식 위에 다양한 확장 방식이 응용될 수 있다.

나) 웹 게시판 부분범위처리 사례 2 (UNIQUE INDEX)

웹 게시판에서 게시글을 시간 순으로 조회하거나 그 반대로 이전 게시글을 조회하는 경우, 전체 데이터를 처리하지 않고 일부 범위만 효율적으로 조회할 수 있는 방법이 필요하다. 이러한 상황을 고려하여, 기본적으로 게시글은 최근 작성된 순서대로 화면에 출력되며, 사용자가 ‘다음’이나 ‘이전’ 버튼을 클릭할 경우 해당 방향으로 25건씩 데이터를 부분 추출하는 방식이 적용된다.

 

이 방식에서 중요한 점은 게시글의 기본 키가 게시판 ID(BBS_ID), 작성일자, 글번호로 구성된 복합 유니크 인덱스이며, 이를 기반으로 정렬 기준을 설정할 수 있다는 점이다. 처음 페이지 혹은 다음 페이지를 조회할 경우에는 작성일자 기준으로 최신순으로 정렬되어야 하며, 이전 페이지를 조회할 경우에는 반대로 과거순으로 데이터를 추출한 뒤 다시 정렬해 주어야 한다.

 

이를 SQL로 구성하면 다음과 같다.

SELECT BBS_ID, 작성일자, 글번호, RNUM
FROM (
  SELECT /*+ USE_CONCAT INDEX_DESC(a billboard.uk) */
         ROWNUM RNUM, BBS_ID, 작성일자, 글번호, 글내용
  FROM BILLBOARD a
  WHERE :SW = 'NEXT'
    AND BBS_ID = :BID
    AND (작성일자 > :INIT_DT 
         OR (작성일자 = :INIT_DT AND 글번호 > :V_NUM))
    AND ROWNUM <= 25

  UNION ALL

  SELECT /*+ USE_CONCAT INDEX_ASC(a billboard.uk) */
         (26 - ROWNUM) RNUM, BBS_ID, 작성일자, 글번호, 글내용
  FROM BILLBOARD a
  WHERE :SW = 'PREV'
    AND BBS_ID = :BID
    AND (작성일자 < :INIT_DT 
         OR (작성일자 = :INIT_DT AND 글번호 < :V_NUM))
    AND ROWNUM <= 25
)
ORDER BY RNUM;

위 쿼리는 두 개의 SELECT 문을 UNION ALL로 묶어 구성된다. 첫 번째 SELECT 문은 사용자가 ‘NEXT’를 요청했을 때 실행되며, 현재 기준 시점인 작성일자(:INIT_DT)와 글번호(:V_NUM) 이후의 데이터를 작성일자 내림차순으로 정렬해 상위 25건만 추출한다. 이 때 INDEX_DESC 힌트를 통해 인덱스를 역방향으로 사용하게 되고, 조건에 만족하는 레코드를 빠르게 액세스할 수 있도록 한다. 추출된 레코드는 그대로 출력된다.

 

두 번째 SELECT 문은 사용자가 ‘PREV’를 요청한 경우 실행되며, 이번에는 기준 시점 이전에 존재하는 데이터를 대상으로 한다. 이 때는 INDEX_ASC 힌트를 활용하여 인덱스를 정방향으로 조회한 후 작성일자와 글번호 조건을 기준으로 과거 데이터를 추출하게 된다. 추출된 결과는 화면에 출력할 순서를 유지하기 위해 ROWNUM을 26에서 빼서 정렬 기준인 RNUM을 생성하며, 마지막에 전체 결과를 ORDER BY RNUM 절을 통해 올바른 순서로 재정렬한다.

 

결과적으로 이 방식은 어떤 방향의 페이지 이동이든 간에 항상 정해진 범위 내에서 효율적으로 데이터를 액세스하며, 인덱스를 활용해 전체 범위 처리를 하지 않도록 유도한다. 특히 연속적인 페이지 이동이 발생하더라도 매번 전체 테이블을 스캔하는 것이 아니라 이전에 추출된 기준 값 이후 또는 이전의 레코드만 접근하게 되므로, 매우 효율적인 웹 게시판 페이지 처리 방식이라 할 수 있다.

라) 웹 게시판 부분범위처리 사례 4 (SET 단위 처리)

페이지 목록의 표현 방식과 실제 데이터 액세스를 분리해서 효율적으로 처리하기 위한 전략이다. 이 방법은 전체 글 수가 많고, 페이지 단위로 세분화된 데이터에 대해 ‘이전’, ‘다음’, 또는 특정 세트 단위로 이동해야 할 때 유용하다.

 

예를 들어 전체 게시글이 341건이고, 한 페이지에 20건씩, 총 10페이지씩 구성된 세트가 존재한다고 가정하면, 1세트는 200건으로 10페이지를 구성하고, 2세트는 나머지 141건이 된다. 각 페이지의 첫 번째 글 식별자만 알고 있으면, 그 글로부터 연속된 20건을 쉽게 추출할 수 있는 구조다. 이런 방식은 페이지 전환 시 전체 범위를 다시 읽지 않고, 이전에 읽었던 마지막 글 이후 또는 이전 글 기준으로 빠르게 이동할 수 있는 장점을 가진다.

 

예를 들어 ‘다음 세트’로 이동 시, 아래와 같은 SQL이 사용된다.

SELECT rnum, bbs_id, cre_dt, num
FROM (
  SELECT /*+ USE_CONCAT INDEX_DESC(a bbs_idx1) */
         ROWNUM rnum, bbs_id, cre_dt, num
  FROM billboard a
  WHERE sw = 'NEXT'
    AND bbs_id = :v_bbs
    AND (cre_dt < :v_init_dt
         OR (cre_dt = :v_init_dt AND num <= :v_num))
    AND ROWNUM <= 201
  UNION ALL
  SELECT /*+ USE_CONCAT INDEX_ASC(a bbs_idx1) */
         ((20*10)+2 - ROWNUM) rnum, bbs_id, cre_dt, num
  FROM billboard a
  WHERE sw = 'PREV'
    AND bbs_id = :v_bbs
    AND (cre_dt > :v_init_dt
         OR (cre_dt = :v_init_dt AND num >= :v_num))
    AND ROWNUM <= 201
)
WHERE rnum IN (1, 21, 41, 61, 81, 101, 121, 141, 161, 181, 201)
ORDER BY rnum;

이 SQL은 ‘다음 세트’ 또는 ‘이전 세트’로 이동할 때 사용되며, 각 세트 내에서 페이지당 첫 번째 글(예: rnum이 1, 21, 41 등)을 기반으로 페이지 목록을 구성한다. INDEX_DESC는 최근 순서로 데이터를 뽑기 위해 사용되며, INDEX_ASC는 이전 세트 조회 시 역방향 정렬을 위해 사용된다. ROWNUM <= 201은 한 세트를 구성하는 최대 글 수를 포함하기 위한 조건이다.

 

실제로 이 방식은 대량의 게시글을 다루는 환경에서 불필요한 전체 범위 액세스를 피하고, 정해진 범위 내에서만 처리함으로써 성능을 극적으로 개선할 수 있다. 특히 시작점이 명확하게 정의되어 있고, 인덱스가 효과적으로 설계되어 있다면, 이전 세트/다음 세트 탐색뿐 아니라 특정 페이지로 직접 이동하는 경우에도 매우 빠르게 접근할 수 있다.

 

이와 같이 SET 단위 처리는 전체 데이터를 대상으로 하지 않고, 페이지 세트를 나누어 필요한 부분만 효율적으로 액세스할 수 있게 해주는 매우 효과적인 부분범위처리 방식 중 하나이다.

마) 웹 게시판 부분범위처리 사례 5 (계층구조의 처리) 

계층형 게시판에서 원글과 댓글, 대댓글이 복잡하게 얽힌 구조를 효율적으로 조회하는 방법을 다룬다. 일반적인 레벨별 구조라면 조인만으로도 단순히 처리할 수 있지만, 본 예제는 하위 계층까지 확장되는 복잡한 형태를 기준으로 한다. 특히 중요한 포인트는 하위 댓글들이 다음 페이지에 걸쳐있거나, 상위 글이 어느 위치에 있는지 모를 때 처리의 어려움이 발생한다는 점이다.

 

이를 해결하기 위해 계층구조의 전개(Explode)와 조상 찾기(Implode) 개념을 도입하며, SQL의 CONNECT BY 구문과 START WITH 절을 활용하여 계층적 트리를 순차적으로 탐색한다. 또한 SYS_CONNECT_BY_PATH를 이용하여 경로 추적 및 조상 파악이 가능하게 하며, 각 원글을 기준으로 게시글과 그 하위 댓글들을 하나의 세트로 묶어 처리한다.

SQL 예시는 다음과 같다.

SELECT id, pid, c_text, substr(path, 3, 3) * 1 parent_id
FROM (
    SELECT rownum rnum, id, pid, writer,
           lpad(' ', 2*level-1)||c_text c_text,
           sys_connect_by_path(to_char(id, '999'), '/') path
    FROM BILLBOARD
    CONNECT BY pid = PRIOR id
    AND rownum <= 10 + :cnt
    START WITH parent_sw = 1
    AND id >= :start_id
)
WHERE rnum BETWEEN :cnt AND 10 + :cnt
AND rownum <= 10;

이 쿼리는 계층 구조에서 특정 시작 글(start_id)부터 게시글을 탐색하며, 최대 10개의 항목만을 결과로 가져오는 방식이다. 핵심은 CONNECT BY를 통해 트리를 전개하면서도 rownum을 통해 부분 범위만 조회하게 제한한 점이다.

 

그리고 이전 페이지로 이동할 경우를 위해 다음과 같은 쿼리를 사용한다.

SELECT rownum as rnum, id, pid
FROM BILLBOARD a
WHERE sw = 1 AND id <= :start_id
AND rownum <= 11;

이 쿼리를 통해 현재 페이지에서의 역방향 탐색이 가능해지며, 이후 각 댓글 트리를 다시 전개해서 순서를 되살리는 방식이다. 단, 역방향 탐색은 다시 정렬이 필요하므로 배열에 담아 순서를 복구한다. 

 

예를 들어, 다음의 PL/SQL 코드에서

FOR j IN reverse 1..i LOOP
  cnt := cnt + 1;
  ret_id(112-cnt) := ret_id(j);
  ...
END LOOP;

위 코드는 추출한 결과를 역순으로 재정렬하여 배열에 다시 저장하고, 마지막엔 원하는 포맷으로 출력한다.

 

결론적으로 이 방법은 복잡한 계층 구조를 효율적으로 부분 범위 처리하는 데 초점을 두며, 필요한 정보만을 필터링하여 각 페이지 단위로 적절하게 나눌 수 있는 유용한 기법이다. 특히 댓글 수가 많거나 계층이 깊은 경우에도 일정한 성능을 유지하기 위한 전개와 정렬 전략이 핵심이다.

 

START: 25. 06. 04

END: 25. 07. 08