ORACLE SQL UPDATE DELETE INSERT optimization and use skills sharing

UPDATE

1. Back up the data first (safe and improve performance).

2. Update in batches and submit in small batches to prevent locking tables.

3. If the updated data is automatically indexed and the amount of updated data is very large, cancel the index first and then recreate it.

4. Update the whole table data. If the table is very large, it is recommended to create a new table instead of updating.

DELETE

1. Submit in batches to reduce the time of locking tables and the pressure of rollback section.

2. Mass data deletion plus rownum < 1000.

3. Delete in large quantities. It is forbidden to adjust ctrl+c or kill to session in the middle.

4. After deleting a large amount of data, it is best to rebuild the index and analyze the table.

INSERT

1. Turn off redo log (ALTER TABLE) < TABLENAME > nologging;)

2. Use /*+ append */to imply.

3. Bind variables.

4. Batch submission.

5. If the table is static and used exclusively, the index and other related constraints can be removed.

6. Analyze the table after pouring a large amount of data.

7. Use SQL load to quickly load data.