RE: ORA-00001: unique constraint (MYDB.SYS_C006557) violated
- From: D'Hooge Freek <Freek.DHooge@xxxxxxxxx>
- To: "mwf@xxxxxxxx" <mwf@xxxxxxxx>, 'ORACLE-L' <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Mar 2012 15:03:58 +0200
Be carefull with these kind of bogus values as they can mislead the the cbo.
Recently I had one case where the cbo was taking a wrong path, because it
thought that a certain step would return very few rows. Reason for that was the
application was using a date that was far (far far far) in the future to
indicate an unknown date and with range predicates the max and min values are
used as part of a formula to calculate the selectivity of the predicate.
Maybe you can add an explicit flag to indicate that the date is unknown and add
this flag column to your unique constraint as well?
Kind regards,
Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge@xxxxxxxxx
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Mark W. Farnham
Sent: vrijdag 30 maart 2012 13:27
To: 'ORACLE-L'
Subject: RE: ORA-00001: unique constraint (MYDB.SYS_C006557) violated
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).
mwf
--
//www.freelists.org/webpage/oracle-l
Other related posts:
