Re: Unique index not being used, up to date stats

On Feb 8, 5:10 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Ben" <bal..._at_comcast.net> wrote in message
>
> news:1170881070.314817.296020_at_m58g2000cwm.googlegroups.com...
> On Feb 6, 1:38 pm, "Ben" <bal..._at_comcast.net> wrote:
>
> >From lack of response, I'm going to assume not many people have done this.
> >I have found a way that I believe I can accomplish tricking the CBO
> >into not seeing the high value of that rogue record, without using
> >dbms_stats.set_column_stats.
> >If I analyze the table and then export the statistics to the stattab,
> >I can go in and find that column in the table and change the value of
> >the C7 column to be a new lower value, and then import those
> >statistics back over the dictionary stats.
> >Is there any reason why this wouldn't work?
>
> An alternative explanation for the lack of a response is that
> we can't all watch the newsgroup 24 hours per day to rush to
> your help when you need it.
>
> You strategy should work, although it is a little laborious.
> If you want to use dbms_stats, here's some sample code
> for changing the high value on a numeric column of a
> non-partitioned heap table when there is no histogram
> in place.
>
> declare
>
> srec dbms_stats.statrec;
>
> m_distcnt number;
> m_density number;
> m_nullcnt number;
> m_avgclen number;
>
> n_array dbms_stats.numarray;
>
> begin
>
> dbms_stats.get_column_stats(
> ownname => user,
> tabname => 't1',
> colname => 'n1',
> distcnt => m_distcnt,
> density => m_density,
> nullcnt => m_nullcnt,
> srec => srec,
> avgclen => m_avgclen
> );
>
> n_array := srec.novals;
> n_array(2) := 999;
>
> dbms_stats.prepare_column_values(srec, n_array);
>
> dbms_stats.set_column_stats(
> ownname => user,
> tabname => 't1',
> colname => 'n1',
> distcnt => m_distcnt,
> density => m_density,
> nullcnt => m_nullcnt,
> srec => srec,
> avgclen => m_avgclen
> );
>
> end;
> /
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html

WHAT?? you have better things to do than help someone that you don't know from Adam?? Preposterous! ;)

Sorry if that previous post sounded rude, it wasn't meant to be. Thank
you for the help, I'm sure there are many individuals that read this group and are grateful for the time and knowledge that is put forth by
the regular contributors on here. Received on Thu Feb 08 2007 - 08:03:04 CST