Oracle Note--DML Statement optimization

When Oracle's DML operational performance is impacted, it is possible to consider improving new energy from the following factors.

1) Efficiency of the WHERE clause

The WHERE clause whether the conditional association uses the index, the statistical information of the maintenance example histogram, etc. to help improve the efficiency of the means

2) costs associated with indexing

Although the index improves the efficiency of the query, it brings the cost to the operation of adding and deleting. When you insert a piece of data into a table, a record is generated in the index space, and the Oracle index is maintained in the form of a B-tree. When you insert too much data, you need to adjust the nodes to restore the height of the index tree

3) Cost of the trigger

4) Cost of complete constraint

When triggering large-scale operations, the cost of trigger and completeness constraints cannot be ignored.

1. Optimization of INSERT statement:
   2. Using bulk operations

      The bulk operation is to combine multiple inserts of the same table into one insertion of multiple data at a time, reducing the overhead associated with the interaction between the database and the application.

   4. Load data directly using append

      Use append to load data directly, avoiding a single step in the normal plug-in data write cache, and writing data directly to disk. The regular insert write cache is then cached and then retrieved out of the data block in the segment space to write the data into a data space. The data block between the append is requested to be inserted. The append operation reduces contention on the cache area and the amount of Redolog logs generated.

      The performance of the system disk IO subsystem needs to be taken into account when using append hit. For slow IO subsystems It may not be possible to bring performance improvements that may even cause the system to degrade. It is also important to note that append will add a full table lock or a partition lock to the table
   6. Use parallelism to improve efficiency

      Using a parallel thread can be multiple threads to complete the insert operation to improve performance. However, disk drivers need to be taken into account. If yes, the disk drive may join parallel write requests to the serial operations in the queue. This does not lead to the improvement of the system, but may result in a decrease in the ability of multithreading to compete for resources.

   8. Use the Insert all statement to operate on multiple tables at the same time
      

      You can consider using insert all when the following data is inserted from one table to insert a different table according to different criteria. Such as

      1. Insert All
      2. When a = xxx to a (COL1,CLO2)
      3. VALUES (V1,V2)
      4. When a = xxx to B (col1,col2)
      5. VALUES (V1,V2)
      6. SELECT * FROM B
3. Delete Optimizations
   1. Use truncate to avoid using delete

      The default truncate is to reclaim space, and all dirty blocks are written back to the data file. After TRUNCATE TABLE, Oracle reclaims the index in the table and its table to the initial size and resets the HWM. So truncate table may be slower than delete

   1. Delete the entire partition directly
   2. Using tombstone
5. Update

   Update Table A A
   Set a.c1 = (select b.c1 from b b where a.id = b.ID)
   where exists (select 1 from b b where a.id = b.ID)

   Consider using the following method to update the data in the table to avoid two queries to B

   Update (Selectfromwhere=  b.id) Set = B_c1

Oracle Note--DML Statement optimization