RE: ORA-00001: unique constraint (MYDB.SYS_C006557) violated

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Mon, 2 Apr 2012 16:49:32 -0400
Message-ID: <012401cd1112$1bc325f0$534971d0$_at_rsiz.com>

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

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.

Regards,

mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Martin Berger
Sent: Friday, March 30, 2012 10:46 AM
To: mwf_at_rsiz.com
Cc: ORACLE-L
Subject: Re: ORA-00001: unique constraint (MYDB.SYS_C006557) violated

Mark,

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
> (2,37,76,null,1);

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.

hth
 Martin

On Fri, Mar 30, 2012 at 13:27, Mark W. Farnham <mwf_at_rsiz.com> wrote:
> You're welcome.
>
> 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).

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Mon Apr 02 2012 - 15:49:32 CDT