I took the question to be "if I've designed myself into a hole and I need to
insert a null value for a date, what would you do instead."
Designing so that you do not need a null value in the first place is indeed
a better thing to do. The general missing values topic is way too broad for
a list and probably begins with the Codd versus Date debates and includes
the fine insights of Lex and Toon.
In the context of that broad debate your point is certainly well taken
I do take issue with the notion that you cannot ascribe a value to NULL for
a particular column of a particular table. A key case in point is using NULL
as the final status so that it drops out of a work control index. A view can
be defined to produce a symbolic value, as needed. The value of this
technique in exploiting Oracle's physical implementation to keep the search
set small for tuples you are interested in is too big to ignore.
Dealing with side effects of the implementation of the CBO is yet another
issue best dealt with on a case by case basis and subordinate to good
design. The CBO will change over time. The tricks and techniques we use
today may not be the tricks and techniques we use tomorrow. This is not to
say they are unimportant.
On Behalf Of Martin Berger
Sent: Friday, March 30, 2012 10:46 AM
Subject: Re: ORA-00001: unique constraint (MYDB.SYS_C006557) violated
I have to contradict here. Any 'default' value just to tell 'it is bogus' is
far from the spirit of relational structures.
Even the usage of NULL at all can be avoided. The big problem here is (as
Lex de Haan described) is you can not distinct, if NULL means
'Inapplicable', 'Not Yet Applicable', or 'Nice to know' (aka 'unimportant').
I try to show you some escape here.
Let's try the original table & inserts:
create table test(id int primary key, pia int, ua int, data date, ver
int); create unique index unik on test(pia int, ua , data , ver )
insert into test values (1,37,76,null,1); insert into test values
I would like to translate it into:
create table test(id int primary key, pia int, ua int, ver int); create
unique index unik on test(pia int, ua , ver );
create table test_data(id int primary key, test_id int REFERENCES test(id),
data date ); create unique index td_uink on test_data(test_id);
insert into test values (1,37,76,1);
insert into test values (2,37,76,1);
so no NULL is needed at all - in best/worst case it's VISIBLE at the outer
join of test and test_data.
I'm quite sure this is a better solution than pollute the system first with
any bogus information, just to try to help out with histograms afterwards?
If someone complains about more IOs because of the 2 tables, I just can
recommend to check the logic behind CLUSTERS.
On Fri, Mar 30, 2012 at 13:27, Mark W. Farnham wrote:
Often, midnight or one second past midnight on Jan. 1, 1970 can be
well known as the value for "this is not a real date" in honor of the
epoch time of UNIX. There are other reasonable choices as well,
particularly if you are doing history or something like that. In some
cases having a special value is not useful, but it does prevent row
length change when it is updated to the "real date" that may later be
appearing. And you can search for it with an equals predicate on an
index if you're looking for dates that haven't been processed yet.
If you're not worried about row length change (or if there is a
positive trade-off versus having a lower pctfree) you can use defaults
values for rows inserted without values for particular columns. With a
default chosen that is unlikely to by a real value, you can check the
data dictionary for this value to firewall your application software
instead of relying on a presumed constant (like 1/1/1970.)
Often a little bit of planning of this nature in the data model design
considered against the planned data flow through an application can
make implementing the system easier, and you may indeed end up with a
system containing few nulls. One notable exception is having a final
status value of null to mean "DONE" on an indexed transaction control
column, which takes advantage of the fact that all null index entries
do not appear in the index for routine Oracle indexes (circling back to
the original topic).