[Oracle] Oracle 임시 테이블(Temporary Tables) 완벽 가이드

Oracle 데이터베이스에서 임시 테이블은 일시적인 데이터를 저장하고 관리하는데 매우 유용한 객체입니다. 이 글에서는 임시 테이블의 특징, 생성 방법, 활용 사례 등을 자세히 알아보겠습니다.

1. 임시 테이블이란?

임시 테이블은 세션 또는 트랜잭션 수준에서 데이터를 유지하는 특수한 형태의 테이블입니다. 테이블 정의는 모든 세션이 공유하지만, 데이터는 각 세션별로 독립적으로 관리됩니다.

1.1 주요 특징

세션별 독립적인 데이터 관리
세션 종료 시 자동 데이터 삭제
다른 세션의 데이터는 볼 수 없음
일반 테이블과 동일한 인덱스, 제약조건 사용 가능
TRUNCATE, DELETE 등의 DDL/DML 명령어 사용 가능

2. 임시 테이블 생성 방법

2.1 기본 문법

CREATE GLOBAL TEMPORARY TABLE 테이블명 (
    컬럼정의
) ON COMMIT [DELETE ROWS | PRESERVE ROWS];

2.2 ON COMMIT 옵션

DELETE ROWS
- 트랜잭션이 종료될 때 데이터 삭제
- 짧은 트랜잭션에서 임시 데이터 처리할 때 유용
PRESERVE ROWS
- 세션이 종료될 때까지 데이터 유지
- 세션 내에서 여러 트랜잭션에 걸쳐 데이터를 유지해야 할 때 사용

3. 실전 예제

3.1 기본 임시 테이블 생성

-- 트랜잭션 단위로 데이터가 삭제되는 임시 테이블
CREATE GLOBAL TEMPORARY TABLE temp_orders (
    order_id NUMBER,
    customer_id NUMBER,
    order_date DATE,
    total_amount NUMBER
) ON COMMIT DELETE ROWS;

-- 세션 종료시 데이터가 삭제되는 임시 테이블
CREATE GLOBAL TEMPORARY TABLE temp_order_items (
    item_id NUMBER,
    order_id NUMBER,
    product_id NUMBER,
    quantity NUMBER,
    unit_price NUMBER
) ON COMMIT PRESERVE ROWS;

3.2 활용 예제: 대량 데이터 처리

-- 임시 테이블을 사용한 대량 주문 처리
DECLARE
    v_batch_size NUMBER := 1000;
BEGIN
    -- 임시 테이블에 처리할 주문 데이터 저장
    INSERT INTO temp_orders
    SELECT order_id, customer_id, order_date, total_amount
    FROM orders
    WHERE process_flag = 'N'
    AND ROWNUM <= v_batch_size;

    -- 임시 테이블의 데이터 처리
    UPDATE orders o
    SET o.process_flag = 'Y'
    WHERE EXISTS (
        SELECT 1 
        FROM temp_orders t 
        WHERE t.order_id = o.order_id
    );

    COMMIT;
END;
/

3.3 인덱스 생성

-- 임시 테이블에 인덱스 생성
CREATE INDEX idx_temp_orders_01 
ON temp_orders (order_date, customer_id);

-- 임시 테이블에 기본키 제약조건 추가
ALTER TABLE temp_orders 
ADD CONSTRAINT pk_temp_orders PRIMARY KEY (order_id);

4. 활용 사례

4.1 복잡한 계산 결과 저장

대량 데이터의 중간 집계 결과 저장
다단계 처리가 필요한 배치 작업의 중간 결과 저장

4.2 데이터 정제 작업

ETL 과정에서 데이터 검증
중복 데이터 제거

4.3 성능 최적화

복잡한 조인 쿼리의 중간 결과 저장
반복적으로 사용되는 데이터 캐싱

5. 주의사항

메모리 사용
- 임시 테이블의 데이터는 TEMP 테이블스페이스를 사용
- 대량의 데이터 처리 시 TEMP 테이블스페이스 크기 고려 필요
트랜잭션 관리
- ON COMMIT DELETE ROWS 사용 시 COMMIT 시점 관리 중요
- 불필요한 데이터는 즉시 삭제하여 리소스 확보
세션 관리
- 세션 종료 전 중요 데이터 백업 필요
- 장시간 실행되는 세션의 경우 리소스 사용량 모니터링

6. 성능 최적화 팁

-- 병렬 처리를 위한 힌트 사용
INSERT /*+ APPEND PARALLEL(t,4) */ INTO temp_orders t
SELECT /*+ PARALLEL(o,4) */ *
FROM orders o
WHERE process_flag = 'N';

-- 효율적인 조인을 위한 임시 테이블 활용
CREATE GLOBAL TEMPORARY TABLE temp_summary
ON COMMIT PRESERVE ROWS
AS
SELECT /*+ PARALLEL(4) */
    customer_id,
    COUNT(*) as order_count,
    SUM(total_amount) as total_sales
FROM orders
GROUP BY customer_id;

-- 인덱스를 활용한 조회 성능 향상
CREATE INDEX idx_temp_summary
ON temp_summary(customer_id);

7. 모니터링 및 관리

임시 테이블의 효율적인 관리를 위해 다음 뷰들을 활용할 수 있습니다:

-- 임시 테이블 사용량 모니터링
SELECT *
FROM v$temp_space_header;

-- 세션별 임시 테이블 사용량
SELECT s.sid, s.serial#, s.username,
       t.blocks * TBS.block_size / 1024 / 1024 as "Size (MB)"
FROM v$session s,
     v$tempseg_usage t,
     dba_tablespaces TBS
WHERE s.saddr = t.session_addr
AND t.tablespace = TBS.tablespace_name;

임시 테이블은 Oracle 데이터베이스에서 세션 기반의 데이터 처리를 위한 강력한 도구입니다. 적절히 활용하면 복잡한 데이터 처리 작업을 효율적으로 수행할 수 있으며, 전체 애플리케이션의 성능을 향상시킬 수 있습니다. 다만, 리소스 사용량과 세션 관리에 주의를 기울여야 하며, 적절한 모니터링과 관리가 필요합니다.

저작자표시 비영리 변경금지

개발