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.

By Laurent Schneider

Oracle Certified Master


  1. 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. 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

Leave a comment

Your email address will not be published.