Re: Cursor_sharing - Optimizer - Histograms


1)    There are many data distributions that could be
    "skewed" without displaying the typical "very 
    popular value" syndrome that is so loved of the
    manuals and sound-bite guides.  For example, 
    if you have 1,000 rows in the range 1 - 2000,
    then a gap, then 1,000 rows in the range 1,000,000
    to 2,000,000 that data is skewed and Oracle may
    choose to create a histogram on it.

2)    If there is a histogram on any column in the 
    WHERE clause, and you have cursor_sharing
    set to similar, then Oracle re-optimizes. That's
    just the way it is (at present). Your observation
    that the this is a query on a column with a unique
    index in place (is there a unique constraint as
    well - might this make a difference) does suggest
    that there had been an oversight in the code path
    that might be fixed on a future release.


Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated March 9th 2005






----- Original Message ----- 
From: <Rajesh.Rao@xxxxxxxxxxxx>
To: <Oracle-L@xxxxxxxxxxxxx>
Sent: Thursday, March 31, 2005 4:59 PM
Subject: Cursor_sharing - Optimizer - Histograms


My questions (if my inferences are right) are :

1. The optimizer for some reason believes that the unique key values are
skewed, whereas for the primary key the values are not skewed. Why?
2. The presence of an unique index should tell the optimizer that only one
row should be returned when queried using the unique key predicate? So, why
does the optimizer infer that the plan could change, after peeking at the
bind variables?

Regards
Raj

--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

Other related posts: