Oracle quickly clears table data

Oracle quickly clears table data
A: Introduction

Delete table data generally there are three commands, truncate, delete, drop, but their execution, efficiency and results are different, such as deleting the 500W record level of data, the three are very different.

Two: Truncate delete drop

All three use can be used to clear the table data, its similarities and differences are as follows

Same point:

1.truncate and delete without a WHERE clause, and drop deletes the data in the table.

2.drop, truncate are DDL statements (data definition language) that are automatically committed after execution.

Different points:

1. Truncate and delete only delete data does not delete the structure of the table (definition) The DROP statement will delete the table's structure dependent constraints (constrain), triggers (trigger), indexes (index), and stored procedures/functions that depend on the table are preserved, But becomes invalid state.

2. The DELETE statement is a database manipulation language (DML), which is placed in the rollback segment, which takes effect after the transaction is committed, and is triggered when the corresponding trigger is executed. Truncate, drop is the database definition language (DDL), the operation takes effect immediately, the original data is not placed in the rollback segment, cannot be rolled back, the operation does not trigger trigger.

The 3.delete statement does not affect the extent occupied by the table, the high waterline (higher watermark) keeps the original position, and the drop statement frees all the space occupied by the table. The TRUNCATE statement frees the space to minextents extent by default, unless using reuse storage;truncate resets the high watermark (back to the beginning).

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

5. Security: Be careful with drop and truncate, especially when there is no backup. Otherwise, you can not cry, use, want to delete some data rows with delete, pay attention to the WHERE clause. The rollback segment is large enough. To delete a table, of course, delete all the data with drop, want to keep the table, if it is not related to the transaction, use truncate. If it is related to a transaction, or if you want to trigger trigger, use Delete. If you are defragmenting the inside of the table, you can use truncate to keep up with reuse stroage and re-import/insert the data.

6.delete is a DML statement and is not automatically committed. Drop/truncate are DDL statements that are automatically committed after they are executed.

7. TRUNCATE table is functionally the same as a DELETE statement without a 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 one row at a time and records an entry in the transaction log for each row that is deleted. TRUNCATE table deletes data by releasing the data page used to store the table data, and records the release of the page only in the transaction log.

8. TRUNCATE table deletes all rows in the table, but the table structure and its columns, constraints, indexes, and so on, remain unchanged. The count value used for the new row identity is reset to the seed of the column. If you want to preserve the identity count value, use DELETE instead. If you want to delete the table definition and its data, use the DROP table statement.

9. For a table with a FOREIGN KEY constraint reference, you cannot use TRUNCATE table, and you should use a DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not recorded in the log, it cannot activate the trigger.
10. TRUNCATE table cannot be used for tables that participate in an indexed view.

Three: Oracle FOREIGN key operation

1. To view foreign keys for 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 (table name depends on the case):

        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 (table name depends on the case):

        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 (table name depends on the case):

        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;

Four: Reset sequence initial value

Oracle, primary keys often use the specified sequence to achieve autogrow, itself is not a problem, but when we use truncate to clear TENS data, sequence each growth of 1, the next time after the deletion of the data, its ID is more than tens of millions of , which is generally not what we want, Oracle generally resets the self-increment sequence to the initial 1 o'clock, is removed and rebuilt, this way has many drawbacks, the function and stored procedures that depend on it will be invalidated and need to be recompiled. But there is a kind of ingenious way, do not delete, use the step parameter, first find out the sequence nextval, remember, the increment to negative this value (in turn), and then change back.

Suppose you need to modify the sequence name: Seq_name:

Create or replace Procedureseq_reset (V_seqname varchar2) as n number (ten);       TSQLVARCHAR2 (+); 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 Mode:

DECLARE       seq_namevarchar2 (+); begin       Seq_name: = ' seq_ry_jbxx ';      Seq_reset (Seq_name); End

V: Use TRUNCATE with foreign KEY constraints

As mentioned earlier, tables with ForeignKey (foreign key) constraints cannot use the TRUNCATE command, but you can do this by disabling the foreign key of the table, and then using TRUNCATE to clear the table data and finally restore the table foreign key.

1. Generate statements that disable foreign keys, and export SQL.

2. Generate Enable FOREIGN KEY constraint statement, and export SQL.

3, re-organized into a new SQL file.

4, in the new SQL file above added to the RY_JBXX table corresponding to the sequence:seq_ry_jbxx to reset.

5. Full SQL file content (where Seq_reset is a stored procedure generated in four, directly called 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 (+);         Begin               Seq_name: = ' seq_ry_jbxx ';               Seq_reset (seq_name);         End

Oracle quickly clears table data