| Deterministic [message #440867] |
Wed, 27 January 2010 10:08  |
prakashaa
Messages: 31
Registered: November 2009
Location: Bangalore
|
Member |
|
|
Hi Experts
What is Deterministic Function,when we use deterministic function please give me one real time scenario.
And what is advantage of Deterministic function over normal function.
I studied something in google but i didn't understand,Please help me on this with clear example.
Thanks in Advance.
|
|
|
|
|
|
| Re: Deterministic [message #441000 is a reply to message #440867] |
Thu, 28 January 2010 05:18  |
pointers
Messages: 451
Registered: May 2008
|
Senior Member |
|
|
In addition to Micheal post..
Deterministic functions always return the same result any time they are called with a specific set of input values.
Nondeterministic functions may return different results each time they are called with a specific set of input values
e.g for nondetrministic function is sysdate
Regards,
Pointers
|
|
|
|
| Re: Deterministic [message #441005 is a reply to message #441000] |
Thu, 28 January 2010 05:43 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
Quote:In addition to Micheal post
Hmmm,
Quote:Deterministic functions always return the same result any time they are called with a specific set of input values.
Michel already said this.
Quote:Nondeterministic functions may return different results each time they are called with a specific set of input values
this would be implicit based on the previous statement.
|
|
|
|
| Re: Deterministic [message #441211 is a reply to message #440867] |
Fri, 29 January 2010 08:34 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Here is a simplistic example:
create or replace function this_is_deterministic (in_p in number) return varchar2 is
begin
if in_p = 1 then return 'a';
elsif in_p = 2 then return 'b';
else return 'c';
end if;
end;
/
show errors
create or replace function not_deterministic (in_p in number) return date is
begin
return (sysdate-in_p);
end;
/
show errors
SQL> select this_is_deterministic(1),not_deterministic(1) from dual
2 /
THIS_IS_DETERMINISTIC(1) NOT_DETERMINISTIC(1)
------------------------------ --------------------
a 28-jan-2010 09:08:56
1 row selected.
SQL> select this_is_deterministic(1),not_deterministic(1) from dual
2 /
THIS_IS_DETERMINISTIC(1) NOT_DETERMINISTIC(1)
------------------------------ --------------------
a 28-jan-2010 09:08:57
1 row selected.
Notice that for the first function, everytime you pass in the value 1, you will always get back the value "a".
Notice that for the second function, when you pass in the value 1, the returned value will most likely be different between function invocations. Forgetting the stupidity of the function, just understand why even though the same parameter value was used above, the returned value was different. This is because the result depends upon the data in the database and the data in the database can change between function invocations.
The first functions result does not depend on data that will change. Thus the same inputs always return the same outputs.
The second function returns a result that depends upon data which can change. Thus the same inputs are not guaranteed to return the same outputs.
The first is deterministic because the inputs "DETERMINE" the output.
The second function is NOT deterministic because the inputs alone and not sufficient to determine the output.
Does this do it for you?
The reason this distiction is important is that deterministic functions can be cached. Thus oracle can without your code knowing it, remember the value returned for a determistic function call and corresponding set of values. Consider the scenario where you are using a deterministic function inside a sql query. There will be many times a call to this function will have the same input across rows. If oracle knows the function is deterministic, it can skip a function call across rows thus speeding up the query.
select deterministicfunction(deptno) from emp;
For the above query, the function call will return the same result for the same deptno value. Consider this data:
empno deptno
101 10
102 10
999 10
There are some 999 employees all who work in department 10. How many times must oracle call the function? If the function is NOT deterministic then oracle must call the function at least once per row which in this case means 999 times. If the function IS deterministic oracle can at its discretion call the function only once per deptno value which in this case means once instead of 999 times. This can add up considerably if one uses lots of function calls in their code.
I believe there are two ways a function becomes deterministic.
1) oracle figures it out for itself.
2) you tell oracle the function is deterministic.
This can make things interesting. For consider that you can lie to oracle. You can tell it a function is deterministic even when it is not. This will cause oracle to cache the results of calls to the function you said was deterministic and can result in what some might consider unusual behavior. Deterministic functions are related to oracle new query caching capability in 11g.
I wonder if someone has a rationale for tagging a function as deterministic even when it is not so. Anyone care to comment?
Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
