Home » SQL & PL/SQL » SQL & PL/SQL » Deterministic (Oracle 10g)
Deterministic [message #440867] Wed, 27 January 2010 10:08 Go to next message
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 #440869 is a reply to message #440867] Wed, 27 January 2010 10:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What is Deterministic Function

A function that always returns the same output for the same parameters value.

Quote:
when we use deterministic function please give me one real time scenario.

For instance to create a function-based index.

Quote:
And what is advantage of Deterministic function over normal function.

None but the ones I mentioned.

Quote:
I studied something in google but i didn't understand

What did you study? And what didn't you understand?

Regards
Michel
Re: Deterministic [message #441000 is a reply to message #440867] Thu, 28 January 2010 05:18 Go to previous messageGo to next message
pointers
Messages: 410
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 Go to previous messageGo to next message
pablolee
Messages: 2834
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 Go to previous message
Kevin Meade
Messages: 2101
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
Previous Topic: Stream URL to BLOB (merged 5)
Next Topic: Cursor
Goto Forum:
  


Current Time: Thu Dec 08 01:59:22 CST 2016

Total time taken to generate the page: 0.08830 seconds