How to bind 10 Gb of Oracle to forall bulk collect in batches

Source: Internet
Author: User

The following articles mainly introduce how to bind 10 Gb of Oracle to forall bulk collect in batches, we all know that batch binding generally reduces context switches between PL/SQL and SQL engines to improve performance. Bulk binds can be bound in batches:

(1) Input collections, use the FORALL statement to Improve the Performance of DMLINSERT, UPDATE, and DELETE operations.

(2) Output collections, use bulk collect clause, used to improve the performance of SELECT queries.

Oracle 10 Gb forall statement can start in three ways:

In low... up

In indices of collection gets the value of the lower mark of the Set element.

In values of collection gets the value of the Set element.

The forall statement can also use some set elements.

SQL % bulk_rowcount (I) indicates the number of rows that the forall statement applies to element I.

 
 
  1. --drop table blktest;  
  2. --CREATE TABLE blktest (num NUMBER(20), name varchar2(50));  
  3. --CREATE OR REPLACE PROCEDURE p_bulktest IS  
  4. DECLARE  
  5. TYPE type_num IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;  
  6. TYPE type_name IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;  
  7. tab_num type_num;  
  8. tab_name type_name;  
  9. t1 NUMBER;  
  10. t2 NUMBER;  
  11. t3 NUMBER;  
  12. BEGIN  
  13. FOR i IN 1 .. 500000 LOOP  
  14. tab_num(i) := i;  
  15. tab_name(i) := 'name: ' || to_char(i);  
  16. END LOOP;  
  17. SELECT dbms_utility.get_time    
  18. INTO t1    
  19. FROM dual;  
  20. FOR i IN 1 .. 500000 LOOP  
  21. INSERT INTO blktest   
  22. VALUES  
  23. (tab_num(i), tab_name(i));  
  24. END LOOP;  
  25. SELECT dbms_utility.get_time    
  26. INTO t2    
  27. FROM dual;  
  28. FORALL i IN 1 .. 500000  
  29. INSERT INTO blktest   
  30. VALUES  
  31. (tab_num(i), tab_name(i));  
  32. SELECT dbms_utility.get_time    
  33. INTO t3    
  34. FROM dual;  
  35. dbms_output.put_line('Execution Time(S)');  
  36. dbms_output.put_line('-------------------');  
  37. dbms_output.put_line('FOR loop: '   
  38. || to_char((t2 - t1) / 100));  
  39. dbms_output.put_line('FORALL:  '   
  40. || to_char((t3 - t2) / 100));  
  41. END;  
  42. /*  
  43. Execution Time(S)  
  44. FOR loop: 32.78  
  45. FORALL:  2.64  
  46. */  
  47. /*  

Bulk collect statement

Used to obtain batch data. It is applicable only to the return clauses of select into, fetch into, and DML statements.

 
 
  1. DECLARE  
  2. TYPE type_emp IS TABLE OF   
  3. scott.emp%ROWTYPE INDEX BY BINARY_INTEGER;  
  4. tab_emp type_emp;  
  5. TYPE type_ename IS TABLE OF   
  6. scott.emp.ename%TYPE INDEX BY BINARY_INTEGER;  
  7. tab_ename type_ename;  
  8. CURSOR c IS  
  9. SELECT *  
  10. FROM scott.emp;  
  11. BEGIN  
  12. SELECT * BULK COLLECT  
  13. INTO tab_emp  
  14. FROM scott.emp;  
  15. FOR i IN 1 .. tab_emp.COUNT LOOP  
  16. dbms_output.put_line(tab_emp(i).ename);  
  17. END LOOP;  
  18. dbms_output.new_line;  
  19. DELETE scott.emp RETURNING   
  20. ename BULK COLLECT INTO tab_ename;  
  21. FOR i IN 1 .. tab_emp.COUNT LOOP  
  22. dbms_output.put_line(tab_emp(i).ename);  
  23. END LOOP;  
  24. ROLLBACK;  
  25. OPEN c;  
  26. FETCH c BULK COLLECT  
  27. INTO tab_emp;  
  28. dbms_output.new_line;  
  29. FOR i IN 1 .. tab_emp.COUNT LOOP  
  30. dbms_output.put_line(tab_emp(i).sal);  
  31. END LOOP;  
  32. END;  
  33. */  

Batch input FORALL + batch output BULK

DECLARE, batch input FORALL + batch output BULK
 

 
 
  1. TYPE type_num IS TABLE OF NUMBER;  
  2. tab_1 type_num;  
  3. tab_2 type_num;  
  4. BEGIN  
  5. tab_1 := type_num(1, 2, 3);   
  6. FORALL i IN 1 .. tab_1.COUNT   
  7. --EXECUTE IMMEDIATE 'update t2 set idid2=id*2   
  8. where id=:1 returning id2 into :2'   
  9. --USING tab_1(i) RETURNING BULK COLLECT INTO tab_2;  
  10. update t2 set idid2=id*2 where id=tab_1(i)   
  11. returning id2 bulk collect into tab_2;  
  12. FOR i IN 1 .. tab_2.COUNT LOOP  
  13. dbms_output.put_line(tab_2(i));  
  14. END LOOP;  
  15. END;  

Related Article

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.