Oracle에서 LOT 데이터 삭제

Oracle에서 LOT 데이터 삭제

저는 데이터베이스 사람이 아닙니다. 제 db 작업의 대부분은 MySQL과 함께 했으므로, 이 질문에 매우 순진한 것이 있다면 용서해 주십시오.

약 1억 개의 행이 있는 오라클 테이블에서 550만 개의 행을 삭제해야 합니다.임시 테이블에 삭제해야 하는 행의 ID가 모두 있습니다.몇 천 줄만 있으면 이렇게 할 겁니다.

DELETE FROM table_name WHERE id IN (SELECT id FROM temp_table);
COMMIT;

550만 행이기 때문에 주의하거나 다르게 해야 할 사항이 있습니까?저는 루프를 하는 것에 대해 생각했습니다, 다음과 같은 것입니다.

DECLARE
  vCT NUMBER(38) := 0;

BEGIN
  FOR t IN (SELECT id FROM temp_table) LOOP
    DELETE FROM table_name WHERE id = t.id;
    vCT := vCT + 1;
    IF MOD(vCT,200000) = 0 THEN
      COMMIT;
    END IF;
  END LOOP;
  COMMIT;
END;

우선, 이것은 제가 생각하는 것처럼 한 번에 200,000개의 커밋을 일괄 처리하는 것입니까?그렇다고 가정하면 550만 개의 SQL 문을 생성하여 200,000개의 일괄 커밋하는 것이 더 나은지 아니면 하나의 SQL 문을 사용하여 한 번에 커밋하는 것이 더 나은지 아직 잘 모르겠습니다.

아이디어? 베스트 프랙티스?

편집: 첫 번째 옵션인 단일 삭제 문을 실행했는데 개발을 완료하는 데 2시간밖에 걸리지 않았습니다.이를 기반으로 프로덕션에서 실행되도록 대기열에 있습니다.

첫 번째 접근 방식은 쿼리 최적화 도구를 숨기려는 것이 아니라 수행하려는 작업을 명확하게 보여주기 때문에 더 좋습니다.데이터베이스 엔진은 내부적으로 5.5m(또는 테이블의 5.5%)를 삭제하는 방법과 200k(또는 0.2%)를 삭제하는 방법이 다를 수 있습니다.

다음은 Oracle에서 대규모 DELETE에 대한 기사입니다.

가장 빠른 방법은 다음을 사용하여 새 파일을 만드는 것입니다.CREATE TABLE AS SELECT용사를 NOLOGGING선택.내 말은:

ALTER TABLE table_to_delete RENAME TO tmp;
CREATE TABLE table_to_delete NOLOGGING AS SELECT .... ;

물론 유효성 검사가 없는 제약 조건, 로깅이 없는 인덱스, 허가...를 다시 만들어야 하지만 속도가 매우 빠릅니다.

운영에 문제가 있는 경우 다음을 수행할 수 있습니다.

ALTER TABLE table_to_delete RENAME to tmp;
CREATE VIEW table_to_delete AS SELECT * FROM tmp;
-- Until there can be instantly
CREATE TABLE new_table NOLOGGING AS SELECT .... FROM tmp WHERE ...;
<create indexes with nologging>
<create constraints with novalidate>
<create other things...>
-- From here ...
DROP VIEW table_to_delete;
ALTER TABLE new_table RENAME TO table_to_delete;
-- To here, also instantly

다음 사항을 처리해야 합니다.

• 저장 프로시저는 무효화될 수 있지만 두 번째 호출 시 다시 컴파일됩니다.테스트를 해봐야 합니다.
• NOLOGGING최소 redo가 생성됨을 의미합니다.DBA 역할이 있는 경우ALTER SYSTEM CHECKPOINT인스턴스 충돌 시 데이터가 손실되지 않도록 보장합니다.
• 위해서NOLOGGING는 테블공있합니다어야도간이에도 합니다.NOLOGGING.

수백만 개의 삽입물을 만드는 것보다 더 나은 또 다른 옵션은 다음과 같습니다.

-- Create table with ids
DELETE FROM table_to_delete
 WHERE ID in (SELECT ID FROM table_with_ids WHERE ROWNUM < 100000);
DELETE FROM table_with_ids WHERE ROWNUM < 100000;
COMMIT;
-- Run this 50 times ;-)

계속 사용하려는 열린 커서(루프된 커서)로 트랜잭션을 커밋하고 닫기 때문에 스냅샷이 너무 오래된 메시지를 생성할 수 있으므로 PLSQL을 선택하는 것이 좋습니다.Oracle은 이를 허용하지만 좋은 방법은 아닙니다.

업데이트: 마지막 PLSQL 블록이 작동하는지 확인할 수 있는 이유는 무엇입니까?제 생각엔...

• 다른 사용자는 통계 수집, 이동, 레코드 삽입 등의 작업을 탭하는 등의 이유로 이 임시 테이블을 사용하지 않습니다.이것만을 위한 보조 테이블이기 때문에 그것은 보장될 수 있습니다.
• 그런 다음 마지막 어설션을 사용하여 쿼리가 동일한 계획으로 정확히 실행되고 동일한 순서로 행을 반환합니다.

삭때수행에서 대량 할 때Oracle부족하지 않도록 주의하십시오.UNDO SEGMENTS.

을 할 때DML,Oracle을 저모든 변사에기다로 .REDOlog(이전 데이터와 새 데이터).

때.REDO로그가 채워지거나 시간 초과가 발생하는 경우Oraclelog synchronization그것은 씁니다.new파일에 이 경우 데이터 을 사용 가능으로를 입력하고 를 데이터 파일에 .UNDO에서 볼 수 하기 "" ("")commit변경 내용)을 참조하십시오.

내용을 는 경내커용때공간의 이 있습니다.UNDOyoor 트랜잭션에 의해 점유된 세그먼트가 해방됩니다.

▁you를 삭제한다는 합니다.5M 데이터 행에는 공간이 합니다.all의 신의이에 이UNDO할 수 를 지정합니다.all at once 및 커밋 됩니다.) 및 커밋 후에만 삭제됩니다.

경우)가 는동시있를한것읽의을다다미니합는어경이야쿼우리는▁from다▁(▁this니▁read▁to▁need의미▁any)▁the합를if이▁thaturrent▁conc▁means는▁also있▁queries▁will)에서 읽어야 한다는 것을 의미하기도 합니다.REDO 또는 로그또UNDO세그먼트(segment)를 표시합니다.이것은 데이터에 액세스하는 가장 빠른 방법이 아닙니다.

이는 또한 옵티마이저가 다음을 선택하는지 여부를 의미합니다.HASH JOIN쿼리의 이 높은 ), 은 " " " " " " " " ( " " " ), " " " " " 에 않습니다.HASH_AREA_SIZE입니다), 에는 (으)ㄹ 필요가 있을 입니다.several 테이블 큰테블위스테면의일이이이로 됩니다.REDO또는UNDO.

위에서 말한 모든 것을 고려할 때, 당신은 아마도 데이터를 삭제하는 것이 좋을 것입니다.200,000청크하고 중간에 변경 내용을 커밋합니다.

, , 위서설문제둘고화, 것다니입할째째거최하적제를한명에▁your▁thus▁described다것,,니▁above입▁will▁problems▁you▁optimize최할적,▁and,화▁second,둘▁get따,,째▁first라▁rid를 최적화할 것입니다.HASH_JOIN읽기 수는 같지만 읽기 자체가 더 효율적이기 때문입니다.

하지만 당신의 경우에는 옵티마이저를 강제로 사용하려고 합니다.NESTED LOOPS당신의 경우가 더 빠를 것으로 예상합니다.

합니다.ID다음과 같이 쿼리를 다시 작성합니다.

DELETE  
FROM   (
       SELECT  /*+ USE_NL(tt, tn) */
               tn.id
       FROM    temp_table tt, table_name tn
       WHERE   tn.id = tt.id
       )

는 설본키야합니다해정에 있어야 합니다.temp_table이 쿼리가 작동합니다.

다음과 비교해 보십시오.

DELETE  
FROM   (
       SELECT  /*+ USE_HASH(tn tt) */
               tn.id
       FROM    temp_table tt, table_name tn
       WHERE   tn.id = tt.id
       )

무엇이 더 빠른지 확인하고 이것을 고수합니다.

첫 번째 예처럼 모든 것을 한 번에 하는 것이 좋습니다.그러나 제거 후 더 이상 사용하지 않는 블록을 회수하기를 원할 수 있으므로 먼저 DBA에게 확인해 보겠습니다.또한 사용자 관점에서 일반적으로 볼 수 없는 스케줄링 문제가 있을 수 있습니다.

원본 SQL에 시간이 오래 걸리는 경우 일부 동시 SQL은 UNDO를 사용하여 커밋되지 않은 변경 사항 없이 데이터 버전을 재구성해야 하므로 느리게 실행될 수 있습니다.

타협안은 다음과 같은 것일 수 있습니다.

FOR i in 1..100 LOOP
  DELETE FROM table_name WHERE id IN (SELECT id FROM temp_table) AND ROWNUM < 100000;
  EXIT WHEN SQL%ROWCOUNT = 0;
  COMMIT;
END LOOP;

필요에 따라 ROWNUM을 조정할 수 있습니다.ROWNUM이 작으면 커밋 빈도가 높아지고 실행 취소를 적용해야 한다는 측면에서 다른 세션에 미치는 영향이 줄어듭니다.그러나 실행 계획에 따라 다른 영향이 있을 수 있으며 전체적으로 시간이 더 걸릴 수 있습니다.기술적으로 루프의 'FOR' 부분은 EX만큼 필요하지 않습니다.IT는 루프를 종료합니다.하지만 무제한 루프가 막히면 세션을 죽이는 것이 고통스럽기 때문에 저는 그들에 대해 편집증적입니다.

한 번의 삭제로 실행하는 것이 좋습니다.

삭제할 하위 테이블이 있습니까?그렇다면 해당 테이블의 외래 키가 색인화되어 있는지 확인합니다.그렇지 않으면 삭제한 모든 행에 대해 하위 테이블을 전체 검색하여 속도가 매우 느려질 수 있습니다.

실행되는 삭제 진행률을 확인할 수 있는 몇 가지 방법이 필요할 수 있습니다.Oracle 데이터베이스에서 장기간 실행 중인 쿼리를 확인하는 방법을 참조하십시오.

다른 사람들이 제안했듯이, 만약 당신이 물을 테스트하고 싶다면, 당신은 당신의 질의 끝에 rownum < 10000을 넣을 수 있습니다.

과거 오라클 7에서도 비슷한 작업을 수행한 적이 있는데, 수천 개의 테이블에서 수백만 개의 행을 삭제해야 했습니다.이 스크립트는 모든 라운드 성능, 특히 대규모 삭제(1개의 테이블에서 백만 행 이상)에 대해 잘 작동했습니다.

약간 수정해야 합니다(즉, 사용자/암호를 검사하고 롤백 세그먼트를 올바르게 설정).또한 DBA와 이 문제를 논의하고 테스트 환경에서 먼저 실행해야 합니다.그 모든 것을 말씀드렸듯이, 그것은 꽤 쉽습니다.delete_sql() 함수는 지정한 테이블에서 로이드 배치를 검색한 다음 배치별로 해당 로이드를 삭제합니다.예를 들어,

exec delete_sql('MSF710', 'select rowid from msf710 s where  (s.equip_no, s.eq_tran_date, s.comp_data, s.rec_710_type, s.seq_710_no) not in  (select c.equip_no, c.eq_tran_date, c.comp_data, c.rec_710_type, c.seq_710_no  from  msf710_sched_comm c)', 500);

위의 예는 sql 문을 기준으로 테이블 MSF170에서 한 번에 500개의 레코드를 삭제하는 것입니다.

여러 테이블에서 데이터를 삭제해야 하는 경우 추가 데이터를 포함합니다.exec delete_sql(...)스클데일레토의sql 파일의 행

롤백 세그먼트를 다시 온라인으로 전환해야 합니다. 스크립트에 없습니다.

spool delete-tables.log;
connect system/SYSTEM_PASSWORD
alter rollback segment r01 offline;
alter rollback segment r02 offline;
alter rollback segment r03 offline;
alter rollback segment r04 offline;

connect mims_3015/USER_PASSWORD

CREATE OR REPLACE PROCEDURE delete_sql (myTable in VARCHAR2, mySql in VARCHAR2, commit_size in number) is
  i           INTEGER;
  sel_id      INTEGER;
  del_id      INTEGER;
  exec_sel    INTEGER;
  exec_del    INTEGER;
  del_rowid   ROWID;

  start_date  DATE;
  end_date    DATE;
  s_date      VARCHAR2(1000);
  e_date      VARCHAR2(1000);
  tt          FLOAT;
  lrc         integer;


BEGIN
  --dbms_output.put_line('SQL is ' || mySql);
  i := 0;
  start_date:= SYSDATE;
  s_date:=TO_CHAR(start_date,'DD/MM/YY HH24:MI:SS');


  --dbms_output.put_line('Deleting ' || myTable);
  sel_id := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(sel_id,mySql,dbms_sql.v7);
  DBMS_SQL.DEFINE_COLUMN_ROWID(sel_id,1,del_rowid);
  exec_sel := DBMS_SQL.EXECUTE(sel_id);
  del_id := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(del_id,'delete from ' || myTable || ' where rowid = :del_rowid',dbms_sql.v7);
 LOOP
   IF DBMS_SQL.FETCH_ROWS(sel_id) >0 THEN
      DBMS_SQL.COLUMN_VALUE(sel_id,1,del_rowid);
      lrc := dbms_sql.last_row_count;
      DBMS_SQL.BIND_VARIABLE(del_id,'del_rowid',del_rowid);
      exec_del := DBMS_SQL.EXECUTE(del_id);

      -- you need to get the last_row_count earlier as it changes.
      if mod(lrc,commit_size) = 0 then
        i := i + 1;
        --dbms_output.put_line(myTable || ' Commiting Delete no ' || i || ', Rowcount : ' || lrc);
        COMMIT;
      end if;
   ELSE 
       exit;
   END IF;
 END LOOP;
  i := i + 1;
  --dbms_output.put_line(myTable || ' Final Commiting Delete no ' || i || ', Rowcount : ' || dbms_sql.last_row_count);
  COMMIT;
  DBMS_SQL.CLOSE_CURSOR(sel_id);
  DBMS_SQL.CLOSE_CURSOR(del_id);

  end_date := SYSDATE;
  e_date := TO_CHAR(end_date,'DD/MM/YY HH24:MI:SS');
  tt:= trunc((end_date - start_date) * 24 * 60 * 60,2);
  dbms_output.put_line('Deleted ' || myTable || ' Time taken is ' || tt || 's from ' || s_date || ' to ' || e_date || ' in ' || i || ' deletes and Rows = ' || dbms_sql.last_row_count);

END;
/

CREATE OR REPLACE PROCEDURE delete_test (myTable in VARCHAR2, mySql in VARCHAR2, commit_size in number) is
  i integer;
  start_date DATE;
  end_date DATE;
  s_date VARCHAR2(1000);
  e_date VARCHAR2(1000);
  tt FLOAT;
BEGIN
  start_date:= SYSDATE;
  s_date:=TO_CHAR(start_date,'DD/MM/YY HH24:MI:SS');
  i := 0;
  i := i + 1;
  dbms_output.put_line(i || ' SQL is ' || mySql);
  end_date := SYSDATE;
  e_date := TO_CHAR(end_date,'DD/MM/YY HH24:MI:SS');
  tt:= round((end_date - start_date) * 24 * 60 * 60,2);
  dbms_output.put_line(i || ' Time taken is ' || tt || 's from ' || s_date || ' to ' || e_date);
END;
/

show errors procedure delete_sql
show errors procedure delete_test

SET SERVEROUTPUT ON FORMAT WRAP SIZE 200000; 

exec delete_sql('MSF710', 'select rowid from msf710 s where  (s.equip_no, s.eq_tran_date, s.comp_data, s.rec_710_type, s.seq_710_no) not in  (select c.equip_no, c.eq_tran_date, c.comp_data, c.rec_710_type, c.seq_710_no  from  msf710_sched_comm c)', 500);






spool off;

아 그리고 마지막 팁.속도가 느리고 테이블에 따라 다운타임이 발생할 수 있습니다.테스트, 타이밍 및 튜닝은 여기서 가장 친한 친구입니다.

여기에 있는 모든 답변은 훌륭합니다. 한 가지만 추가하면 됩니다. 테이블의 모든 레코드를 삭제하고 롤백할 필요가 없다면 table 잘라내기 명령을 사용해야 합니다.

(당신의 경우, 당신은 단지 부분 집합을 삭제하기를 원했지만, 비슷한 문제를 가지고 잠복해 있는 사람이라면, 저는 이것을 추가할 것이라고 생각했습니다.)

내게 가장 쉬운 방법은:-

DECLARE
L_exit_flag VARCHAR2(2):='N';
L_row_count NUMBER:= 0;

BEGIN
   :exit_code        :=0;
   LOOP
      DELETE table_name
       WHERE condition(s) AND ROWNUM <= 200000;
       L_row_count := L_row_count + SQL%ROWCOUNT;
       IF SQL%ROWCOUNT = 0 THEN
          COMMIT;
          :exit_code :=0;
          L_exit_flag := 'Y';
       END IF;
      COMMIT;
      IF L_exit_flag = 'Y'
      THEN
         DBMS_OUTPUT.PUT_LINE ('Finally Number of Records Deleted : '||L_row_count);
         EXIT;
      END IF;
   END LOOP;
   --DBMS_OUTPUT.PUT_LINE ('Finally Number of Records Deleted : '||L_row_count);
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE ('Error Code: '||SQLCODE);
      DBMS_OUTPUT.PUT_LINE ('Error Message: '||SUBSTR (SQLERRM, 1, 240));
      :exit_code := 255;
END;

언급URL : https://stackoverflow.com/questions/644975/deleting-a-lot-of-data-in-oracle