이력 데이터 조회: 점이력·선분이력·기간 겹침과 Temporal Validity

핵심 요약

이력 데이터는 시간의 흐름에 따라 같은 업무 Key의 상태가 여러 행으로 저장되는 데이터입니다. 이력을 정확히 조회하려면 SQL 문법보다 먼저 어떤 시간을 표현하는지, 결과 한 행의 의미가 무엇인지, 기간의 시작과 종료를 포함하는지를 정해야 합니다.

대표적인 저장 방식은 다음 두 가지입니다.

점이력은 변경 기록을 추가하기 쉽지만 조회 시 최신 행을 선택해야 합니다. 선분이력은 특정 시점 조회가 직관적이지만 인접 기간의 경계와 기간 중복을 일관되게 관리해야 합니다.

학습 목표

이 이론을 학습한 뒤에는 다음을 설명할 수 있어야 합니다.

1. 점이력과 선분이력의 저장 방식과 조회 비용을 비교한다.
2. 업무 유효시간과 데이터베이스 기록시간을 구분한다.
3. 기준 시점 이전의 최신 점이력을 결정적인 순서로 조회한다.
4. 반개구간 [시작, 종료)을 이용해 선분이력의 경계를 처리한다.
5. 닫힌 구간과 반개구간에서 기간 겹침 조건이 달라지는 이유를 설명한다.
6. 기간이 겹치거나 비어 있는 데이터에서 중복·누락이 생기는 지점을 찾는다.
7. 단일 업무 Key 조회와 다수 Key 일괄 조회에 적합한 실행 방식을 비교한다.
8. 동시 변경 상황에서 이력 무결성을 지키는 기본 절차를 설명한다.

1. 이력 데이터에서 먼저 구분할 두 시간

이력 테이블에는 서로 다른 두 종류의 시간이 등장할 수 있습니다.

예를 들어 주소 변경을 7월 10일에 입력했지만 업무상 효력은 7월 1일부터라고 정할 수 있습니다.

업무 유효 시작: 2026-07-01
DB 기록 시각  : 2026-07-10 09:30:00

이때 “7월 5일의 유효 주소”를 묻는 Query와 “7월 5일에 Database에 저장되어 있던 주소”를 묻는 Query는 결과가 다를 수 있습니다. 이번 이론은 주로 업무 유효시간을 기준으로 한 조회를 다룹니다.

1.1 유효시간과 기록시간을 함께 보존하는 모델

업무상 소급 변경과 감사 요구가 모두 있으면 두 시간축을 함께 보존할 수 있습니다.

Valid Time
→ 그 사실이 업무 세계에서 언제 유효한가?

Transaction Time
→ Database가 그 사실을 언제 알고 있었는가?

예를 들어 7월 10일에 “7월 1일부터 주소가 변경되었다”고 소급 입력한 경우입니다.

VALID_FROM  = 2026-07-01
RECORDED_AT = 2026-07-10 09:30

질문도 두 가지로 나뉩니다.

7월 5일에 업무상 유효했던 주소
→ Valid Time Query

7월 5일 당시 Database가 알고 있던 주소
→ Transaction Time·Flashback Query

두 질문을 혼합하면 감사 결과가 달라질 수 있습니다. Oracle Temporal Validity는 업무 유효시간을, Flashback Query·Flashback Data Archive는 기록·Transaction Time 분석을 지원하는 기능으로 구분합니다.

2. 점이력과 선분이력

2.1 점이력

점이력은 상태가 바뀐 시점만 저장합니다.

CUSTOMER_ID | CHANGE_TIME        | STATUS | HISTORY_ID
------------+--------------------+--------+-----------
100         | 2026-01-01 09:00   | NORMAL | 1
100         | 2026-03-10 14:00   | HOLD   | 2
100         | 2026-04-05 11:30   | NORMAL | 3

각 행의 의미는 다음과 같습니다.

해당 CHANGE_TIME부터 다음 변경 직전까지 STATUS가 유지된다.

점이력의 장점은 기존 행의 종료 시점을 수정하지 않고 새 행을 추가할 수 있다는 점입니다. 반면 특정 시점의 상태를 조회하려면 기준 시점 이하의 행 중 가장 최신 행을 찾아야 합니다.

2.2 선분이력

선분이력은 상태가 유효한 시작과 종료를 함께 저장합니다.

CUSTOMER_ID | VALID_FROM         | VALID_TO           | STATUS
------------+--------------------+--------------------+--------
100         | 2026-01-01 09:00   | 2026-03-10 14:00   | NORMAL
100         | 2026-03-10 14:00   | 2026-04-05 11:30   | HOLD
100         | 2026-04-05 11:30   | NULL               | NORMAL

현재 행의 종료 시점을 NULL로 표현할 수 있습니다. 또는 업무 규칙에 따라 충분히 큰 종료일을 사용할 수도 있지만, 실제 가능한 업무 날짜와 충돌하지 않도록 정책을 명확히 해야 합니다.

2.3 두 모델의 비교

저장 방식은 조회 편의성만으로 정하지 않습니다. 변경 빈도, 소급 변경 여부, 동시성, 감사 요구, 현재 조회 비율을 함께 고려합니다.

점이력
→ 변경 Event 저장
→ 조회 시 다음 변경 시점 또는 최신 행 계산

선분이력
→ 유효 시작·종료 저장
→ 조회는 단순하지만 종료 갱신·겹침 무결성 관리 필요

이 이론의 범위

SQLP SQL 고급 활용 및 튜닝 → 고급 SQL 활용 → 이력 조회 범위에서 업무 유효시간·기록시간, 점이력 최신 행, 선분이력 시점 조회, 반개구간 겹침, 기간 Join, Oracle Temporal Validity와 실행계획 진단을 다룹니다.

3. 점이력에서 기준 시점의 최신 행 찾기

3.1 단일 업무 Key 조회

고객 100의 :as_of 시점 상태를 찾는 SQL입니다.

SELECT h.customer_id,
       h.change_time,
       h.status,
       h.history_id
FROM   customer_status_history h
WHERE  h.customer_id = :customer_id
AND    h.change_time <= :as_of
ORDER  BY h.change_time DESC,
          h.history_id DESC
FETCH FIRST 1 ROW ONLY;

처리 의미는 다음과 같습니다.

1. 같은 고객의 이력만 찾음
2. 기준 시점 이후 이력 제거
3. 변경 시각이 가장 늦은 행을 앞에 배치
4. 같은 변경 시각이면 HISTORY_ID로 순서 확정
5. 첫 행만 반환

CHANGE_TIME만 정렬하면 같은 시각의 행이 두 개 이상일 때 어느 행이 선택될지 불안정할 수 있습니다. HISTORY_ID처럼 유일한 Tie-Breaker를 포함해 전체 순서를 확정합니다.

다음 형태의 Index가 단일 Key 최신 조회에 유리할 수 있습니다.

CREATE INDEX customer_status_h_ix
ON customer_status_history
   (customer_id, change_time DESC, history_id DESC);

Oracle은 기존 Ascending Index를 역방향으로 읽는 실행계획도 선택할 수 있으므로 DESC 지정 자체보다 다음 조건이 더 중요합니다.

• 업무 Key가 선두에서 등치로 고정되는가?
• 기준 시점 조건으로 탐색 범위를 만들 수 있는가?
• 정렬 Key와 Tie-Breaker가 Index 순서에 연결되는가?
• 첫 행에서 중단하는 Stopkey가 적용되는가?

실행계획에서는 INDEX RANGE SCAN DESCENDING, COUNT STOPKEY, WINDOW NOSORT STOPKEY, SORT ORDER BY STOPKEY 등의 Operation과 실제 A-Rows, Buffers를 확인합니다.

3.2 기준 시점 조건의 위치

다음 순서를 지켜야 합니다.

기준 시점 이하 행을 먼저 제한
→ 그 안에서 최신 순위를 계산

SELECT customer_id,
       change_time,
       status,
       history_id
FROM (
    SELECT h.*,
           ROW_NUMBER() OVER (
               PARTITION BY h.customer_id
               ORDER BY h.change_time DESC NULLS LAST,
                        h.history_id DESC
           ) AS rn
    FROM   customer_status_history h
    WHERE  h.change_time <= :as_of
)
WHERE rn = 1;

전체 이력에서 최신 행을 먼저 선택한 뒤 바깥에서 change_time <= :as_of를 적용하면, 최신 행이 기준 시점 이후인 고객이 결과에서 사라질 수 있습니다.

3.3 점이력을 선분 형태로 해석하기

점이력의 다음 변경 시점을 LEAD로 구하면 조회용 유효 구간을 만들 수 있습니다.

SELECT customer_id,
       change_time AS valid_from,
       LEAD(change_time) OVER (
           PARTITION BY customer_id
           ORDER BY change_time, history_id
       ) AS valid_to,
       status,
       history_id
FROM customer_status_history;

해석은 다음과 같습니다.

현재 CHANGE_TIME
→ VALID_FROM

같은 Key의 다음 CHANGE_TIME
→ VALID_TO

다음 행 없음
→ VALID_TO NULL

이 결과를 Inline View·View로 사용하면 점이력도 선분 조건으로 조회할 수 있습니다.

SELECT *
FROM (
    SELECT h.*,
           LEAD(change_time) OVER (
               PARTITION BY customer_id
               ORDER BY change_time, history_id
           ) AS valid_to
    FROM customer_status_history h
)
WHERE customer_id = :customer_id
  AND change_time <= :as_of
  AND (valid_to > :as_of OR valid_to IS NULL);

주의할 점입니다.

• 같은 변경 시각의 순서를 HISTORY_ID 등으로 확정한다.
• 기준 시점 필터와 전체 이력 연결에 필요한 이전·다음 행 범위를 고려한다.
• 매 Query마다 Window 계산이 비싸면 Materialized View·직접 선분 모델과 비교한다.
• 소급 변경이 들어오면 이후 파생 구간이 달라질 수 있다.

4. 선분이력의 특정 시점 조회

기간 경계는 가능한 한 반개구간으로 통일하는 것이 좋습니다.

[VALID_FROM, VALID_TO)
시작은 포함하고 종료는 포함하지 않음

현재 행의 VALID_TO가 NULL인 모델은 다음처럼 조회합니다.

SELECT h.customer_id,
       h.valid_from,
       h.valid_to,
       h.status
FROM   customer_status_period h
WHERE  h.customer_id = :customer_id
AND    h.valid_from <= :as_of
AND   (h.valid_to > :as_of OR h.valid_to IS NULL);

같은 조건을 읽기 쉬운 형태로 쓰면 다음과 같습니다.

VALID_FROM <= 기준 시점
그리고
기준 시점 < VALID_TO 또는 종료 없음

반개구간의 한 행은 다음 논리로 정의합니다.

[VALID_FROM, VALID_TO)

VALID_FROM 포함
VALID_TO 제외

따라서 새 상태가 이전 상태의 VALID_TO와 같은 시각에 시작해도 두 상태가 동시에 유효하지 않습니다.

열린 시작·종료와 데이터 타입

업무 모델에서 VALID_FROM IS NULL을 과거 무한대, VALID_TO IS NULL을 미래 무한대로 사용할 수 있습니다. 직접 SQL 모델에서는 NULL 의미를 명시적으로 정의해야 합니다.

WHERE (:as_of >= valid_from OR valid_from IS NULL)
  AND (:as_of <  valid_to   OR valid_to   IS NULL)

Oracle Temporal Validity의 PERIOD FOR는 다음 의미를 사용합니다.

START NULL, END 값
→ END 전의 모든 시각에 유효

START 값, END NULL
→ START 이후의 모든 시각에 유효

START NULL, END NULL
→ 모든 시각에 유효

START·END 값
→ START 포함, END 제외

시간 Column의 타입과 정밀도도 맞춰야 합니다.

화면에서 날짜만 보인다고 실제 Column의 시·분·초가 없는 것은 아닙니다. Bind Type을 Column과 맞추고, 문자열의 묵시적 변환과 Session NLS에 의존하지 않습니다.

반개구간의 장점

다음 두 기간이 있다고 가정합니다.

A: [2026-01-01, 2026-02-01)
B: [2026-02-01, 2026-03-01)

2026-02-01은 B에만 포함됩니다. 인접 기간이 같은 경계를 공유해도 두 행이 동시에 유효해지지 않습니다.

날짜만 저장하는지, 시각까지 저장하는지도 중요합니다. DATE Column에 시각이 포함될 수 있는데 화면에서 날짜만 보인다고 BETWEEN을 사용하면 하루의 일부가 누락될 수 있습니다.

-- 7월 전체를 조회하는 반개구간
WHERE event_time >= DATE '2026-07-01'
AND   event_time <  DATE '2026-08-01'

5. 두 기간의 겹침 조건

5.1 닫힌 구간

두 기간이 모두 시작과 종료를 포함하는 [시작, 종료]라면 겹침 조건은 다음과 같습니다.

a.valid_from <= b.valid_to
AND b.valid_from <= a.valid_to

다음 두 기간은 경계 하루를 공유하므로 겹칩니다.

A: [1월 1일, 1월 31일]
B: [1월 31일, 2월 28일]

5.2 반개구간

두 기간이 [시작, 종료)라면 비교 연산자는 <를 사용합니다.

a.valid_from < b.valid_to
AND b.valid_from < a.valid_to

A: [1월 1일, 2월 1일)
B: [2월 1일, 3월 1일)

위 두 기간은 접하지만 겹치지 않습니다.

5.3 열린 시작·종료가 있는 기간

VALID_TO IS NULL을 미래 무한대로 해석한다면 비교 전에 같은 의미의 값으로 정규화할 수 있습니다.

ON  a.business_key = b.business_key
AND a.valid_from < NVL(b.valid_to, DATE '9999-12-31')
AND b.valid_from < NVL(a.valid_to, DATE '9999-12-31')

DATE '9999-12-31'가 실제 업무 데이터로 사용되지 않는다는 규칙이 필요합니다. Column이 TIMESTAMP라면 DATE Literal로 소수초를 잃거나 묵시적 변환을 만들지 않도록 타입을 맞춥니다.

TIMESTAMP '9999-12-31 23:59:59.999999'

Sentinel을 사용하면 다음 영향도 검토합니다.

• 종료일 분포가 하나의 큰 값에 집중되어 Histogram·Cardinality가 달라질 수 있음
• 실제 업무 최대일과 충돌할 수 있음
• NVL(valid_to, sentinel)이 일반 Index와 다른 표현식이 됨
• Function-Based Index·Virtual Column이 필요할 수 있음

NULL을 직접 처리하면 다음처럼 작성할 수 있습니다.

ON  a.business_key = b.business_key
AND (b.valid_to IS NULL OR a.valid_from < b.valid_to)
AND (a.valid_to IS NULL OR b.valid_from < a.valid_to)

겹친 실제 구간은 다음처럼 계산할 수 있습니다.

GREATEST(a.valid_from, b.valid_from) AS overlap_from,
LEAST(
    NVL(a.valid_to, DATE '9999-12-31'),
    NVL(b.valid_to, DATE '9999-12-31')
) AS overlap_to

Oracle에서 GREATEST나 LEAST의 인수에 NULL이 들어가면 결과도 NULL이 되므로 열린 시작·종료를 먼저 정규화합니다.

GREATEST(
    NVL(a.valid_from, TIMESTAMP '0001-01-01 00:00:00'),
    NVL(b.valid_from, TIMESTAMP '0001-01-01 00:00:00')
) AS overlap_from,
LEAST(
    NVL(a.valid_to, TIMESTAMP '9999-12-31 23:59:59.999999'),
    NVL(b.valid_to, TIMESTAMP '9999-12-31 23:59:59.999999')
) AS overlap_to

반개구간에서 실제 겹침 길이가 존재하는지 마지막으로 확인합니다.

overlap_from < overlap_to

두 기간이 경계에서 접하기만 하면 두 값이 같으므로 겹침 길이는 0입니다.

6. 기간 Join의 행 증가 이해

같은 업무 Key에 기간이 여러 개 존재하면 기간 Join은 등치 Join보다 많은 후보 조합을 만들 수 있습니다.

SELECT a.customer_id,
       a.grade,
       b.discount_rate,
       GREATEST(a.valid_from, b.valid_from) AS overlap_from,
       LEAST(a.valid_to, b.valid_to)        AS overlap_to
FROM   customer_grade_history a
JOIN   discount_history b
  ON   b.customer_id = a.customer_id
 AND   a.valid_from < b.valid_to
 AND   b.valid_from < a.valid_to;

실행 구조는 다음처럼 이해합니다.

CUSTOMER_ID로 후보 집합 결합
→ 각 후보 쌍의 기간 겹침 검사
→ 실제 겹치는 조합만 반환

Hash Join이 선택되면 CUSTOMER_ID 같은 등치 조건이 Hash Key가 되고 기간 조건은 추가 Filter로 평가될 수 있습니다. Key별 이력 행이 많으면 후보 조합과 CPU 비용이 크게 증가할 수 있습니다.

다음 수치를 확인합니다.

• 각 입력에서 조건 적용 후 A-Rows
• Join 결과의 A-Rows
• Key별 평균·최대 이력 행 수
• 기간 Filter에서 제거되는 행 수
• Buffers, CPU, TEMP 사용량
• 특정 Key에 이력이 집중되는 Data Skew

6.1 기간 Join의 Access와 Filter

기간 겹침에는 보통 다음 두 종류의 조건이 함께 있습니다.

등치 조건
→ BUSINESS_KEY

범위 조건
→ START < END

Hash Join에서는 업무 Key를 Hash Key로 사용하고 기간 조건을 Join Filter로 평가할 수 있습니다. 한 Key에 양쪽 이력이 각각 m, n건이면 최악의 후보 조합은 m × n에 가까워질 수 있습니다.

한 Key
A 이력 100건
B 이력 200건

등치 후보
→ 최대 20,000쌍

기간 Filter
→ 실제 겹침 Row만 남김

선필터가 중요합니다.

WHERE a.valid_from < :query_to
  AND (a.valid_to > :query_from OR a.valid_to IS NULL)

Query 대상 기간과 전혀 겹칠 수 없는 이력을 먼저 제거한 뒤 두 이력 Table을 Join합니다.

6.2 대표 Index 방향

단건 시점 조회 후보입니다.

CREATE INDEX history_point_ix
ON history_table(business_key, valid_from DESC, history_id DESC);

선분 시점 조회 후보입니다.

CREATE INDEX history_period_ix
ON history_table(business_key, valid_from, valid_to);

그러나 valid_from <= :as_of가 Range 조건이 되면 후행 valid_to가 좁은 Start·Stop Key가 아니라 Filter로 남을 수 있습니다. Index가 있다고 기간 조건 전체가 Access Predicate가 되는 것은 아닙니다.

열린 종료를 자주 조회한다면 다음 대안을 검토할 수 있습니다.

CREATE INDEX history_period_end_ix
ON history_table(
    business_key,
    NVL(valid_to, DATE '9999-12-31'),
    valid_from
);

Query 표현식과 Index 표현식, 데이터 타입, DML 비용을 일치시켜 검증합니다.

7. 단건 조회와 일괄 조회의 전략 차이

소수 Key에 대해 최신 행을 찾을 때는 반복 Index Probe가 적고 첫 행 응답이 빠를 수 있습니다. 전체 고객을 한 번에 조회할 때는 Key마다 Index를 반복 탐색하는 방식과 전체 이력을 한 번 읽어 Window·Group 처리하는 방식을 비교해야 합니다.

소수 Key
→ 반복 횟수가 적으므로 Index Stopkey가 유리할 가능성

다수 Key
→ 반복 Probe 총량이 커질 수 있으므로 일괄 Window·Group 방식 비교

SQL 모양만으로 결론을 고정하지 않고 동일한 기준 시점과 전체 Fetch 조건에서 실제 작업량을 측정합니다.

8. 이력 무결성과 동시성

점이력

점이력에서는 같은 업무 Key와 같은 변경 순서를 중복 저장하지 않도록 유일성을 정의합니다.

ALTER TABLE customer_status_history
ADD CONSTRAINT customer_status_h_uq
UNIQUE (customer_id, change_time, history_id);

HISTORY_ID가 전역 Primary Key라면 업무적으로 같은 시각에 여러 이력을 허용할지 별도의 규칙을 정해야 합니다.

선분이력

선분이력에서 다음 오류를 관리해야 합니다.

• VALID_FROM >= VALID_TO
• 같은 Key의 기간 겹침
• 의도하지 않은 빈 구간
• 현재 행이 두 개 이상 존재
• 이전 행 종료와 새 행 입력 사이의 동시 변경 충돌

기간 겹침은 일반적인 단일 행 CHECK Constraint만으로 다른 행과 비교해 차단하기 어렵습니다. 같은 업무 Key의 현재 행을 잠그고, 기존 기간을 확인하고, 종료·신규 입력을 하나의 Transaction으로 처리하는 절차를 설계합니다.

1. 같은 업무 Key의 기준 행 잠금
2. 새 기간과 겹치는 기존 행 확인
3. 이전 기간 종료 또는 기간 분할
4. 새 이력 입력
5. 중복·빈 구간 검증
6. Commit

동시성 제어는 이후의 Lock과 동시성 제어 이론에서 더 자세히 학습합니다.

9. Oracle Temporal Validity 기능과 직접 기간 모델

Oracle은 PERIOD FOR, AS OF PERIOD FOR, VERSIONS PERIOD FOR를 이용해 유효시간을 표현하는 기능을 제공합니다.

CREATE TABLE policy_history (
    policy_id   NUMBER,
    status      VARCHAR2(20),
    valid_from  TIMESTAMP,
    valid_to    TIMESTAMP,
    PERIOD FOR policy_valid_time (valid_from, valid_to)
);

특정 유효 시점 조회입니다.

SELECT *
FROM policy_history
AS OF PERIOD FOR policy_valid_time :as_of
WHERE policy_id = :policy_id;

특정 유효 기간과 관련된 Version 조회입니다.

SELECT *
FROM policy_history
VERSIONS PERIOD FOR policy_valid_time
BETWEEN :from_time AND :to_time
WHERE policy_id = :policy_id;

AS OF PERIOD FOR의 시점 표현식과 VERSIONS PERIOD FOR ... BETWEEN의 두 경계는 Timestamp 값이어야 하고 NULL일 수 없습니다.

Temporal Validity의 Start·End Column은 DATE·TIMESTAMP 계열이어야 하며 시작은 포함, 종료는 제외됩니다. 시작 또는 종료가 NULL인 열린 기간도 공식적으로 지원합니다.

직접 VALID_FROM, VALID_TO 조건을 작성하는 모델과 목적은 비슷하지만, 도입 전에는 다음을 확인해야 합니다.

• 사용 중인 Oracle Version과 기능 지원 범위
• 기존 SQL·Framework와의 호환성
• 기간 종료 NULL 처리 규칙
• Partitioning·Index·통계 수집 방식
• Flashback의 기록시간과 유효시간을 함께 사용할지 여부
• 자동 생성되는 Invisible Start·End Column을 사용할지 직접 Column을 지정할지 여부
• AS OF PERIOD FOR Expression의 Timestamp 타입과 NULL 금지
• 기존 직접 조건과 Temporal Clause의 결과·Plan 차이

Temporal Validity는 Valid Time을 표현합니다. 일반 AS OF SCN·AS OF TIMESTAMP Flashback은 Database가 과거에 보았던 Transaction Time을 조회합니다. 두 Clause를 함께 사용하면 “특정 기록 시점에서 보이는 특정 유효 시점의 상태”처럼 두 시간축을 분석할 수 있습니다.

기능 사용 여부와 관계없이 업무 유효시간과 기록시간을 구분하고 기간 경계를 명시하는 설계 원칙은 동일합니다.

10. 실행계획과 결과를 검증하는 순서

1. 결과 한 행의 Grain을 정의한다.
2. 업무 Key와 이력 순서를 확정한다.
3. 기준 시점과 기간 경계의 포함 여부를 정한다.
4. 0건·1건·동률·겹침·빈 구간 데이터를 만든다.
5. 작은 데이터에서 결과를 먼저 검증한다.
6. 실제 실행계획의 Starts·A-Rows·Buffers·CPU·TEMP를 확인한다.
7. Index Predicate의 access·filter와 Stopkey 적용을 구분한다.
8. 기간 Join 전 입력 Row와 등치 후보·최종 겹침 Row를 비교한다.
9. 단건과 일괄 처리의 첫 행·전체 Fetch 비용을 비교한다.
10. 동시 변경에서도 무결성이 유지되는지 시험한다.

혼동하기 쉬운 판단