Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to improve the performance of dml". In daily operation, I believe many people have doubts about how to improve the performance of dml. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts of "how to improve the performance of dml"! Next, please follow the editor to study!

If the batch processing of large amounts of data is usually very complex and slow, there are many methods, but the common concept is: delete in batches, submit one by one. Here are some common ways to improve the efficiency of DML statements.

UPDATE

① multi-field updates use a query.

② modifies the table to NOLOGGING mode.

③ decides whether or not to pause the index according to the situation and resume after the update. Avoid index maintenance involved in the update process.

④ batch updates, submit actions in time after updating some records to avoid taking up a large number of rollback segments and temporary table space.

⑤ can create a temporary large tablespace to handle these update actions.

⑥ enlarges the sort buffer.

⑦ if the amount of data updated is close to the entire table, then indexes should not be used and full table scans should be used.

⑧ if the server has more than one CPU, then PARELLEL Hint can be used, which can greatly improve efficiency.

The parameters of the ⑨ table are very important. For tables that are updated very frequently, it is recommended to increase the value of PCTFREE to ensure that there is enough space in the data block for UPDATE.

⑩ updates the ROWID through the fast cursor update method, as follows:

DECLARE V_COUNTER NUMBER; BEGIN V_COUNTER: = 0; FOR CUR IN (SELECT A.AREA_CODE, B.ROWID ROW_ID FROM TA A, TB B WHERE A.ID = B.ID ORDER BY B.ROWID) LOOP UPDATE TB SET AREA_CODE = CUR.AREA_CODE WHERE ROWID = CUR.ROW_ID; V_COUNTER: = V_COUNTER + 1; IF (V_COUNTER > = 1000) THEN COMMIT; V_COUNTER: = 0; END IF; END LOOP; COMMIT;END

⑪ when the table to be updated is a single table or the updated field does not need to be associated with the data brought by other tables (for example, foreign key constraints), the standard UPDATE statement is selected, which has the fastest speed, the best stability, and returns the number of affected entries. If the fields in the WHERE condition are indexed, the update is more efficient. However, UPDATE is very inefficient when it comes to updating fields with associated tables. At this point, table-to-table UPDATE operations can be efficiently completed in MERGE and non-associative form.

INSERT

① modifies the table to NOLOGGING mode.

② pauses the index.

③ is inserted in APPEND mode.

④ is added to PARALLEL and is inserted in parallel.

DELETE

① is done using FORALL.

② uses ROWID or ROW_NUMBER () OVER () to efficiently delete duplicate records.

③ modifies the table to NOLOGGING mode.

At this point, the study on "how to improve the performance of dml" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.