analyze vs dbms_stats

I'm trying to get our shop to convert from analyze to dbms_stats.
I'm running into some "strange" results though and wanted to see if I'm
missing something or you have some advice.

analyze command:
ESTIMATE STATISTICS SAMPLE 30
 PERCENT
 FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254

dbms_stats code I'm running:
exec DBMS_STATS.GATHER_TABLE_STATS ( -
   ownname    =>    'STATION_TEST', -
   tabname    =>    'MEMBERS', -
   partname   =>    NULL, -
   estimate_percent => 30, -
   block_sample  =>   FALSE, -
   method_opt =>    'FOR ALL COLUMNS SIZE 254', -
   degree      =>     0, -
   granularity =>    'DEFAULT', -
   cascade     =>   TRUE, -
   stattab     =>  NULL, -
   statid      => NULL, -
   statown     =>    NULL, -
   no_invalidate =>   FALSE);

sample of stats info from analyze:
Table STATION_TEST.MEMBERS
- Number of rows     : 14284780
- Number of blocks   : 1623961
- Average row length : 192

  Column RESTRICTION_CODE
  - Number of distinct values : 0
  - Number of nulls : 14284780

  Column DAY_PHONE
  - Number of distinct values : 51
  - Number of nulls : 14273930

  Column EVE_PHONE
  - Number of distinct values : 44
  - Number of nulls : 14284620

  Column ISP_ID
  - Number of distinct values : 10
  - Number of nulls : 14284770

sample of dbms stats generated statistics:
Table STATION_TEST.MEMBERS
- Number of rows     : 14283597
- Number of blocks   : 1623961
- Average row length : 193

  Column RESTRICTION_CODE
  - Number of distinct values : 0
  - Number of nulls : 14283597

  Column DAY_PHONE
  - Number of distinct values : 95
  - Number of nulls : 14273027

  Column EVE_PHONE
  - Number of distinct values : 0
  - Number of nulls : 14283467

  Column ISP_ID
  - Number of distinct values : 0
  - Number of nulls : 14283577

  Column USER_ID (NOT NULL)
  - Number of distinct values : 14283597
  - Number of nulls : 0

As it turns out analyze in this test is far more accurate.  For instance
eve_phone actually has 66 distinct values.
Any ideas on the disparity?  I am going to try compute, but apples to
apples reflect a big diff...
Thanks
- David
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: