Bulk Processing in Oracle

 Bulk Processing with BULK COLLECT and FORALL

BULK COLLECT: SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval

FORALL: INSERTs, UPDATEs, and DELETEs that use collections to change multiple rows of data very quickly

BULK COLLECT

declare
  type t_plsql_type is table of employee%rowtype index by pls_integer;
  vt_plsql_type t_plsql_type;                      
begin

   select * bulk collect into vt_plsql_type from  employee ;

   for v_typ_idx in 1..vt_plsql_type.count loop
       dbms_output.put_line(vt_plsql_type(v_typ_idx).emp_id);
   end loop;       

end ;  

FORALL

Context Switches and Performance

Context Switches and Performance

Almost every program PL/SQL developers write includes both PL/SQL and SQL statements. PL/SQL statements are run by the PL/SQL statement executor; SQL statements are run by the SQL statement executor. When the PL/SQL runtime engine encounters a SQL statement, it stops and passes the SQL statement over to the SQL engine. The SQL engine executes the SQL statement and returns information back to the PL/SQL engine   This transfer of control is called a context switch, and each one of these switches incurs overhead that slows down the overall performance of programs

Bulk Processing in PL/SQL

The bulk processing features of PL/SQL are designed specifically to reduce the number of context switches required to communicate from the PL/SQL engine to the SQL engine

Cursor Execution Cycle

  Detailed phase of cursor execution


 1. The cursor is opened
 2. Parse SQL
 3. The query is described to know the projection (what columns are going to be      
    returned, names, datatypes etc.)
 4. Bind SQL (Variable Binding)
 5. SELECT Query execution
 6. Move the record pointer to the first record


     FETCH


 7. A row of data is fetched 
 8. The data values from the columns within that row are extracted into the known    
      projection 
 9. Step 6 and 7 are repeated until there is no more data or another condition ceases
       the fetching

10. The cursor is closed

Restore point

 A restore point is an attribute of a database, not a transaction, so you can generate many transactions and still restore the entire database back to the restore point.  

Restoring to a restore point, however, will affect every change that had been made to the database between the time that you defined the restore point and the time that you do restore  you would lose all changes made not just by your session or your user but all sessions and users in the database. 


CREATE RESTORE POINT statement to create a restore point, which is a name associated with an SCN of the database corresponding to the time of the creation of the restore point.

A restore point can be used to flash a table or the database back to the time of creation of the restore point without the need to determine the SCN or timestamp.

SQL> CREATE RESTORE POINT before_upgrade;

Guaranteed restore points never age out of the control file and must be explicitly dropped.

SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

SQL> DROP RESTORE POINT before_app_upgrade;

V$RESTORE_POINT

CREATE RESTORE POINT good_data;

SELECT salary FROM employees WHERE employee_id = 108;

    SALARY
----------
     12000

UPDATE employees SET salary = salary*10 WHERE employee_id = 108;

SELECT salary FROM employees WHERE employee_id = 108;

    SALARY
----------
    120000

COMMIT;

FLASHBACK TABLE employees TO RESTORE POINT good_data;

SELECT salary FROM employees WHERE employee_id = 108;

    SALARY
----------
     12000

SAVEPOINT

SAVEPOINT identify a point in a transaction to which you can later roll back.

Example

create table countries (region_id number,region_name varchar2(100),country_id varchar2(100)) ;
insert into COUNTRIES (REGION_ID, REGION_NAME, COUNTRY_ID) values (33, 'Middle East', null);
insert into COUNTRIES (REGION_ID, REGION_NAME, COUNTRY_ID) values (5, null, 'NG');

UPDATE countries SET region_name = 'Middle East' WHERE region_name = 'Middle East and Africa';

SAVEPOINT reg_rename;

UPDATE countries SET country_id = 'ZM'  WHERE region_id = 5;

SAVEPOINT zambia;

UPDATE countries SET country_id = 'NG'  WHERE region_id = 5 ;

SAVEPOINT nigeria;

UPDATE countries SET country_id = 'ZW'  WHERE region_id = 5;

SAVEPOINT zimbabwe;

UPDATE countries SET country_id = 'EG' WHERE  region_id = 5;

SAVEPOINT egypt;

ROLLBACK TO SAVEPOINT nigeria;

COMMIT;