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.