20 September, 2009

SQLs in Functions : Each Execution is Independent

Functions (Stored PLSQL code that return a single value at each call) are good for calculations, validation against business rules etc. But when you use them for lookups, you must watch out for underlying data being changed !

Thus, if you have a statement like "SELECT col_1, my_function(col_2) FROM ...", the function "my_function" is executed from every row that is returned from the query. Oracle guarantees Read Consistency at the Statement Level. However, insofar as the Function is executed, each execution of the Function is a separate SQL statement executed against the database. If underlying data has been changed in transaction from a different database session, the Function may start returning different values mid-way in a result set.


Here's a demonstration of how a Function being used for a lookup returns different values for the same lookup, within a single SQL statement.


SQL> drop table my_obj_types_lookup purge;

Table dropped.

SQL> drop table obj_names_and_descr purge;

Table dropped.

SQL>
SQL> -- create the Lookups table
SQL> create table my_obj_types_lookup
2 (obj_type varchar2(30) not null, obj_descr varchar2(50) not null)
3 /

Table created.

SQL> insert into my_obj_types_lookup select distinct object_type, 'Is an ' || object_type from dba_objects;

42 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> -- REM Create a function that does Lookups
SQL> create or replace function lookup_obj_descr (obj_type_in varchar2)
2 return varchar2
3 as
4 obj_type_ret varchar2(50);
5 begin
6 select obj_descr into obj_type_ret from my_obj_types_lookup where obj_type = obj_type_in;
7 return obj_type_ret;
8 exception when others then return 'Unknown object type';
9 end;
10 /

Function created.

SQL>
SQL> -- REM create the target table
SQL> create table obj_names_and_descr
2 (obj_owner varchar2(30) not null, obj_name varchar2(128) not null, obj_type varchar2(30) not null, obj_descr varchar2(60) not null);

Table created.

SQL>
SQL> pause PRESS ENTER TO PROCEED
PRESS ENTER TO PROCEED

SQL> -- Now we populate OBJ_NAMES_AND_DESCR using our Lookup Function !
SQL> insert into obj_names_and_descr
2 select owner, object_name, object_type, lookup_obj_descr (object_type)
3 from dba_objects
4 order by owner
5 /

50639 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> -- REM Let's verify the count of different types of objects
SQL> select obj_type, obj_descr, count(*)
2 from obj_names_and_descr
3 where obj_type like 'TAB%'
4 group by obj_type, obj_descr
5 order by 1,2
6 /

OBJ_TYPE OBJ_DESCR COUNT(*)
------------------------------ ------------------------------------------------------------ ----------
TABLE Is an TABLE 897
TABLE OOPS ! 759
TABLE PARTITION Is an TABLE PARTITION 128

SQL>
SQL>
SQL> -- REM REM REM #!#!
SQL> -- REM Hey ! Where did the "OOPS !" rows come from ? My INSERT INTO ... SELECT should have given my Read Consistency
SQL> -- REM Therefore, every TABLE should have the same OBJ_DESCR !
SQL>


Surely, the "insert into obj_names_and_descr" should have "seen" a Read Consistent image of data ? Yes. The image from the dba_objects view would have been consistent. However, the lookup function "lookup_obj_descr" was executed once against "my_obj_types_lookup" for *each* row from the query against dba_objects. Each execution of the function was independent of the other executions. It so happens that the underlying table "my_obj_types_lookup" was updated by someone else running :

SQL> update my_obj_types_lookup set obj_descr = 'OOPS !' where obj_type = 'TABLE';

1 row updated.

SQL> commit;

Commit complete.

SQL>

mid-way through the INSERT ... SELECT operation.

Therefore, although some rows that were returned from dba_objects saw the obj_descr as "Is an TABLE" for obj_type = 'TABLE' , rows that were still being fetched from dba_objects after the other session committed his update to 'OOPS !', now saw the new value 'OOPS !' as being the description.

Therefore, the SELECT portion of the INSERT actually returned different values for obj_descr when using the Function !

.
.
.

7 comments:

Alex Nuijten said...

Isn't this why "they" created the Deterministic Function?

Hemant K Chitale said...

Something to explore. Though I haven't seen very many DETERMINISTIC functions.

Anonymous said...
This comment has been removed by a blog administrator.
Log Buffer said...

"Hemant’s Oracle DBA Blog featured an item on SQLs in functions: each execution is independent. [...]"

Log Buffer #163

Rahul said...

Hi,

Nice and interesting article. Thanks a lot.

Thanks and regards,
Rahul Jain
http://www.dbametrix.com

Unknown said...

So, what should we do for that?

Is there a workaround?

Does it mean that we should be very careful for using functions within sql statement?

Hemant K Chitale said...

Functions have a place. However, a function that is written for a single lookup should not be used for very large data sets. Retain the function but rewrite the report to use a join instead of calling the function.

Hemant K Chitale