how delete 12,33,200 rows in oracle table

10 pts.
Tags:
DELETE statement
Oracle Table
how delete 12,33,200 rows in oracle table

Answer Wiki

Thanks. We'll let you know when a new response is added.

How many total rows are in the table? If not much more than the rows you want to delete, then you might copy the rows you want to keep into a new table and truncate the old table and then copy the records back.

Otherwise, you will need to make sure your rollback segment is large enough to handle that many transactions or you could place the database in no archive mode.

[kccrosser] If you are trying to empty the entire table, just use “truncate table <tablename>”. If you are removing nearly all the rows, but need to keep a “few” (as in probably < 2,000,000), then the above answer is fastest/easiest – copy to a new table, truncate the old table, then add in the rows you want and drop the temporary table.

If, however, you need to keep a lot of rows, and/or you have referential integrity requirements (i.e., other tables have foreign key links to this table), then the best method is to write a simple script to delete the rows in large “chunks”.

Deleting row by row is incredibly inefficient if you commit each delete. On the other hand deleting 12 million rows in a single transaction will probably kill your system’s rollback segments and/or block all other transactions due to resource constraints.

I would probably use the “bulk collect” to get the keys of the records to kill, then kill them in “chunks” of around 10000 records each (do a commit after every 10000 records). If your system isn’t that large, you might need to commit after every 5000.

Example:

PROCEDURE delete_my_rows
IS
ci_limit constant number = 10000;
CURSOR tbl_cur
IS
SELECT <keycol> FROM <my_table> WHERE <condition>;

TYPE typetemp_tbl IS TABLE OF <my_table>.<keycol>
INDEX BY PLS_INTEGER;

temp_tbl typetemp_tbl;
BEGIN
OPEN tbl_cur;
LOOP
FETCH tbl_cur
BULK COLLECT INTO temp_tbl LIMIT ci_limit;

FOR indx IN 1 .. temp_tbl.COUNT
LOOP
delete from <my_table> where <keycol> = temp_tbl.<keycol>;
END LOOP;
COMMIT;

EXIT WHEN temp_tbl.COUNT < ci_limit;

END LOOP;

CLOSE tbl_cur;
END delete_my_rows;

———————————-
If deletion is regular i.e. on daily basis, then partition the table based on the chunk e.g daily basis. So for deletion is very easy to delete by executing a drop partition.
Cheers- Muthu

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Edgar
    Hi Melanie, Why do you propose to do a FOR ... LOOP instead of using the FORALL construct after your BULK COLLECT. Would probably perform even faster -- about twice as fast according to: http://www.dba-oracle.com/plsql/t_plsql_bulk_update.htm Kind regards, Edgar
    10 pointsBadges:
    report
  • Keithsh
    I get the error Error at line 1 ORA-06550: line 18, column 44: PLS-00302: component 'the_ID' must be declared ORA-06550: line 18, column 35: PL/SQL: ORA-00904: "TEMP_TBL"."the_ID": invalid identifier ORA-06550: line 18, column 1: PL/SQL: SQL Statement ignored When i attempt to execute the following declare CURSOR tbl_cur IS SELECT the_id FROM tablewithdeltedid; TYPE typetemp_tbl IS TABLE OF tbl_cur%rowtype; temp_tbl typetemp_tbl; BEGIN OPEN tbl_cur; LOOP FETCH tbl_cur BULK COLLECT INTO temp_tbl; FOR indx IN 1 .. temp_tbl.COUNT LOOP delete from deletefromtable where the_id = temp_tbl.the_id; END LOOP; COMMIT; END LOOP; CLOSE tbl_cur; END; Any thoughts?? Thanks
    10 pointsBadges:
    report
  • carlosdl
    Try defining your type this way:
    TYPE typetemp_tbl IS TABLE OF tablewithdeltedid.the_id%type;
    Also, your code doesn't currently have a way to exit from the outer loop.
    69,365 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following