delete histogram

August 19th, 2009 | Categories: Boring | Tags:
Leave a comment | Trackback

11g以下版本只能通过指定”columns size 1″重新分析表来删除histogram，挺麻烦的。

SQL> select column_name,HISTOGRAM from dba_tab_columns where table_name=’TEST’;

COLUMN_NAME                      HISTOGRAM
———————————————
TEST_ID                          NONE
VERSION_ID                       FREQUENCY
BUCKET                           NONE
FIELD_ID                         NONE
PARTITION_KEY                    FREQUENCY
SUBPARTITION_KEY                 FREQUENCY
DESIGN_VALUE                     FREQUENCY

SQL> exec dbms_stats.gather_table_stats(user,’test’,cascade=>true,method_opt=>’for all columns size 1′);

SQL> select column_name,HISTOGRAM from dba_tab_columns where table_name=’TEST’;

COLUMN_NAME                     HISTOGRAM
———————————————
TEST_ID                           NONE
VERSION_ID                        NONE
BUCKET                            NONE
FIELD_ID                          NONE
PARTITION_KEY                     NONE
SUBPARTITION_KEY                  NONE
DESIGN_VALUE                      NONE
SUBPARTITION_KEY                  NONE

11g中则可以使用DELETE_COLUMN_STATS提供的col_stat_type参数来删除某个列的统计信息。方便多了。

col_stat_type

Type of column statistics to be deleted.This argument takes the following values:

*HISTOGRAM - delete column histogram only
*ALL - delete base column statistics and histogram (default value)

Del.icio.us Google书签 Digg Live Bookmark Technorati Furl Yahoo书签 Facebook 百度搜藏 新浪 ViVi 365Key 网摘 天极网摘 和讯网摘 博拉网 POCO 网摘 饭否 QQ 书签 Digbuzz 我挖网 Mister Wong
  1. luzp
    August 21st, 2009 at 13:56
    Quote | #1

    10g用dbms_stats.SET_COLUMN_STATS也可以删除histogram。9i没试过，应该也可以的。

  2. OoNiceDream
    August 21st, 2009 at 17:41
    Quote | #2

    9i、10g中用dbms_stats.set_column_stats应该也可以的：
    SQL> select column_name,HISTOGRAM from dba_tab_columns where table_name=’TEST_H’;

    COLUMN_NAME HISTOGRAM
    ————— —————
    ID FREQUENCY

    SQL> select owner,table_name,NUM_DISTINCT from dba_tab_columns where table_name=’TEST_H’;

    OWNER TABLE_NAME NUM_DISTINCT
    ———- —————————— ————
    SYS TEST_H 12

    SQL> select column_name,HISTOGRAM from dba_tab_columns where table_name=’TEST_H’;

    COLUMN_NAME HISTOGRAM
    ————— —————
    ID NONE

  3. OoNiceDream
    August 21st, 2009 at 17:42
    Quote | #3

    SQL> exec dbms_stats.set_column_stats(’SYS’,'test_h’,'ID’,DISTCNT=>12);

    PL/SQL procedure successfully completed.

  4. yumianfeilong
    August 22nd, 2009 at 09:44
    Quote | #4

    Oh. Nice. thank you.