시간적 유효성 지원 (Temporal Validity Support) 활용

시간적 유효성 지원(Temporal Validity Support)이란?

오라클 데이터베이스에서 시간적 유효성 지원은 데이터의 유효 기간을 명시적으로 관리할 수 있도록 해주는 기능입니다. 이는 특정 시점의 데이터 상태를 조회하거나, 데이터 변경 이력을 추적하고 감사하는 데 유용합니다. 예를 들어, 인사 시스템에서 직원의 고용 시작일과 종료일을 관리하거나, 제품 가격의 변경 이력을 관리하는 데 사용할 수 있습니다.

시간적 유효성 지원의 주요 개념

  • 유효 기간 열 (Validity Period Columns): 데이터의 유효 기간을 나타내는 시작 날짜와 종료 날짜를 저장하는 열입니다.
  • AS OF 절 (AS OF Clause): 특정 시점의 데이터 상태를 조회하는 데 사용되는 SQL 절입니다.
  • 버전 쿼리 (Version Query): 특정 기간 동안의 데이터 변경 이력을 조회하는 쿼리입니다.
  • 플래시백 아카이브 (Flashback Archive): 데이터의 장기적인 변경 이력을 저장하고 관리하는 데 사용되는 저장소입니다.

시간적 유효성 지원을 활용한 예시

다음은 인사 시스템에서 직원의 고용 시작일과 종료일을 관리하는 예시입니다.

  1. 테이블 생성: 직원 테이블에 hire_date와 termination_date 열을 추가하여 고용 기간을 관리합니다. 
    
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    employee_name VARCHAR2(100),
    hire_date DATE,
    termination_date DATE
);
  2. 데이터 삽입: 직원의 고용 시작일을 hire_date 열에 저장합니다. 현재 재직 중인 직원의 경우 termination_date는 NULL로 설정합니다. 
    
INSERT INTO employees (employee_id, employee_name, hire_date, termination_date)
VALUES (1, '홍길동', DATE '2020-01-01', NULL);
  3. 퇴사 처리: 직원이 퇴사하는 경우 termination_date 열을 퇴사일로 업데이트합니다. 
    
UPDATE employees
SET termination_date = DATE '2023-12-31'
WHERE employee_id = 1;
  4. 특정 시점의 데이터 조회 (AS OF): 2022년 1월 1일 당시의 직원 목록을 조회하려면 AS OF 절을 사용합니다. 
    
SELECT employee_id, employee_name
FROM employees AS OF TIMESTAMP DATE '2022-01-01 00:00:00'
WHERE termination_date IS NULL;
  5. 특정 기간의 데이터 조회 (Version Query): 2020년 이후 퇴사한 직원들의 목록과 퇴사일을 조회합니다. 
    
SELECT employee_id, employee_name, hire_date, termination_date
FROM employees
WHERE termination_date BETWEEN DATE '2020-01-01' AND SYSDATE;
  6. 플래시백 아카이브 설정 (Flashback Archive): 장기적인 데이터 보존을 위해 플래시백 아카이브를 설정합니다. 먼저 테이블스페이스를 생성합니다. 
    
CREATE TABLESPACE flashback_ts DATAFILE 'flashback_ts.dbf' SIZE 100M AUTOEXTEND ON;
    그 후 플래시백 아카이브를 생성합니다. 
    
CREATE FLASHBACK ARCHIVE fla_archive TABLESPACE flashback_ts RETENTION 10 YEAR;
    마지막으로, 플래시백 아카이브를 해당 테이블에 적용합니다. 
    
ALTER TABLE employees ADD FLASHBACK ARCHIVE fla_archive;

더욱 심화된 예시

제품 가격 변경 이력 관리:


CREATE TABLE product_prices (
    product_id NUMBER,
    price NUMBER,
    valid_from DATE,
    valid_to DATE
);

INSERT INTO product_prices (product_id, price, valid_from, valid_to)
VALUES (100, 99.99, DATE '2023-01-01', DATE '2023-06-30');

INSERT INTO product_prices (product_id, price, valid_from, valid_to)
VALUES (100, 109.99, DATE '2023-07-01', DATE '9999-12-31');

SELECT product_id, price
FROM product_prices
WHERE product_id = 100
AND DATE '2023-05-15' BETWEEN valid_from AND valid_to;

시간적 유효성 지원 활용 시 주의사항

  • 유효 기간 열의 데이터 타입은 DATE 또는 TIMESTAMP를 사용하는 것이 좋습니다.
  • AS OF 절을 사용할 때는 성능에 영향을 미칠 수 있으므로, 인덱스를 적절히 활용하는 것이 중요합니다.
  • 플래시백 아카이브를 설정할 때는 충분한 저장 공간을 확보해야 합니다.

결론

시간적 유효성 지원은 데이터의 과거 상태를 보존하고, 변경 이력을 추적하는 데 매우 유용한 기능입니다. 이 기능을 통해 감사, 보고, 복구 등 다양한 시나리오에서 활용 가능하며, 데이터 관리의 효율성을 높일 수 있습니다.

위로 스크롤