deterministic
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 t.id=f.id;
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.
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 t.id=f.id;
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.
Posted by Laurent Schneider at 7/22/2005 03:37:00 PM
Permalink : deterministic
Post to :
del.icio.us -
furl -
digg -
reddit -
co.mments
2 Comments:
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 9.2.0.5.0) 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).
Rich
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 ;-)
Post a Comment
<< Home