Transactions - Expert Oracle Database Architecture - page 282

First let's create table T :
EODA@ORA12CR1> create table t
2 as
3 select *
4 from all_objects
5 where 1=0
6 /
Table created.
Now consider this PL/SQL procedure:
EODA@ORA12CR1> create or replace procedure p
2 as
3 begin
4 for x in ( select * from all_objects )
5 loop
6 insert into t values X;
7 commit;
8 end loop;
9 end;
10 /
Procedure created.
That PL/SQL code reads a record at a time from ALL_OBJECTS , inserts the record into table T and commits each
record as it is inserted. Logically, that code is the same as this:
EODA@ORA12CR1> create or replace procedure p
2 as
3 begin
4 for x in ( select * from all_objects )
5 loop
6 insert into t values X;
7 commit write NOWAIT;
8 end loop;
9
10 -- make internal call here to ensure
11 -- redo was written by LGWR
12 end;
13 /
Procedure created.
So, the commits performed in the routine are done with WRITE NOWAIT and before the PL/SQL block of code
returns to the client application, PL/SQL makes sure that the last bit of redo it generated was safely recorded to
disk—making the PL/SQL block of code and its changes durable.
In Chapter 11, we'll see the salient effects of this feature of PL/SQL when measuring the performance of reverse
key indexes. If you'd like to see how PL/SQL performs in the manner described earlier, skip there for a moment to
review the reverse key index benchmark.