if I create a function for a materialized view with query rewrite or for a function based index, I must create hte deterministic.

f(x) = x*2 is deterministic. for a give x, f(x) will always be the same, f(5) will be always 10; always.

f(y) = sysdate+y is non-deterministic. For a given y, the return values may vary.

OK, we cannot use sysdate in mviews. What else should I say?

Well, some are less evident to find out!

Some limit cases:
to_date(‘2000′,’YYYY’) is non deterministic, it returns the 1st of current month, 2000, f. ex. 2000-07-01 or 2000-08-01
to_char(date ‘2000-01-01′,’DAY’) is non deterministic, it can deliver SATURDAY or SAMSTAG
to_char(date ‘2000-01-01′,’DAY’,’NLS_DATE_LANGUAGE=american’) is deterministic
to_char(date ‘2000-01-01′,’D’) is non deterministic, it can deliver 6,7,1 or 2, depending what your territory is (in Bangladesh, the week starts on friday)
timestamp ‘2005-10-30 02:30:00 Europe/Zurich’ is per default non deterministic, if you try it in summer, you will get time offset +02:00, and in winter you will get +01:00, because the period 02:00-02:59, Oct 30th, exists in both time zones. This is called time boundaries. I can make it deterministic by setting ERROR_ON_OVERLAP_TIME in the session, in which case boundaries will be rejected.

I can always define a function as deterministic, at my own risk…

For exemple if I have t(id,gender) {1,male;2,female}, I could have a function

f(id)=select gender from t where;

and I could define it as deterministic. However, if I decide to set id=2 for female later, I am prepared to have corrupted data, wrong results, inconsistencies and even ORA-600 errors.

3 thoughts on “deterministic

  1. Rich J

    You can use “deterministic” dates in fast-refresh MVs, with the caveat that a fast refresh can take as long or longer than a full refresh when the date changes.

    The way to get around the restriction (I did this on is to put the formatted date into a single-row table. The problem is that if that row changes, all occurrences of it in your MV’s query will need to be recalculated. In our case, I was using the date to determine aged buckets (30-day, 60-day, 90-day, etc) and refreshing the MV daily.

    Trials showed that a full refresh nightly was faster than a fast refresh. But I kept the implementation anyway in case the MV needed to be refreshed during the day, where it would take seconds instead of an hour (estimated).


  2. Laurent Schneider

    ok, this is a way to do it.
    the problem with to_char(date,’d’) is that it cannot be made deterministic, well, you could use another calculation like trunc(date,’IW’) but I expected to have NLS_DATE_TERRITORY in to_char before oracle 15 😉

  3. Pingback: sql date

Comments are closed.