대용량 데이터 추출: 청킹, keyset 페이지네이션, ORA-01555 회피
개요
접속이 확립되면 소스에서 데이터를 끌어와야 한다. 수백만~수천만 행 규모의 records·documents 테이블을 단일 SELECT *로 한 번에 읽으려는 순간, 대개 ORA-01555: snapshot too old 앞에서 무너진다. 대용량 추출의 핵심은 “한 번에 다 읽지 않는 것”, 즉 읽기를 잘게 쪼개는 것(청킹) 이다.
이 문서는 왜 긴 쿼리가 실패하는지(undo 메커니즘), 어떻게 쪼개야 안정적인지(keyset 페이지네이션), 그리고 CLOB·카탈로그 권한 같은 폐쇄망 특유의 걸림돌을 다룬다.
핵심 개념·원리
ORA-01555는 왜 발생하나
Oracle은 읽기 일관성(read consistency)을 위해 쿼리가 시작된 시점의 스냅샷을 보장한다. 다른 세션이 데이터를 변경하면 이전 값을 undo 세그먼트에 보관하고, 오래 도는 쿼리는 필요 시 undo에서 옛 값을 복원해 읽는다. 문제는 undo 보존 시간(UNDO_RETENTION)과 크기가 유한하다는 점이다. 벌크 SELECT가 너무 오래 돌면, 복원해야 할 undo 블록이 이미 다른 트랜잭션에 의해 덮어써져 사라진다. 그 순간 Oracle은 일관된 스냅샷을 재구성할 수 없어 ORA-01555를 던진다.
즉 ORA-01555는 “쿼리가 undo가 버틸 수 있는 시간보다 오래 돌았다”는 신호다. undo를 키우는 것은 DBA 협조가 필요하고 근본 해법도 아니다. 근본 해법은 각 쿼리를 짧게 유지하는 것이다.
청킹 전략: 날짜·파티션 vs keyset
| 방식 | 방법 | 장점 | 단점 |
|---|---|---|---|
| 날짜/파티션 청킹 | 생성일·파티션 키 범위로 분할 | 파티션 프루닝 활용 | 데이터 편중 시 청크 크기 불균등 |
| OFFSET 페이지네이션 | OFFSET n ROWS FETCH NEXT m | 구현 단순 | 뒤로 갈수록 급격히 느려짐 |
| keyset(seek) 페이지네이션 | WHERE id > :last ORDER BY id | 인덱스로 O(log n) 시크, 일정 속도 | 정렬 가능한 유니크 키 필요 |
OFFSET이 나쁜 이유는 명확하다. OFFSET 1000000은 앞의 100만 행을 읽고 버린 뒤 다음 페이지를 반환한다. 페이지가 뒤로 갈수록 버리는 행이 늘어 O(n²)에 가깝게 느려지고, 그만큼 각 쿼리가 오래 돌아 ORA-01555 위험도 커진다. keyset은 마지막으로 읽은 키 이후만 인덱스로 시크하므로 첫 페이지든 백만 번째 페이지든 속도가 일정하다.
실전
keyset 페이지네이션
정렬 가능한 유니크 키(id)를 커서로 삼아, 마지막 키 이후부터 고정 크기로 읽는다.
-- OFFSET 대신 마지막 키 이후부터: 인덱스 시크, 일정 속도
SELECT *
FROM records
WHERE id > :last_id
ORDER BY id
FETCH FIRST 50000 ROWS ONLY;파이썬 추출 루프는 마지막 키를 이월하며 반복한다.
def extract_chunk(cur, after: int | None, size: int = 50_000):
cur.execute(
"""
SELECT id, record_no, body, updated_at
FROM records
WHERE (:after IS NULL OR id > :after)
ORDER BY id
FETCH FIRST :size ROWS ONLY
""",
after=after, size=size,
)
rows = cur.fetchall()
return rows, (rows[-1][0] if rows else after) # 다음 커서 = 마지막 id각 청크가 짧게 끝나므로 undo 부담이 없고, 중단되어도 마지막 id부터 재개할 수 있어 멱등성과도 잘 맞는다(→ 05.적재 (멱등 upsert·COPY·자연키)).
CLOB 성능
documents.body 같은 CLOB 컬럼을 매 행 전량 가져오면 LOB 로케이터 왕복 때문에 추출이 급격히 느려진다. 미리보기·해시 용도라면 필요한 앞부분만 잘라 온다.
-- CLOB 전체 대신 앞 4000바이트만: LOB 왕복 최소화
SELECT id, DBMS_LOB.SUBSTR(body, 4000, 1) AS body_head
FROM documents;전량이 필요하면 CLOB만 별도 배치로, 나머지 스칼라 컬럼과 분리해 끌어오는 것이 처리량에 유리하다. CLOB의 내용 정제(NUL 문자 등)는 다음 단계에서 다룬다(→ 04.데이터 정제 (NUL 문자·인코딩·암호화 컬럼)).
진행 상황 로깅
긴 추출은 어디까지 진행됐는지 보여야 재개·모니터링이 된다.
total = 0
after = None
while True:
rows, after = extract_chunk(cur, after)
if not rows:
break
total += len(rows)
print(f"extracted {total:,} rows, cursor={after}")함정·트러블슈팅
| 증상 | 원인 | 해법 |
|---|---|---|
ORA-01555 snapshot too old | 벌크 SELECT가 undo 초과 | keyset 청킹으로 각 쿼리를 짧게 |
| 페이지가 뒤로 갈수록 느려짐 | OFFSET 페이지네이션 | keyset(seek)로 전환 |
| 추출이 CLOB 테이블에서 급감 | LOB 로케이터 왕복 | DBMS_LOB.SUBSTR로 부분만, 또는 CLOB 분리 배치 |
ORA-00942 table or view does not exist로 v$version 조회 실패 | 최소권한 계정이 시스템 뷰 미접근 | ”테이블이 없다”고 단정 말고 DBA에게 버전·권한 확인 |
| 청크 경계에서 행 누락/중복 | 커서 키가 유니크하지 않거나 정렬 불안정 | 유니크+정렬 가능한 키 사용, ORDER BY 고정 |
카탈로그 권한의 현실
폐쇄망 최소권한 계정은 v$version, dba_tables 같은 시스템 뷰를 못 읽는 경우가 흔하다. 조회가 ORA-00942로 실패한다고 해서 “그런 테이블/데이터가 없다”고 단정하면 안 된다. 실제로는 권한이 없어 안 보이는 것뿐이다. 서버 버전, 파티션 구성, 대상 테이블 목록 같은 메타데이터는 추정하지 말고 DBA에게 확인받는다. 이 확인은 02.접속 확립 (thin·thick·libaio·NLS)에서 thin/thick을 결정할 때도 필요하다.
정리
대용량 추출의 원리는 하나다. “긴 쿼리는 undo 앞에서 죽으니, 짧은 쿼리로 쪼갠다.” OFFSET을 버리고 keyset 페이지네이션으로 일정 속도·재개 가능성을 확보하고, CLOB은 부분만 끌거나 분리하며, 안 보이는 시스템 뷰는 권한 문제로 의심한다. 이렇게 안전하게 끌어온 원시 데이터를 다음 단계에서 정제한다.