Re: ORA-00001: unique constraint (MYDB.SYS_C006557) violated
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "'ORACLE-L'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 30 Mar 2012 14:20:11 +0100
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
