오라클 AS OF 절로 과거 데이터 조회하기

 

오라클 데이터베이스에서 AS OF 절은 플래시백 쿼리(Flashback Query) 기능의 핵심 요소로, 과거 시점의 데이터를 조회할 때 사용됩니다. 이는 데이터베이스에서 특정 시점 또는 SCN(System Change Number)을 기준으로 데이터를 복원하거나 조회하고자 할 때 매우 유용합니다.

이 글에서는 AS OF 절의 개념, 사용법, 그리고 실무에서 활용 가능한 세 가지 예시를 통해 과거 데이터 조회에 대해 알아보겠습니다.

---

1. AS OF 절의 개념

AS OF 절은 오라클 플래시백 기술의 일부로, 과거의 데이터를 UNDO 테이블스페이스를 활용해 조회할 수 있도록 합니다.

AS OF 절의 주요 특징

1. 시간 기반 조회: 특정 시각(TIMESTAMP) 기준으로 데이터를 조회합니다.
2. SCN 기반 조회: 오라클의 내부 변경 번호(SCN)를 기준으로 데이터를 조회합니다.
3. 데이터 복원 X: 데이터를 실제로 복원하지 않고 과거 데이터를 읽기 전용으로 확인할 수 있습니다.
4. 데이터 변경에 강점: 실수로 삭제되거나 수정된 데이터를 조회하여 복구의 근거로 활용할 수 있습니다.

---

2. AS OF 절 사용법

AS OF 절은 SELECT 문과 함께 사용되며, 과거 데이터 조회를 위해 TIMESTAMP 또는 SCN을 기준으로 설정합니다.

2.1 기본 문법

SELECT column_list  
FROM table_name  
AS OF TIMESTAMP TO_TIMESTAMP('YYYY-MM-DD HH24:MI:SS', 'YYYY-MM-DD HH24:MI:SS');  

2.2 SCN을 사용한 문법

SELECT column_list  
FROM table_name  
AS OF SCN scn_number;  

2.3 현재 SCN 조회

현재 SCN 번호는 다음과 같이 확인할 수 있습니다.

SELECT CURRENT_SCN FROM V$DATABASE;

---

3. AS OF 절 활용 예시

예시 1: 특정 시점의 데이터 조회

데이터가 잘못 수정되기 전 시점의 데이터를 확인하는 경우입니다.

-- 2024년 12월 1일 오후 2시 데이터를 조회
SELECT employee_id, first_name, salary  
FROM employees  
AS OF TIMESTAMP TO_TIMESTAMP('2024-12-01 14:00:00', 'YYYY-MM-DD HH24:MI:SS');  

이 쿼리는 employees 테이블에서 2024년 12월 1일 오후 2시 기준의 데이터를 반환합니다.

예시 2: SCN 기반 과거 데이터 확인

UNDO 테이블스페이스의 SCN 번호를 활용해 과거 데이터를 조회할 수 있습니다.

-- SCN 번호를 기준으로 과거 데이터를 조회
SELECT department_id, department_name  
FROM departments  
AS OF SCN 123456789;  

SCN 번호는 데이터 변경 시 오라클에서 자동으로 증가하므로, 특정 변경 이전의 데이터를 정확히 확인할 수 있습니다.

예시 3: 삭제된 데이터 복원 근거 확인

중요한 데이터가 실수로 삭제된 경우 AS OF 절로 삭제되기 전 데이터를 조회합니다.

-- TIMESTAMP 기준으로 삭제 전 데이터를 조회
SELECT *  
FROM orders  
AS OF TIMESTAMP TO_TIMESTAMP('2024-11-30 18:00:00', 'YYYY-MM-DD HH24:MI:SS');  

이 데이터를 활용해 복구 작업을 수행할 수 있습니다.

---

4. AS OF 절 사용 시 주의사항

1. UNDO 테이블스페이스 크기: AS OF 절은 UNDO 테이블스페이스를 활용하기 때문에, 테이블스페이스 설정에 따라 조회 가능한 시간 범위가 제한될 수 있습니다.
   • 예를 들어, UNDO 데이터 보존 기간이 15분으로 설정된 경우, 15분 이상 지난 데이터는 조회할 수 없습니다.
2. 플래시백 권한 필요: 사용자는 플래시백 쿼리를 실행하기 위해 FLASHBACK 관련 권한을 가져야 합니다.
3. GRANT FLASHBACK ANY TABLE TO user_name;
4. 성능: 과거 데이터를 조회할 때 UNDO 로그를 읽기 때문에 쿼리 성능이 다소 저하될 수 있습니다. 대량의 데이터를 조회할 때는 주의가 필요합니다.

---

5. AS OF 절과 플래시백 기술의 확장 활용

AS OF 절은 단순히 과거 데이터를 조회하는 데 그치지 않고, 플래시백 기술과 함께 데이터를 복원하거나 분석하는 데도 활용됩니다.

• 플래시백 테이블(Flashback Table): 테이블 데이터를 특정 시점으로 복원합니다.
• 플래시백 트랜잭션(Flashback Transaction): 특정 트랜잭션의 변경 사항을 롤백합니다.
• 플래시백 드롭(Flashback Drop): 삭제된 테이블을 복원합니다.

---

6. 결론

오라클의 AS OF 절은 데이터 무결성을 유지하고 과거 데이터를 조회하는 데 강력한 도구입니다. 실무에서는 데이터 손실이나 변경이 발생했을 때 빠르게 복구하거나, 문제의 원인을 파악하는 데 유용합니다.

AS OF 절을 제대로 이해하고 사용하면 데이터베이스 관리에서 신뢰성과 효율성을 높일 수 있습니다. 실시간 데이터를 활용하는 업무 환경에서는 AS OF 절을 통해 안정적이고 유연한 데이터 관리가 가능해질 것입니다.