Oracle clears table data quickly, Oracle clears table data

Source: Internet
Author: User

Oracle clears table data quickly, Oracle clears table data
Oracle Quick Clear table data
I. Introduction

 

Table data deletion usually involves three common commands: truncate, delete, and drop. However, their execution methods, efficiency, and results are different. For example, when data of million records is deleted A big difference.

Ii. truncate delete drop

 

The three methods can be used to clear table data. Their Similarities and differences are as follows:

Similarities:

1. truncate, delete without where clause, and drop all delete table data.

2. drop and truncate are all DDL statements (Data Definition Language), which are automatically submitted after execution.

Differences:

1. the "truncate" and "delete" statements that only delete data do not delete the table's structure (Definition) drop statement will delete the constraints, triggers, and indexes on which the table structure is dependent ); stored Procedures/functions dependent on the table will be retained but changed to invalid state.

2. The delete statement is the database operation language (dml). This operation will be placed in the rollback segment and take effect after the transaction is committed. If there is a corresponding trigger, it will be triggered during execution. Truncate and drop are database Definition Language (ddl). The operation takes effect immediately. The original data is not stored in rollback segment, and cannot be rolled back. trigger is not triggered.

3. The delete statement does not affect the extent used by the table. The high watermark statement keeps the original position unchanged, and the drop statement releases all the space occupied by the table. By default, the truncate statement releases the space to the extent of minextents, unless reuse storage is used; truncate resets the high water line (back to the beginning ).

4. Speed. Generally, drop> truncate> delete

5. security: Use drop and truncate with caution, especially when no backup is available. otherwise, the system will be too late to use. If you want to delete some data rows, use the delete clause. the rollback segment must be large enough. if you want to delete a table, you can use drop to retain the table and delete all the data. If it is not related to transactions, use truncate. If it is related to a transaction or you want to trigger a trigger, delete is used. If you want to organize fragments in the table, you can use truncate to keep up with the reuse stroage, and then re-import/insert data.

6. delete is a DML statement and is not automatically submitted. Drop/truncate are DDL statements, which are automatically submitted after execution.

7. The truncate table function is the same as the DELETE statement without the WHERE clause: both DELETE all rows in the TABLE. However, truncate table is faster than DELETE and uses less system and transaction log resources. The DELETE statement deletes a row at a time and records one row in the transaction log. Truncate table deletes data by releasing the data pages used to store TABLE data, and only records the release of pages in transaction logs.

8. truncate table deletes all rows in the TABLE, but the TABLE structure and its columns, constraints, and indexes remain unchanged. The Count value used by the new row ID is reset to the seed of the column. To retain the ID Count value, use DELETE instead. To delete TABLE definitions and data, use the drop table statement.

9. For tables referenced by the foreign key constraint, the truncate table cannot be used, but the DELETE statement without the WHERE clause should be used. Because the truncate table is not recorded in the log, it cannot activate the trigger.
10. The truncate table cannot be used in the index view.

 

Iii. oracle foreign key operations

 

1. view the Foreign keys of all tables:

        select table_name, constraint_name from user_constraints where constraint_type = 'R';

2. Disable all foreign key constraints:

        select 'alter table ' || table_name || ' dsable constraint ' || constraint_name ||';' from user_constraints where constraint_type = 'R';

Generate the following SQL statement (the table name depends on the actual situation ):

        alter table RS_ZGSHBX disable constraint FK_RS_ZGSHB_REFERENCE_RY_JBXX;        alter table WS_JKQK disable constraint FK_WS_JKQK_REFERENCE_RY_JBXX;        alter table WS_YBJZQK disable constraint FK_WS_YBJZQ_REFERENCE_RY_JBXX;

3. Enable all foreign key constraints:

        select 'alter table ' || table_name || ' enable constraint ' || constraint_name ||';' from user_constraints where constraint_type = 'R';

Generate the following SQL statement (the table name depends on the actual situation ):

        alter table RS_ZGSHBX enable constraint FK_RS_ZGSHB_REFERENCE_RY_JBXX;        alter table WS_JKQK enable constraint FK_WS_JKQK_REFERENCE_RY_JBXX;        alter table WS_YBJZQK enable constraint FK_WS_YBJZQ_REFERENCE_RY_JBXX;

4. Delete all foreign key constraints:

        select 'alter table ' || table_name || ' drop constraint '|| constraint_name || ';' from user_constraints where constraint_type = 'R';

Generate the following SQL statement (the table name depends on the actual situation ):

        alter table RS_ZGSHBX drop constraint FK_RS_ZGSHB_REFERENCE_RY_JBXX;        alter table WS_JKQK drop constraint FK_WS_JKQK_REFERENCE_RY_JBXX;        alter table WS_YBJZQK drop constraint FK_WS_YBJZQ_REFERENCE_RY_JBXX;

Iv. Reset the Initial sequence Value

 

In Oracle, the primary key often uses the specified sequence to achieve automatic growth. There is no problem in itself, but when we use truncate to clear tens of millions of data, and the sequence increases by 1 each time, delete the id of the data inserted next time is more than 10 million. This is not what we want. in Oracle, when auto-incrementing sequence is reset to initial 1, this method has many drawbacks. The functions and stored procedures that depend on it will become invalid and need to be re-compiled. However, there is a clever way to do this without deleting it. You can use the step size parameter to first find the nextval of sequence, remember to change the increment value to the negative value (which goes in turn), and then change it back.

Assume that the sequence name to be modified is seq_name:

create or replace procedureseq_reset(v_seqname varchar2) as n number(10);       tsqlvarchar2(100);begin       execute immediate'select '||v_seqname||'.nextval from dual' into n;        n:=-(n-1);        tsql:='altersequence '||v_seqname||' increment by '|| n;        execute immediatetsql;        execute immediate'select '||v_seqname||'.nextval from dual' into n;        tsql:='altersequence '||v_seqname||' increment by 1';        execute immediatetsql;end seq_reset;


Call method:


declare       seq_namevarchar2(100); begin       seq_name :='seq_ry_jbxx';      seq_reset(seq_name); end;

 

5. Use truncate with foreign key constraints

 

As mentioned earlier, FOREIGNKEY (foreign key) exists) the restricted table cannot use the TRUNCATE command. However, you can disable the foreign key of the table, use the TRUNCATE command to clear table data, and restore the foreign key.

1. generate statements for disabling Foreign keys and export SQL statements.

2. Generate and export SQL statements that enable foreign key constraints.

3. reorganize the new SQL file.

4. Add the sequence: seq_ry_jbxx corresponding to the RY_JBXX table to the new SQL file and reset it.

5. Complete SQL file content (seq_reset is the stored procedure generated in Step 4, which is called directly here ):

        alter table RS_ZGSHBX disable constraintFK_RS_ZGSHB_REFERENCE_RY_JBXX;        alter table WS_JKQK disableconstraint FK_WS_JKQK_REFERENCE_RY_JBXX;        alter table WS_YBJZQK disableconstraint FK_WS_YBJZQ_REFERENCE_RY_JBXX;          truncate tableRY_JBXX;        alter table RS_ZGSHBX enable constraintFK_RS_ZGSHB_REFERENCE_RY_JBXX;        alter table WS_JKQK enableconstraint FK_WS_JKQK_REFERENCE_RY_JBXX;        alter table WS_YBJZQK enableconstraint FK_WS_YBJZQ_REFERENCE_RY_JBXX;         select count(*) from ry_jbxx;         declare               seq_name varchar2(100);         begin               seq_name :='seq_ry_jbxx';               seq_reset(seq_name);         end;


 

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.