Skip to Main Content
  • Questions
  • Deterministic functions and Virtual Columns

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Don.

Asked: September 12, 2017 - 4:38 pm UTC

Last updated: September 14, 2017 - 3:21 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

It seems that “DETERMINISTIC” means exactly what I think (or some of the non-oracle sources on the internet) think it does. I expected this script to fail either on the create function, create table or when running the select statements. I would have thought the sys_guid() was the epitome of "non-deterministic."

Any information is greatly appreciated.

Thanx,
Don


with LiveSQL Test Case:

and Connor said...

Thanks for the test case.

DETERMINISTIC is to handle the "reverse" case. It is *you* giving the database *better* information.

For example

SQL> create or replace
  2  function f(p int) return number is
  3  begin
  4    return sqrt(ln(p));
  5  end;
  6  /

Function created.


*I* know from my high school maths days that this function is deterministic. But if I try to use it, then the database can't be sure and blocks us.

SQL> create table t (
  2      I int,
  3      blah number as (f(I))
  4      );
    blah number as (f(I))
                    *
ERROR at line 3:
ORA-30553: The function is not deterministic



So specifying the deterministic keyword is *our* assurance to the database that the function *is* deterministic

SQL> create or replace
  2  function f(p int) return number deterministic is
  3  begin
  4    return sqrt(ln(p));
  5  end;
  6  /

Function created.

SQL> create table t (
  2      I int,
  3      blah number as (f(I))
  4      );

Table created.


and the database is going to *trust* that we know what we're talking about :-) And if we break that trust, then we can get wrong result.

In your example, the database trusted you...so when it saw repeated calls to your function it made an optimization decision and said "Well, I'll just remember the previous result of the call rather than call the function again. Because after all, its deterministic because Don *promised* me it would be".


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Don Simpson, September 13, 2017 - 4:16 pm UTC

Ah... I didn't get that from the documentation. I stopped reading after the line "A deterministic function must return the same value on two distinct invocations if the arguments provided to the two invocations are the same." If I'd read further it becomes clear. Also, I had a typo in my question. "It seems that “DETERMINISTIC” means exactly" was supposed to be "It seems that “DETERMINISTIC” doesn't mean exactly ..."

So this is a conundrum. My company wants a virtual column that acts as an active flag for a row, meaning that "NOW" (sysdate) is between the effective and expiration dates of the queried row. Because these dates can be null, the proper query it too hard for the java developers to handle (said with dripping sarcasm).

By using the DETERMINISTIC clause, I am able to create a virtual columns that "works," but I feel like we're treading into dangerous territory.

function active_flag (
    EFFECTIVE_DATE date, 
    EXPIRATION_DATE date 
    )
return integer 
DETERMINISTIC 
as
begin 
    return (
        case when sysdate between coalesce(EFFECTIVE_DATE, sysdate) and coalesce(EXPIRATION_DATE, sysdate) 
            then 1 
            else 0 
        end
        );
end;

create table GST_DOMAIN_ROLE
    (
    ROLE_ID number(19),
    DESCRIPTION varchar2(255 char),
    ACTIVE_FROM date, 
    ACTIVE_TO date, 
    PARTY_ID number(19), 
    IS_ACTIVE number(1) generated always as (active_flag(ACTIVE_FROM, ACTIVE_TO))
    );


Do you have any recommendations for a better approach?

Thanx, Don

Connor McDonald
September 14, 2017 - 3:21 am UTC


By using the DETERMINISTIC clause, I am able to create a virtual columns that "works," but I feel like we're treading into dangerous territory.

Indeed you are. Data corruption territory.

I assume you're getting purity errors

SQL> create table GST_DOMAIN_ROLE
  2  (
  3  ROLE_ID number(19),
  4  DESCRIPTION varchar2(255 char),
  5  ACTIVE_FROM date,
  6  ACTIVE_TO date,
  7  PARTY_ID number(19),
  8  IS_ACTIVE number(1) generated always as (case when sysdate between coalesce(ACTIVE_FROM, sysdate) and coalesce(ACTIVE_TO, sysdate)
  9          then 1
 10          else 0
 11      end)
 12  );
IS_ACTIVE number(1) generated always as (case when sysdate between coalesce(ACTIVE_FROM, sysdate) and coalesce(ACTIVE_TO, sysdate)
                                                   *
ERROR at line 8:
ORA-54002: only pure functions can be specified in a virtual column expression


You could use a view to workaround that.


SQL>
SQL> create table "_GST_DOMAIN_ROLE"
  2  (
  3  ROLE_ID number(19),
  4  DESCRIPTION varchar2(255 char),
  5  ACTIVE_FROM date,
  6  ACTIVE_TO date,
  7  PARTY_ID number(19)
  8  );

Table created.

SQL>
SQL> create or replace
  2  view GST_DOMAIN_ROLE as
  3  select g.* ,
  4  case when sysdate between coalesce(ACTIVE_FROM, sysdate) and coalesce(ACTIVE_TO, sysdate)
  5          then 1
  6          else 0
  7      end is_active
  8  from "_GST_DOMAIN_ROLE"  g  ;

View created.


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library