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

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

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

• Original Message ----- From: "D'Hooge Freek" <Freek.DHooge_at_uptime.be> To: <mwf_at_rsiz.com>; "'ORACLE-L'" <oracle-l_at_freelists.org> 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.

--

http://www.freelists.org/webpage/oracle-l Received on Fri Mar 30 2012 - 08:20:11 CDT