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

On Jan 29, 4:37 pm, "Ben" <bal..._at_comcast.net> wrote:
> On Jan 29, 4:24 pm, "jerni..._at_kochind.com" <jerni..._at_kochind.com>
> wrote:
>
> > Was there any bad data inserted into the table with an abnormally high
> > or low value? What is the max and min for that column. I have seen
> > this change the plan.the max and min is a little out of whack, due to two different next
> number routines being used, but the numbers are listed in the above
> thread, I don't think there are enough of the lower values to throw it
> off that bad.

If min/max are out of whack, have you tried gathering histograms? That might help also ..:

ORA92> select min(a), max(a) from test98;

    MIN(A) MAX(A)
---------- ----------

         1 2000

ORA92> insert into test98 values (10000,'x','y');

1 row created.

ORA92> commit;

Commit complete.

ORA92> exec
dbms_stats.gather_table_stats(user,'TEST98',cascade=>true);

PL/SQL procedure successfully completed.

ORA92> set autotrace traceonly exp
ORA92> select * from test98 where a >= 1999;

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=1602 Bytes=264330)

   1 0 TABLE ACCESS (FULL) OF 'TEST98' (Cost=11 Card=1602 Bytes=264330)

ORA92> exec
dbms_stats.gather_table_stats(user,'TEST98',cascade=>true,method_opt=>' FOR COLUMNS A SIZE AUTO') PL/SQL procedure successfully completed.

ORA92> select * from test98 where a >= 1999;

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=26 Bytes=4290)

   1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST98' (Cost=3 Card=26 Bytes=4290)

   2 1 INDEX (RANGE SCAN) OF 'TEST98_PK' (UNIQUE) (Cost=2 Card=26)

Anurag Received on Mon Jan 29 2007 - 18:38:52 CST