Oracle에서 지정된 행을 삭제하는 최적의 방법

Oracle에서 지정된 행을 삭제하는 최적의 방법

저는 다양한 크기의 6개 테이블 중 하나에서 수만 개의 행을 삭제해야 하지만 그 사이에 약 3천만 개의 행이 있는 프로젝트가 있습니다.제가 받은 데이터의 구조 때문에, 저는 6개의 테이블 중 어떤 테이블에 삭제해야 할 행이 있는지 모르기 때문에 모든 테이블에 대해 모든 삭제를 실행해야 합니다.ID 열에 대한 인덱스를 작성하여 속도를 높이려고 했지만 속도가 빨라지면 제거할 수 있습니다.

문제는 실제로 삭제를 수행할 수 있는 효율적인 방법을 찾을 수 없다는 것입니다.테스트를 위해 약 9400개의 행이 있는 단일 테스트 테이블에 대해 7384개의 행 삭제를 실행하고 있습니다.Oracle SQL Developer에서 가능한 여러 쿼리 솔루션을 테스트했습니다.

7384 별도DELETE문에 203초가 걸렸습니다.

delete from TABLE1 where ID=1000001356443294;
delete from TABLE1 where ID=1000001356443296;
etc...

7384 별도SELECT문이 57초 소요됨:

select ID from TABLE1 where ID=1000001356443294
select ID from TABLE1 where ID=1000001356443296
etc...

7384 별도DELETE from (SELECT)214초가 걸렸습니다.

delete from (select ID from TABLE1 where ID=1000001356443294);
delete from (select ID from TABLE1 where ID=1000001356443296);
etc...

1 SELECT7384를 포함하는 진술OR의 절은 127.4초를 사용했습니다.

select ID from TABLE1 where ID=1000001356443294 or ID = 1000001356443296 or ...

1 DELETE from (SELECT)7384를 포함하는 진술OR의 절은 74.4초를 사용했습니다.

delete from (select ID from TABLE1 where ID=1000001356443294 or ID = 1000001356443296 or ...)

마지막이 가장 빠를 수도 있지만, 추가 테스트를 통해 9000 행 테이블에서 200,000 행 테이블(최종 테이블 세트 크기의 1% 미만)로 확장할 때에도 동일한 문을 실행하는 데 14분이 소요됩니다.행당 50% 이상 빨라지지만 전체 데이터 세트에 대해 실행될 때는 하루 정도까지 추정됩니다.저는 우리가 이 일을 하는 데 사용했던 소프트웨어가 약 20분 안에 그것을 할 수 있다는 것을 잘 알고 있습니다.

그래서 제 질문은 다음과 같습니다.

• 삭제하는 더 좋은 방법이 있습니까?
• 제가 한 번 사용해볼까요?SELECT주어진 행이 어떤 테이블에 있는지 검색하고 삭제 쿼리를 실행하는 문(예: 두 번째 테스트)?그마저도 꽤 느려 보이지만...
• 삭제 속도를 높이기 위해 제가 할 수 있는 다른 방법이 있나요?DBA 수준의 액세스 권한이나 지식이 없습니다.

질문에 답변하기 전에 다음과 같이 진행하겠습니다.

명령문의 수와 명령문이 발행하는 작업을 상대적으로 최소화합니다.

모든 시나리오에서는 ID 테이블이 있다고 가정합니다.PURGE_IDS합니다.TABLE_1,TABLE_2 타기.

대량 삭제의 경우 CREATE TABLE을 SELECT로 사용하는 것을 고려합니다.

작업이행 중을 삭제하는 삭제하지 마십시오. 30%를 합니다.create table as select유지할 행과 새 테이블을 이전 테이블로 바꿉니다.INSERT /*+ APPEND */ ... NOLOGGING만약 당신이 그것을 살 수 있다면 놀랍도록 저렴합니다.일부 동시 활동이 있더라도 온라인 테이블 재정의를 사용하여 테이블을 제자리에 재구성할 수 있습니다.

행을 삭제하지 않을 것이라는 것을 알고 있는 DELETE 문을 실행하지 않음

ID 값이 여섯 개 테이블 중 하나에 있을 경우 삭제한 ID를 추적하고 다른 테이블에서 해당 ID를 삭제하지 마십시오.

CREATE TABLE TABLE1_PURGE NOLOGGING
AS 
SELECT ID FROM PURGE_IDS INNER JOIN TABLE_1 ON PURGE_IDS.ID = TABLE_1.ID;

DELETE FROM TABLE1 WHERE ID IN (SELECT ID FROM TABLE1_PURGE);

DELETE FROM PURGE_IDS WHERE ID IN (SELECT ID FROM TABLE1_PURGE);

DROP TABLE TABLE1_PURGE;

반복합니다.

필요한 경우 동시성 관리

또 다른 방법은 테이블 위에 PL/SQL 루프를 사용하여 행 수 제한 삭제 문을 실행하는 것입니다.이 방법은 삭제를 실행하는 테이블에 대해 삽입/업데이트/삭제 동시 로드가 많은 경우에 가장 적합합니다.

declare
  l_sql varchar2(4000);
begin
  for i in (select table_name from all_tables 
             where table_name in ('TABLE_1', 'TABLE_2', ...)
             order by table_name);
  loop
    l_sql := 'delete from ' || i.table_name || 
             ' where id in (select id from purge_ids) ' || 
             '   and rownum <= 1000000';
    loop
      commit;
      execute immediate l_sql;
      exit when sql%rowcount <> 1000000;  -- if we delete less than 1,000,000
    end loop;                             -- no more rows need to be deleted!
  end loop;
  commit;
end;

삭제할 모든 ID를 테이블에 저장합니다.그러면 세 가지 방법이 있습니다.테이블에 있는 모든 ID를 루프한 다음 X 커밋 간격 동안 한 번에 하나의 행을 삭제합니다.X는 100 또는 1000일 수 있습니다.OLTP 환경에서 작동하며 잠금을 제어할 수 있습니다.

Oracle 대량 삭제 사용

상관된 삭제 쿼리를 사용합니다.

일반적으로 단일 쿼리는 컨텍스트 전환 및 구문 분석이 적기 때문에 여러 쿼리보다 빠릅니다.

먼저 삭제 중에 인덱스를 비활성화하는 것이 좋습니다.

을 사용해 보십시오. MERGE INTO는 다음과 같습니다.
ID와 TABLE1의 추가 열을 가진 임시 테이블을 만들고 다음을 사용하여 테스트합니다.

MERGE INTO table1 src
USING (SELECT id,col1
         FROM test_merge_delete) tgt
ON (src.id = tgt.id)
WHEN MATCHED THEN
  UPDATE
     SET src.col1 = tgt.col1
  DELETE
   WHERE src.id = tgt.id

저는 이 코드를 시도해 보았는데 제 경우에는 잘 작동합니다.

DELETE FROM NG_USR_0_CLIENT_GRID_NEW WHERE rowid IN
( SELECT rowid FROM
  (
      SELECT wi_name, relationship, ROW_NUMBER() OVER (ORDER BY rowid DESC) RN
      FROM NG_USR_0_CLIENT_GRID_NEW
      WHERE wi_name = 'NB-0000001385-Process'
  )
  WHERE RN=2
);

언급URL : https://stackoverflow.com/questions/10092407/optimal-way-to-delete-specified-rows-from-oracle