Hi All,

The index is getting used if i just analyze the table with buckets 1 for COLS stats, where as same is going for FTS when i take STATS of the cols with 50/75/100 buckets.

PS below
Code:
WW04_PRO_DBA> analyze table shipment_2002 delete statistics;

Table analyzed.

WW04_PRO_DBA> analyze table shipment_2002 estimate statistics sample 40 percent;

Table analyzed.

WW04_PRO_DBA> Select
  2    TABLE_NAME                                                             ,
  3    NUM_ROWS                                                               ,
  4    BLOCKS                                                                 ,
  5    EMPTY_BLOCKS                                                           ,
  6    AVG_SPACE                                                              ,
  7    CHAIN_CNT                                                              ,
  8    AVG_ROW_LEN                                                            ,
  9    AVG_SPACE_FREELIST_BLOCKS                                              ,
 10    SAMPLE_SIZE                                                            ,
 11    LAST_ANALYZED
 12  From
 13    dba_tables
 14  Where
 15    Table_Name   =   'SHIPMENT_2002'
 16  /

TABLE_NAME        NUM_ROWS   BLOCKS   EMPTY_BLOCKS   AVG_SPACE   CHAIN_CNT   AVG_ROW_LEN   SAMPLE_SIZE LAST_ANAL
--------------- ---------- -------- -------------- ----------- ----------- -------------  ------------ ---------
SHIPMENT_2002      1401935    20784            975         874           0           105        460658 11-SEP-03

WW04_PRO_DBA> ed
Wrote file afiedt.buf

  1  Select
  2  TABLE_NAME                             ,
  3  COLUMN_NAME                            ,
  4  NUM_DISTINCT                           ,
  5  LOW_VALUE                              ,
  6  HIGH_VALUE                             ,
  7  DENSITY                                ,
  8  NUM_NULLS                              ,
  9  NUM_BUCKETS                            ,
 10  LAST_ANALYZED                          ,
 11  SAMPLE_SIZE
 12  from
 13  dba_tab_columns
 14  where
 15* Table_Name   =   'SHIPMENT_2002'
WW04_PRO_DBA> /

TABLE_NAME       COLUMN_NAME             NUM_DISTINCT LOW_VALUE                HIGH_VALUE                              DENSITY    NUM_BUCKETS    SAMPLE_SIZE
---------------- ---------------------- ------------- ------------------------ -------------------------- --------------------  -------------  -------------
SHIPMENT_2002    SLS_ORG_CD                        76 41455331                 55535645                      .0131578947368421              1         460658
SHIPMENT_2002    SLS_ORD_NUMBER                181091 30303030313631343333     49313433303932303530       5.52208558128234E-06              1         460658
SHIPMENT_2002    SHIPID                        325636 30303231313333313837     41333733373434323838       .0000030709135353585              1         460658
SHIPMENT_2002    SLS_ORD_LINE_ITEM_NM            1413 303030303031             313232303031                .000707714083510262              1         460658
SHIPMENT_2002    PART_NUMBER                     2879 3130333235302D423234     525A32394C2D454E            .000347342827370615              1         460658

WW04_PRO_DBA> select * from shipment_2002 where part_number='ajsd';

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=382 Card=487 Bytes=4
          3830)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'SHIPMENT_2002' (Cost=382
           Card=487 Bytes=43830)

   2    1     INDEX (RANGE SCAN) OF 'PK_SHIPMENT_2002' (UNIQUE) (Cost=
          6 Card=487)

WW04_PRO_DBA> analyze table shipment_2002 estimate statistics sample 40 percent for all columns size 75;

Table analyzed.

WW04_PRO_DBA> Select
  2  TABLE_NAME                             ,
  3  COLUMN_NAME                            ,
  4  NUM_DISTINCT                           ,
  5  LOW_VALUE                              ,
  6  HIGH_VALUE                             ,
  7  DENSITY                                ,
  8  NUM_NULLS                              ,
  9  NUM_BUCKETS                            ,
 10  LAST_ANALYZED                          ,
 11  SAMPLE_SIZE
 12  from
 13  dba_tab_columns
 14  where
 15  Table_Name   =   'SHIPMENT_2002';

TABLE_NAME       COLUMN_NAME             NUM_DISTINCT LOW_VALUE               HIGH_VALUE                             DENSITY  NUM_BUCKETS  SAMPLE_SIZE
---------------- ----------------------- ------------ ----------------------- ------------------------- -------------------- ------------ ------------
SHIPMENT_2002    SLS_ORG_CD                        76 41455331                55535645                    .00666666666666667           31       460658
SHIPMENT_2002    SLS_ORD_NUMBER                181091 30303030313631343333    49313433303932303530      .0000134466437177281           75       460658
SHIPMENT_2002    SHIPID                        325636 30303231313333313837    41333733373434323838      .0000076974590687614           75       460658
SHIPMENT_2002    SLS_ORD_LINE_ITEM_NM            1413 303030303031            313232303031                .00666666666666667           42       460658
SHIPMENT_2002    PART_NUMBER                     2879 3130333235302D423234    525A32394C2D454E            .00587852006350807           68       460658

WW04_PRO_DBA> select * from shipment_2002 where part_number='ajsd';

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1268 Card=8241 Bytes
          =741690)

   1    0   TABLE ACCESS (FULL) OF 'SHIPMENT_2002' (Cost=1268 Card=824
          1 Bytes=741690)
I dunno why CBO sometimes acts crazyly..could any one explain this behaviour.

Thanks
Abhay.