Oracle SQL-UPDATE, DELETE, INSERT optimization and usage tips

UPDATE

1. Back up data first (security and performance improvement ).

2. Batch update and batch commit to prevent lock tables.

3. If the updated index automatically has a large volume of data, the index will be canceled first and then re-created.

4. Full table data update. If the table is very large, we recommend that you replace the update in the form of a new table.

DELETE
 
1. Batch commit, reduce the lock table time, and reduce the pressure on rollback segments.

2. Add rownum <1000 to delete a large volume of data.

3. Batch deletion. Do not press ctrl + c or kill to call the session.

4. It is best to re-create the index and analysis table after deleting a large amount of data.

INSERT

1. Disable redo log (alter table <TABLENAME> nologging ;)

2. Use/* + append */suggestion.

3. bind variables.

4. Batch submit.

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

6. Analyze the table after a large amount of data is filled in.

7. Use SQL load to quickly load data.