Oracle Magazine - November/December 2010

before you read the last record with a $300 BALANCE—someone did the $100 transfer? Without read consistency, if you just read whatever value you found in the last row ($400 in this example), the SUM(BALANCE) returned would be $100 more than ever existed. That would be a rather obvious error on a balance sheet. Instead of returning that erroneous value, Oracle Database returns a SUM(BALANCE) value that incorporates BALANCE values as of the same point in time. The answer you get back from Oracle Database incorporates the $300 balance for account 789, not the “modified after you began” $400 balance.

Now, let’s suppose you are executing a simple query such as SELECT a, b, c, f(x) FROM t, where a, b, c, and x are columns of a table t and f is a PL/SQL function you’ve written that contains its own SQL statements. And you are seeing what appear to be inconsistent results—that is, the data returned for the first row is inconsistent with the data returned for the last row, something that sounds impossible in Oracle Database. Well, the rules of read consistency have not been violated here: every query executed in this case will be read-consistent. However, each query executed will be read-consistent with respect to itself by default. If you use SQL to execute a function that in turn runs its own SQL, each query executed is by default consistent with itself but not with the original driving SQL. I can demonstrate this rather easily by using a copy of the EMP and DEPT tables from the SCOTT schema. I know there should be a grand total of 4 departments in the DEPT table and 14 employees in the EMP table— 3 in DEPTNO 10, 5 in DEPTNO 20, 6 in DEPTNO 30, and 0 in DEPTNO 40. I write a little logic in PL/SQL to count the employees by department (a PL/SQL function that does some SQL to return a result):

Code Listing 1: The optimizer knows that X is NOT NULL.

SQL> set autotrace on explain SQL> create index t_x_idx on t(x); Index created.

SQL> select /*+ index_ffs( t t_x_idx ) */ count(*) from t;

COUNT(*) ———————————————

0

————————————————————————————————————————————————————————————————————————————————————————————————————————————— | Id | Operation | Name | Rows | Cost (%CPU)| Time | ————————————————————————————————————————————————————————————————————————————————————————————————————————————— | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| T_X_IDX | 1 | 2 (0)| 00:00:01 | —————————————————————————————————————————————————————————————————————————————————————————————————————————————

7 from emp

8 where emp.deptno = p_deptno; 9

10 do_work_in_another_transaction(); 11

12 return l_cnt; 13 end;

14 / Function created.

9 end loop; 10 commit;

11 end;

12 / Procedure created.

Note that this function (F) calls another
procedure: DO_WORK_IN_ANOTHER_
TRANSAC TION(). This is a stored procedure
I wrote just for this example that simulates
a situation in which other concurrent users
in the database are doing some work (modi-
fying the data I am reading) and committing
that work. This represents real life, where
other users are in the database making
modifications to the data I am currently
reading. Here is the implementation of the
DO_WORK_IN_ANOTHER_TRANSACTION()
stored procedure:
Every time the procedure is called, I
“hire” a new set of employees—one each
in each of the departments—and then
commit. Now, let’s query my data. I’ll select
the DEPTNO and DNAME columns from
DEPT and invoke my function (F) to get
the count by DEPTNO. To show the effects
of read consistency, I’ll also get the “true”
count with a scalar subquery—I’ll just take
the SELEC T COUNT(*) from my function (F)
and invoke that directly in the query itself: