Possible Oracle Performance Optimization Problems

Source: Internet
Author: User
When I read the Oracle performance optimization book, I talked about replacing in with exists and using table links to replace exists. About the former, the effect is generally obvious, and the exists efficiency is significantly higher than in.

When I read the Oracle performance optimization book, I talked about replacing in with exists and using table links to replace exists. About the former, the effect is generally obvious, and the exists efficiency is significantly higher than in.

When I read the Oracle performance optimization book, I talked about replacing in with exists and using table links to replace exists. About the former, the effect is generally obvious, and the exists efficiency is much higher than in, however, if you want to make table join more efficient than exists, you must put the tables with more records in the from clause before the tables with fewer records.

About select... bulk collect into... limit... or fetch... bulk collect into... limit... sentence:

When using the preceding sentence pattern, we usually use the for or forall loop to perform insert, update, and delete operations.

There are several for/forall loop methods, such

1st types:


For tp in tmp. FIRST... tmp. LAST loop... end loop;


2nd types:


For tp in 1... tmp. COUNT loop... end loop;


3rd types:


For tp in indecs of tmp loop... end loop;


One cause of the above 1st methods is that in select... bulk collect... limit... or fetch... bulk collect... limit... if no exit is available, the first method returns an error: ORA-06502: PL/SQL: numeric or value error. Because the tmp. FIRST access does not exist, it is null. You must handle errors. In a nested loop, the inner loop cannot use exit. This error is inevitable.

This problem does not occur in the 2nd methods, but has not been tried in the 3rd methods.

Based on the practices on the Internet, a stored procedure for batch data deletion using Bound variables is provided:


PROCEDURE RemoveBat2DjaRecords (parameter) AS type RowIdArray is table of rowid index by binary_integer; rowIds RowIdArray; BEGIN loop select rowid bulk collect into rowIds from table name where query condition and rownum <= 1000; exit when SQL % NOTFOUND; forall k in 1 .. rowIds. COUNT delete from table name where rowid = rowIds (k); commit; end loop; EXCEPTION when OTHERS then rollback; END RemoveBat2DjaRecords;


The above 1000 entries are a configurable number, which can be expanded or reduced based on your server performance.

Exit is used to jump out of the loop. Normally, exit only jumps out of the loop at the current layer, which is similar to the break statement of other programming languages. In a nested loop, If You Want To directly jump out of an external multi-layer loop from the inner loop, you can use the 'exit tag when' statement, for example:


SQL> BEGIN 2 <> 3 FOR v_outerloopcounter IN 1 .. 2 LOOP 4 <> 5 FOR v_innerloopcounter IN 1 .. 4 LOOP 6 DBMS_OUTPUT.PUT_LINE ('outer Loop counter is '7 | v_outerloopcounter | 8 'inner Loop counter is '| v_innerloopcounter); 9 exit when v_innerloopcounter = 3; 10 end loop innerloop; 11 end loop outerloop; 12 END; 13/Outer Loop counter is 1 Inner Loop counter is 1 Outer Loop counter is 1 Inner Loop counter is 2 Outer Loop counter is 1 Inner Loop counter is 3 Outer Loop counter is 2 Inner Loop counter is 1 Outer Loop counter is 2 Inner Loop counter is 2 Outer Loop counter is 2 Inner Loop counter is 3 PL/SQL procedure successfully completed.


As can be seen from the above, under normal circumstances, exit only jumps out of the cycle of the current layer.


SQL> BEGIN 2 <> 3 FOR v_outerloopcounter IN 1 .. 2 LOOP 4 <> 5 FOR v_innerloopcounter IN 1 .. 4 LOOP 6 DBMS_OUTPUT.PUT_LINE ('outer Loop counter is '7 | v_outerloopcounter | 8 'inner Loop counter is '| v_innerloopcounter); 9 EXIT outerloop WHEN v_innerloopcounter = 3; 10 end loop innerloop; 11 end loop outerloop; 12 END; 13/Outer Loop counter is 1 Inner Loop counter is 1 Outer Loop counter is 1 Inner Loop counter is 2 Outer Loop counter is 1 Inner Loop counter is 3 PL/SQL procedure successfully completed.


As shown above, exit jumps out of the outer loop.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.