RE: ORA-00001: unique constraint (MYDB.SYS_C006557) violated
Date: Fri, 30 Mar 2012 15:03:58 +0200
Message-ID: <4814386347E41145AAE79139EAA398981CBE5CD21E_at_ws03-exch07.iconos.be>
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_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Mar 30 2012 - 08:03:58 CDT