如何批量删除Oracle表中的大量数据

jjjwad0x  于 2023-08-04  发布在  Oracle
关注(0)|答案(5)|浏览(251)

我使用Oracle 10 g,并希望从Persons表中删除约500万条记录(共有1500万条记录),该表在Order表中没有任何引用(共有500万条记录)
由于一次性删除500万条记录会导致撤销日志问题,我决定分批删除10万条记录。我使用下面的查询:

DELETE FROM Persons p
      WHERE     City = 'ABC'
            AND NOT EXISTS
                   (SELECT O_Id
                      FROM Orders o
                     WHERE p.P_Id = o.P_Id)
            AND ROWNUM <= 100000

字符串
现在的问题是,这个查询执行100 k条记录所需的时间与执行500万条记录所需的时间一样长,因为仍然会对两个表进行全表扫描和连接。
是否有一种有效的方法来重写此查询以加快执行速度?或者用更好的连接条件替换NOT EXISTS子句?或者使用一些更好的方法来限制记录到100 k?
P.S.这是一个一次性的操作,我不能使用任何DDL操作,但是pl/sql可以

jxct1oxe

jxct1oxe1#

根据我的经验,删除大量行的最快方法是:
解决方案1(Tom Kyte推荐)

`SET TRANSACTION USE ROLLBACK SEGMENT <your_seg>
 DELETE FROM <tab1> WHERE <cond>
 COMMIT`

字符串

解决方案2

`create table new_table unrecoverable as select * from old_table where ....;
drop table old_table;
rename new_table to old_table;
create index old_table_idx1 on old_table(c1,c2) unrecoverable parallel 5;
`


我在不同的情况下使用了第二种解决方案:删除大量行总是最快的。
另一种方法是将要删除的数据放在一个分区中,然后删除该分区(每个分区都有自己的回滚段,可以使用并行性,...)。

bvk5enib

bvk5enib2#

如果希望此查询运行得更快,请添加以下两个索引:

create index idx_persons_city_pid on persons(city, p_id);
 create index idx_orders_pid on orders(p_id);

字符串

wmomyfyw

wmomyfyw3#

DECLARE
 v_limit PLS_INTEGER :=100000;

CURSOR person_deleted_cur
IS 
 SELECT rowid 
   FROM Persons p
  WHERE City = 'ABC'
   AND NOT EXISTS
               (SELECT O_Id
                  FROM Orders o
                 WHERE p.P_Id = o.P_Id);

TYPE person_deleted_nt IS TABLE OF person_deleted_cur%ROWTYPE
        INDEX BY PLS_INTEGER;
BEGIN
  OPEN person_deleted_cur;
    LOOP
      FETCH person_deleted_cur 
        BULK COLLECT INTO person_deleted_nt LIMIT v_limit;

    FORALL indx IN 1 .. person_deleted_nt.COUNT 
      DELETE FROM Persons WHERE rowid=person_deleted_nt(indx);

    EXIT WHEN person_deleted_cur%NOTFOUND;

   END LOOP;

   CLOSE person_deleted_cur;
  COMMIT;
END;
/

字符串

shstlldc

shstlldc4#

解决方案1:下面的方法将一次删除1000条记录,并将提交删除的1000条记录。
--此集合将包含1000个要删除的ID
创建或替换数组名称类型为VARARRAY(1000)的编号;

DECLARE  
arrayNameType ARRAYNAMETYPE ;  
recordsToBeDeleted NUMBER; 
noOfRecordsDeleted NUMBER;
countDeleted NUMBER;      

CURSOR CURSOR1  
 IS  
   SELECT ID_COLUMN FROM TABLE with conditions;  

BEGIN   
    noOfRecordsDeleted  :=0;  
    countDeleted :=0;  
   --Taking the count of the total no of records that is planned to delete  
   SELECT COUNT(*) INTO recordsToBeDeleted from table_name with where condition  

     open CURSOR1;  
        LOOP   
          FETCH CURSOR1 BULK COLLECT INTO arrayNameType LIMIT 1000;  
          EXIT WHEN arrayNameType.count=0;  
            delete from table where id in (select * from table (arrayNameType));  
countDeleted:=SQL%ROWCOUNT;
noOfRecordsDeleted:= countDeleted+noOfRecordsDeleted
            commit;  

        END LOOP;  
CLOSE CURSOR1;  

end;

字符串
解决方案二:如果必须删除70%或以上的数据,则可以考虑以下方法
1)创建一个临时表,并将数据复制到该临时表中,该临时表不被删除2)截断原始表3)将临时表中的记录插入原始表4)删除临时表。5)需要注意的指标

flvtvl50

flvtvl505#

另一种删除方式:

begin
  dbms_errlog.create_error_log('PERSONS');
end;
/

-- index on foreign key is useful thing in many cases, not only now
create index idx_orders_pid on orders(p_id); 

declare 
  min_id number;
  max_id number;
begin
  select min(p_id), max(p_id)
    into min_id, max_id
    from persons;

  for i in min_id..max_id loop
    delete from persons where p_id between i and i + 100000
    log errors into err$_persons reject limit unlimited;
  end loop;
end;
/

drop table err$_persons;

字符串

相关问题