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

From: Jonathan Lewis <>
Date: Fri, 30 Mar 2012 14:20:11 +0100
Message-ID: <21CAC2F3E5AB4EE1AA7FFADA7B16A7D9_at_Primary>

A possible workaround to the "silly value for null" design error is to create a histogram on the column - this allows the optimizer to see that there is a big gap in the data range and compensate accordingly. It isn't effective in all cases, of course, but may deal with a sufficiently large percentage to make it worth doing. The downside includes the impact is has on "cursor_sharing = similar" and adaptive cursor sharing - where the presence of histograms on columns that appear with bind variables in the where clause is sufficient to identify the SQL as "bind sensitive".


Jonathan Lewis
Oracle Core (Apress 2011)

  • Original Message ----- From: "D'Hooge Freek" <> To: <>; "'ORACLE-L'" <> Sent: Friday, March 30, 2012 2:03 PM Subject: RE: ORA-00001: unique constraint (MYDB.SYS_C006557) violated

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.

-- Received on Fri Mar 30 2012 - 08:20:11 CDT

Original text of this message