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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 8 Feb 2007 10:10:05 -0000
Message-ID: <De2dnZKqOvxna1fYRVnygAA@bt.com>

"Ben" <balvey_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 Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

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

Received on Thu Feb 08 2007 - 04:10:05 CST