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@xxxxxxxxx>
To: <mwf@xxxxxxxx>; "'ORACLE-L'" <oracle-l@xxxxxxxxxxxxx>
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

Other related posts: