Loading ...
Sorry, an error occurred while loading the content.

DBMS_STATS and AVG_COL_LEN

Expand Messages
  • alexcosser
    I ve been on the dark side using DB2 for a couple of years. Good to be back. Anyway DBMS_STATS appears to be creating incorrect statistics for AVG_COL_LEN. It
    Message 1 of 1 , Nov 2, 2006
    • 0 Attachment
      I've been on the dark side using DB2 for a couple of years. Good to
      be back. Anyway

      DBMS_STATS appears to be creating incorrect statistics for
      AVG_COL_LEN. It is always one more than it should be.

      SQL*Plus: Release 9.2.0.1.0 - Production on Wed Nov 1 08:13:24 2006

      Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

      Connected to:
      Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
      With the Partitioning and OLAP options
      JServer Release 9.2.0.4.0 - Production

      create table demo_stats_problem (firstcol varchar(20));
      create index demo_stats_problem_I on demo_stats_problem (firstcol);
      insert into demo_stats_problem values ('12345');
      select table_name, column_name, GLOBAL_STATS, AVG_COL_LEN from
      dba_tab_columns
      where owner = 'AOPSHD1' and table_name = 'DEMO_STATS_PROBLEM';

      TABLE_NAME
      COLUMN_NAME GLO AVG_COL_LEN
      ------------------------------ ---------------------------------------
      - --- ------------
      DEMO_STATS_PROBLEM
      FIRSTCOL NO

      analyze table DEMO_STATS_PROBLEM compute statistics;
      select table_name, column_name, GLOBAL_STATS, AVG_COL_LEN from
      dba_tab_columns
      where owner = 'AOPSHD1' and table_name = 'DEMO_STATS_PROBLEM';

      TABLE_NAME
      COLUMN_NAME GLO AVG_COL_LEN
      ------------------------------ ---------------------------------------
      - --- ------------
      DEMO_STATS_PROBLEM
      FIRSTCOL NO 5

      EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEMO_STATS_PROBLEM')
      select table_name, column_name, GLOBAL_STATS, AVG_COL_LEN from
      dba_tab_columns
      where owner = 'AOPSHD1' and table_name = 'DEMO_STATS_PROBLEM';

      TABLE_NAME
      COLUMN_NAME GLO AVG_COL_LEN
      ------------------------------ ---------------------------------------
      - --- ------------
      DEMO_STATS_PROBLEM
      FIRSTCOL YES 6


      I've got another problem where the cascade option seems to produce
      significantly different results in a number of columns including high
      and low values.
    Your message has been successfully submitted and would be delivered to recipients shortly.