일요일 분석용 사본 재적재는 성공으로 끝났는데 월요일 아침 0건이다

중급PLSQL유통463 / 700

상황

본사 레거시 유통은 매주 일요일 01:10에 판매상품 CF11을 분석용 사본 CF11_DW로 통째로 다시 옮긴다. 지난주 배치 담당자가 교재 14화를 보고, 인덱스를 모두 끄고 직접 경로로 넣은 뒤 다시 짓도록 프로시저를 바꿨다. 일요일 스케줄러 기록은 「SUCCEEDED」였는데, 월요일 09:00 MD팀 분석 화면이 전부 0건으로 나왔다. MD팀: "지난주 판매가 하나도 없대요."

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO)
CF11_DW 분석용 사본(문제 설정, CF11과 같은 컬럼): 기본 키 CF11_DW_PK (SLE_NO, ITM_NO)(제약이 만든 유니크 인덱스, 리프 약 4,500블록) · 일반 인덱스 CF11_DW_X01 (ITM_NO)(리프 약 2,400블록) · FK와 트리거는 없다.
지난주까지는 인덱스를 켠 채 일반 INSERT로 6분 12초가 걸렸다. MD팀 화면은 월요일 08:30 이후에만 쓴다.
skip_unusable_indexes는 기본값 TRUE다. LOG_MSG는 로그 테이블에 한 줄을 남기는 자율 트랜잭션 프로시저다.

원본 SQL

sql

PROCEDURE P_CF11_DW_LOAD IS
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE CF11_DW';
  EXECUTE IMMEDIATE 'ALTER INDEX CF11_DW_PK UNUSABLE';
  EXECUTE IMMEDIATE 'ALTER INDEX CF11_DW_X01 UNUSABLE';

  INSERT /*+ APPEND */ INTO CF11_DW (SLE_NO, ITM_NO, SLE_QTY, SLE_UPRC)
  SELECT SLE_NO, ITM_NO, SLE_QTY, SLE_UPRC FROM CF11;
  COMMIT;

  EXECUTE IMMEDIATE 'ALTER INDEX CF11_DW_PK REBUILD';
  EXECUTE IMMEDIATE 'ALTER INDEX CF11_DW_X01 REBUILD';
  LOG_MSG('P_CF11_DW_LOAD', '적재 완료');
EXCEPTION
  WHEN OTHERS THEN
    EXECUTE IMMEDIATE 'ALTER INDEX CF11_DW_PK REBUILD';      -- 인덱스는 살려 둔다
    EXECUTE IMMEDIATE 'ALTER INDEX CF11_DW_X01 REBUILD';
    LOG_MSG('P_CF11_DW_LOAD', '경고: ' || SQLERRM);
END;

실행계획 학습용 예시

xplan

[일요일 — 스케줄러와 로그 테이블]
  USER_SCHEDULER_JOB_RUN_DETAILS   P_CF11_DW_LOAD · STATUS SUCCEEDED · 01:10:02 ~ 01:10:09
  로그 테이블
    01:10:03  P_CF11_DW_LOAD  경고: ORA-01502: index 'PUP.CF11_DW_PK' or partition of such index is in unusable state

[월요일 09:05]
  SELECT COUNT(*) FROM CF11_DW;      →  0
  USER_INDEXES   CF11_DW_PK  VALID · CF11_DW_X01  VALID   (빈 테이블에 다시 지었다)

[연습 DB · 같은 순서를 손으로 다시 돌림]
  ALTER INDEX CF11_DW_X01 UNUSABLE 만 하고 PK 는 켠 채 두면 같은 INSERT 가 된다
    → 적재 6.1 초 · redo 131 MB (대부분 PK 를 적재 끝에 한꺼번에 고치는 몫) · X01 REBUILD 1.6 초
  두 인덱스를 다 끄면
    → ORA-01502 (위와 같다)

문제

INSERT가 ORA-01502로 실패한 이유와, 그런데도 배치가 성공으로 끝나 월요일까지 아무도 몰랐던 이유를 설명하시오.
기본 키를 지키면서 인덱스 유지 비용 없이 다시 채우도록 프로시저를 고치시오.
고친 방법에서 실패할 수 있는 지점과 그때 어떻게 알게 되는지 쓰시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

PROCEDURE P_CF11_DW_LOAD IS
  v_src NUMBER;
  v_dst NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE CF11_DW';
  -- PK(유니크)는 UNUSABLE 로 끌 수 없다 — 제약을 끄면 제약이 만든 유니크 인덱스도 함께 내려간다
  EXECUTE IMMEDIATE 'ALTER TABLE CF11_DW DISABLE CONSTRAINT CF11_DW_PK DROP INDEX';
  EXECUTE IMMEDIATE 'ALTER INDEX CF11_DW_X01 UNUSABLE';          -- 일반 인덱스는 끈다

  INSERT /*+ APPEND */ INTO CF11_DW (SLE_NO, ITM_NO, SLE_QTY, SLE_UPRC)
  SELECT SLE_NO, ITM_NO, SLE_QTY, SLE_UPRC FROM CF11;
  COMMIT;

  -- 제약을 켜면 유니크 인덱스를 정렬해 새로 짓고 중복을 검사한다 (중복이 있으면 ORA-02437 로 실패)
  EXECUTE IMMEDIATE 'ALTER TABLE CF11_DW ENABLE CONSTRAINT CF11_DW_PK';
  EXECUTE IMMEDIATE 'ALTER INDEX CF11_DW_X01 REBUILD';

  -- 건수 대조 — 원천과 사본이 같아야 완료다
  SELECT COUNT(*) INTO v_src FROM CF11;
  SELECT COUNT(*) INTO v_dst FROM CF11_DW;
  IF v_src <> v_dst THEN
    RAISE_APPLICATION_ERROR(-20038, 'CF11_DW 건수 불일치: 원천 ' || v_src || ' / 사본 ' || v_dst);
  END IF;
  LOG_MSG('P_CF11_DW_LOAD', '적재 완료 ' || v_dst || '행');
EXCEPTION
  WHEN OTHERS THEN
    LOG_MSG('P_CF11_DW_LOAD', '실패: ' || SQLERRM);
    RAISE;                        -- 삼키지 않는다 — 스케줄러가 FAILED 로 적고 담당자가 아침 전에 안다
END;

개선 후 실행계획 학습용 예시

xplan

[연습 DB · 고친 순서 · 같은 1,096,708행]
  단계                                                 경과      redo
  TRUNCATE · PK 제약 끄기(인덱스 삭제) · X01 끄기        0.1 초    0.1 MB
  INSERT /*+ APPEND */                                 0.4 초    0.2 MB
  ENABLE CONSTRAINT CF11_DW_PK (인덱스 생성 · 중복 검사)  2.3 초    0.3 MB
  ALTER INDEX CF11_DW_X01 REBUILD                      1.6 초    0.2 MB
  합계                                                 4.4 초    0.8 MB   (지난주까지 6분 12초)
  건수 대조   CF11 1,096,708 = CF11_DW 1,096,708 · 스케줄러 SUCCEEDED

[일부러 실패시켜 본 날 (TEMP 를 줄여 ENABLE 에서 ORA-01652)]
  스케줄러 STATUS FAILED · 로그 테이블 '실패: ORA-01652 …' · 07:00 알림

모범 답안 서술

INSERT가 실패한 것은 꺼진 인덱스가 유니크(PK) 인덱스였기 때문이다. skip_unusable_indexes = TRUE는 꺼진 일반 인덱스를 옵티마이저가 건너뛰고 DML이 고치지 않게 할 뿐이다. 유니크 인덱스는 중복을 막는 일을 맡고 있어서 꺼진 채로는 건너뛸 수 없고, 그 테이블에 행을 넣으려는 순간 ORA-01502가 난다. 연습 DB에서도 PK를 켠 채 두면 같은 INSERT가 됐다. 그런데도 배치가 성공으로 끝난 것은 예외 처리부가 에러를 삼켰기 때문이다. WHEN OTHERS가 인덱스를 다시 짓고 로그에 '경고'를 남긴 뒤 예외를 밖으로 던지지 않았다. 스케줄러는 프로시저가 정상 종료했으니 SUCCEEDED로 적었다. 이미 TRUNCATE는 끝났고 TRUNCATE는 되돌릴 수 없으므로 CF11_DW는 빈 테이블로 남았고, 인덱스는 빈 테이블 위에 VALID로 다시 지어졌다. 그래서 월요일 09:00까지 아무도 몰랐다. 고친 프로시저는 유니크 인덱스를 끄는 대신 그 인덱스를 쓰는 PK 제약을 끈다. DISABLE CONSTRAINT … DROP INDEX로 제약과 유니크 인덱스를 함께 내리고, 일반 인덱스 X01만 UNUSABLE로 끈 뒤 직접 경로로 넣는다. 적재가 끝나면 ENABLE CONSTRAINT로 제약을 켠다. 이때 유니크 인덱스를 정렬해 한 번에 새로 짓고 중복도 검사한다. X01은 REBUILD한다. 연습 DB에서 합계 4.4초, redo 0.8MB로 지난주의 6분 12초보다 훨씬 짧다. 실패할 수 있는 지점은 적재(공간 부족), 제약 켜기(중복이 있으면 ORA-02437, 정렬 공간이 모자라면 ORA-01652), REBUILD다. 어느 곳에서 실패하든 예외 처리부가 로그를 남긴 뒤 RAISE로 다시 던지므로 스케줄러가 FAILED로 적고 담당자가 아침 전에 안다. 마지막에 원천과 사본의 건수를 대조해, 맞지 않으면 그 자체를 실패로 만든다. TRUNCATE로 시작하는 재적재는 실패하면 빈 테이블을 남기므로 '실패를 바로 아는 것'이 가장 중요한 안전장치다.

해설

제약을 끌 때 그 제약이 만든 유니크 인덱스는 기본으로 지워진다. 의도를 밝히려고 DROP INDEX를 적었다. 미리 만든 일반 인덱스를 USING INDEX로 쓰는 제약이라면 KEEP INDEX로 남길 수도 있지만, 그 인덱스는 다시 UNUSABLE로 끄고 REBUILD해야 한다.
다른 정답: PK는 켠 채 두고 X01만 끈다. 연습 DB에서 적재 6.1초, redo 131MB로 지난주(6분 12초)보다는 훨씬 낫고, 유니크 검사를 적재와 함께 하므로 단계가 단순하다. 대신 PK를 적재 끝에 한꺼번에 고치는 몫이 남는다.
중복이 있으면 ENABLE CONSTRAINT가 ORA-02437로 실패하고 PK도 없다. EXCEPTIONS INTO로 중복 행을 찾아 원천을 고친다. 원천 CF11에 같은 PK가 있으니 중복이 날 일은 거의 없지만 검사는 남겨 둔다.
사본을 하루라도 비워 둘 수 없다면 TRUNCATE부터 하지 말고, 새 테이블에 적재하고 검증한 뒤 이름을 바꿔 끼운다(DML-003의 순서).
교재 15화의 WHEN OTHERS THEN NULL과 같은 병이다. 예외를 '경고'로 적고 넘어가는 것도 삼키는 것이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황