Cases where a NULL status is correct, real time synchronous and asynchronous information versus transactional information
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 24 Sep 2004 10:47:10 -0400
> Dear God! Oracle Financials uses NULL in end_date on fnd_login to
> indicate either the current login or an abrupt termination of the user
> session neatly making your point and potentially providing with an
This is incorrect.
A login gets a value for end_date when a graceful logout is made. The
initial assignment of NULL to end_date is correct, as there is no end_date
for a current login. When there is a graceful logout, then and only then is
there a known value for end_date. Understanding that the value is born NULL
and how it can later become defined is entirely different from imputing a
false meaning. Indeed an abrupt termination of the session from the client
side results in a situation where the end_date remains undefined, and a
crash of the database results in a situation where the end_date remains
undefined.
Interpreting the value NULL for end_date as anything other than that the
time of the end of the session is unknown is an error of logic. If someone
in the e-Business suite team has produced something that counts NULL
end_date tuples as current sessions, that is a bug, pure and simple.
Documenting that a NULL value remains after an "abrupt" termination is
correct documentation, but the NULL value continues to indicate only that
the end_date is not known.
Whoever presents a result counting null end_dates as the number of current
sessions is making an error. Now it may be a valid complaint that there is
no convenient way to tell which fnd_logins are current other than joining to
v$session and v$process, but misinterpreting the meaning of end_date is not
justified by that complaint. Of course non-NULL end_date tuples can be
filtered from consideration as current sessions, since those tuples were
definitely excluded by graceful logouts.
This actually presents an interesting case of the interpretation of real
time status information versus synchronous time based status versus
committed transaction information.
Consider that if the e-Business suite added a column "CURRENT_SESSION" to
fnd_login. That could be committed to TRUE on login, but what happens if the
session is terminated on a real computer in real time? Then the committed
status information in the database would be simply wrong. Not only is the
quality of information less than that represented by the accuracy of unknown
(NULL) in end_date, but it is actually misleading. This is an archetypal
example of the attempting to record real time status information as anything
other than information with an "as-of" date. It was true at such-and-so a
time. If you want to know whether it is true right now, go look. You can
save the answer again, and once again "as-of" your answer was right if you
observed correctly.
Now another great example case of CURRENT is for things like catalog price
lists. Informationally they can in theory be stamped with true on a status
column if a price listed is valid until it is transactionally marked
expired. Unfortunately, on price lists there is often a pre-determined valid
starting time and expiration time (for example for a sale). This is well
represented by two time columns as information. VALID_BEGINNING and
VALID_THROUGH then have very specific values and the march of time tells you
whether to include such a tuple in the candidate actual prices. (From which
you're usually getting the minimum unless you're a used car dealer.) Still,
no update to a status column is required to get the correct information
value.
Now in the case of using VALID_BEGINNING and VALID_THROUGH, it is
informationally true to use "high_values" for VALID_THROUGH, and this is
conveniently included in the same query filtering against the system
date(time) where VALID_BEGINNING is in the past and VALID_THROUGH is not in
the past. That it may cause Oracle optimization difficulties is something
that should be noted, but that is an implementation detail rather than
informational truth. It is more germane to telling you whether you should
collect histograms on the column than determining your information
expression architecture. Still, whenever you are presented with date range
where started some time ago through high values is indicative of "current"
you do have a valid option of adding and maintaining a status column
transactionally. The column adds no information (as long as there is a known
value for high_values for time and we presume a system time value can be
retrieved [both of which are true for Oracle]). However, whether the status
column is useful is determined by size, cost of maintenance, and indexing on
the negative side versus convenience and quite possibly speed on the
positive side. Note that the interesting distinction here is that high
values for time is disjoint from synchronous real time events (unless it is
possible to run the system beyond the stated high_values, which is a
violation of the definition of high_values), so that setting the status
value is transactionally valid.
Regards,
mwf
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Cases where a NULL status is correct, real time synchronous and asynchronous information versus transactional information
