Couple of weeks back I read [url: http://tkyte.blogspot.in/2006/01/yet-another-new.html]this article from Great Tom, the article was really interesting and made me to share it with many of my colleagues and friends.
As soon as I finished reading it, I started implementing it in my application too, to see some miracles happen.However I faced some bumps during this implementation.
An analogy
SQL> drop sequence r_dummy_seq;
Sequence dropped.
SQL>
SQL> drop table r_dummy;
Table dropped.
SQL>
SQL> drop function get_next_val;
Function dropped.
SQL>
SQL> create table r_dummy as select mod(level,2) a from dual connect by level <= 10;
Table created.
SQL>
SQL> create sequence r_dummy_seq;
Sequence created.
SQL>
SQL> create or replace function get_next_val
2 (
3 p_in_number in number
4 )
5 return number
6 is
7 l_ret number;
8 begin
9 dbms_application_info.set_client_info(userenv('client_info')+1);
10
11 if p_in_number is not null then
12
13 select r_dummy_seq.nextval
14 into l_ret
15 from dual;
16
17 end if;
18
19 return l_ret;
20 end;
21 /
Function created.
SQL>
SQL> exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.
SQL>
SQL> select userenv('client_info') from dual;
USERENV('CLIENT_INFO')
----------------------------------------------------------------
0
SQL>
SQL> select a,get_next_val(r.a) nextval from r_dummy r;
A NEXTVAL
---------- ----------
1 1
0 2
1 3
0 4
1 5
0 6
1 7
0 8
1 9
0 10
10 rows selected.
SQL>
SQL> select userenv('client_info') from dual;
USERENV('CLIENT_INFO')
----------------------------------------------------------------
10
SQL>
SQL> exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.
SQL>
SQL> select a,(select get_next_val(r.a) from dual) nextval from r_dummy r;
A NEXTVAL
---------- ----------
1 11
0 12
1 11
0 12
1 11
0 12
1 11
0 12
1 11
0 12
10 rows selected.
SQL>
SQL> select userenv('client_info') from dual;
USERENV('CLIENT_INFO')
----------------------------------------------------------------
2
The results are exactly what anyone don't want.I see same data (only 2 distinct sequence values for two distinct column values) getting repeated for all the rows. Why its returning same/wrong sequence number?, when I didn't promised it to return same answer for the given input( As you can see I haven't marked my function as DETERMINISTIC).
I came to know that my results are getting cached without my interest, why should they be cached? and moreover why should it return wrong results, when I didn't lie to Oracle saying that my results are constant, predicable, deterministic for the given input.
I feel this is more of a bug, we can't expect results to be cached/wrong just because rewriting SQL in another legitimate way.
Its unfortunate that everyone is allowed to code/wrap their functions with "SELECT FROM DUAL" causing them to mess up with wrong results. Although there are many people with rich skills in performance tuning and working on oracle from decades (not me ;)), still there are many who are not aware of this strange caching behavior .
The article/feature is really good for DETERMINISTIC functions. However I wish there must be a warning/error thrown talking about the limitation before using this.
I request Oracle to bring some kind of limitation on this, presumably it should throw an ORA-30553 error or at least a warning message, asking to mark the function as DETERMINISTIC before they can wrap it with
"SELECT FROM DUAL".And yes, something similar to functional indexes where we have a prerequisite to mark the function as "DETERMINISTIC" (leaving risk to the developer).
As always, urging for expert's voice on this.