One Oracle massive data deduplication experience

Oracle has a data that is already 25 million data, the software used to this table data is very slow, so ready to one months ago the data cleared.

My steps are (the bottom operations are all running in plsql)

1. First export this month's data to a temporary table (the data is what you want to keep)

CREATE TABLE temptable as select Id,name from table1 where sj>to_date (' 2013-7-31 23:59:59 ', ' yyyy-mm-dd hh24:mi:ss ');

2, then the entire 25 million data delete (this is I in the case of backup)

TRUNCATE TABLE table1;

3, then the entire table is deleted (because in advance from the online search, there are netizens said truncate, directly to the data from the temporary table back to a data file did not become smaller, I did not try)

drop TABLE table1;

4, the data from the temporary table all back

CREATE TABLE table1 as select Id,name from TempTable;

5. Then delete the temporary table

drop table temptable;

The whole process took about 10 minutes to complete.

The above is only the experience of their own operations, if there are incorrect or better ways to hope that everyone correct.

Attached: from http://www.cnblogs.com/simplefrog/archive/2012/07/30/2615169.html

syntax delete from AA truncate TABLE AA

difference 1.delete from the back can write the condition, truncate not.

2.delete from records are deleted, each row of records will go into the log, and truncate delete the entire page at once, so the day to the inside only record page release, in short, delete from the update log, truncate basic No, the use of less transaction log space.

3.delete from the time the table is deleted, an empty page is retained and truncate does not leave any pages in the table.

4. When a DELETE statement is executed with a row lock, the rows in the table are locked for deletion. Truncate always locks tables and pages instead of locking rows.

5. If there is an identity-generated self-increment ID column, the delete from is still incremented from the last number, that is, the seed is unchanged, and after truncate, the seed resumes its initial start.

6.truncate triggers for delete are not triggered because the truncate operation does not record individual row deletions.

Summary 1.truncate and delete only delete data without deleting the structure of the table (definition) The DROP statement will delete the table's structure by dependent constraints (constrain), trigger (trigger), index (indexed); Stored procedures/functions that depend on the table are preserved, but become invalid states.

The 2.delete statement is DML, which is put into rollback segement, the transaction is committed, and if there is a corresponding trigger, the execution will be triggered Truncate,drop is the DDL, the operation takes effect immediately, The original data is not placed in the rollback segment and cannot be rolled back. Operation does not trigger trigger.

The 3.delete statement does not affect the extent occupied by the table, the high waterline (higher watermark) remains in its original position. Obviously the drop statement releases all the space occupied by the table TRUNCATE statement by default see space released to Minextents   Extent unless reuse storage is used; Truncate will reset the high watermark (back to the beginning).

4. Speed, in general: drop> truncate > Delete.

5. Security: Use Drop and truncate carefully, especially when there is no backup. Otherwise, it's too late to cry.

6. On the use, want to delete some data rows with delete, note with the WHERE clause.     The rollback segment should be large enough. If you want to delete a table, delete all of the data by dropping it to keep the table. If it is unrelated to the transaction, use truncate. If it is related to a transaction, or if you want to trigger trigger, or delete the fragment inside the table, you can use truncate to keep up with reuse stroage and re-import/insert the data.

One Oracle massive data deduplication experience