搜集直方图repeat和skewonly

来源：互联网发布：侠客淘宝 cdkey 编辑：程序博客网时间：2024/06/10 01:13
SQL> set linesize 200SQL>   select a.column_name,                   b.num_rows,                  a.num_distinct Cardinality,                    round(a.num_distinct / b.num_rows * 100, 2) selectivity,                   a.histogram,                  a.num_buckets              from dba_tab_col_statistics a, dba_tables b             where a.owner = b.owner              and a.table_name = b.table_name            and a.owner = 'MBFE'         and a.table_name = upper('messagein');  2    3    4    5    6    7    8    9   10   11  COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM  NUM_BUCKETS------------------------------ ---------- ----------- ----------- --------------- -----------ID   978619      978619      100 NONE    1MSGID   978619      978619      100 FREQUENCY    2STATUS   978619    10 NONE    1PROCCNT    978619    20 NONE    1ACCEPTTIME   978619      653888    66.82 NONE    1MSGBODY    978619    00 NONE    0QUEUE   978619    40 NONE    17 rows selected.SQL>  BEGIN          DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'MBFE',                                       tabname          => 'MESSAGEIN',                                      estimate_percent => 100,                                        method_opt       => 'for columns MSGID size 1',                                      no_invalidate    => FALSE,                                       degree           => 16,                                       cascade          => TRUE);       END;  2    3    4    5    6    7    8    9   10  /PL/SQL procedure successfully completed.SQL>   select a.column_name,                   b.num_rows,                  a.num_distinct Cardinality,                    round(a.num_distinct / b.num_rows * 100, 2) selectivity,                   a.histogram,                  a.num_buckets              from dba_tab_col_statistics a, dba_tables b             where a.owner = b.owner              and a.table_name = b.table_name            and a.owner = 'MBFE'         and a.table_name = upper('messagein');  2    3    4    5    6    7    8    9   10   11  COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM  NUM_BUCKETS------------------------------ ---------- ----------- ----------- --------------- -----------ID   978619      978619      100 NONE    1MSGID   978619      978619      100 NONE    1STATUS   978619    10 NONE    1PROCCNT    978619    20 NONE    1ACCEPTTIME   978619      653888    66.82 NONE    1MSGBODY    978619    00 NONE    0QUEUE   978619    40 NONE    17 rows selected.此时直方图已经被干掉SQL>  BEGIN          DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'MBFE',                                       tabname          => 'MESSAGEIN',                                      estimate_percent => 100,                                        method_opt       => 'for columns MSGID size repeat',                                      no_invalidate    => FALSE,                                       degree           => 16,                                       cascade          => TRUE);       END;  2    3    4    5    6    7    8    9   10  /PL/SQL procedure successfully completed.SQL> ^C^CSQL> SQL> select a.column_name,                   b.num_rows,                  a.num_distinct Cardinality,                    round(a.num_distinct / b.num_rows * 100, 2) selectivity,                   a.histogram,                  a.num_buckets              from dba_tab_col_statistics a, dba_tables b             where a.owner = b.owner              and a.table_name = b.table_name            and a.owner = 'MBFE'         and a.table_name = upper('messagein');  2    3    4    5    6    7    8    9   10   11  COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM  NUM_BUCKETS------------------------------ ---------- ----------- ----------- --------------- -----------ID   978619      978619      100 NONE    1MSGID   978619      978619      100 NONE    1STATUS   978619    10 NONE    1PROCCNT    978619    20 NONE    1ACCEPTTIME   978619      653888    66.82 NONE    1MSGBODY    978619    00 NONE    0QUEUE   978619    40 NONE    17 rows selected.SQL> 用repert 方式收集：repeat表示以前收集过直方图，现在收集统计信息的时候就收集直方图，如果以前没收集过直方图，现在收集统计信息的时候就不收集。此时无效果SQL> BEGIN          DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'MBFE',                                       tabname          => 'MESSAGEIN',                                      estimate_percent => 100,                                        method_opt       => 'for columns MSGID size skewonly',                                      no_invalidate    => FALSE,                                       degree           => 16,                                       cascade          => TRUE);       END;  2    3    4    5    6    7    8    9   10  /PL/SQL procedure successfully completed.SQL> select a.column_name,       b.num_rows,       a.num_distinct Cardinality,       round(a.num_distinct / b.num_rows * 100, 2) selectivity,       a.histogram,       a.num_buckets  from dba_tab_col_statistics a, dba_tables b where a.owner = b.owner   and a.table_name = b.table_name   and a.owner = 'MBFE'   and a.table_name = upper('messagein');  2    3    4    5    6    7    8    9   10   11  COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM  NUM_BUCKETS------------------------------ ---------- ----------- ----------- --------------- -----------ID   978619      978619      100 NONE    1MSGID   978619      978619      100 FREQUENCY    2STATUS   978619    10 NONE    1PROCCNT    978619    20 NONE    1ACCEPTTIME   978619      653888    66.82 NONE    1MSGBODY    978619    00 NONE    0QUEUE   978619    40 NONE    17 rows selected.此时已搜集：skewonly 对所有的列搜集统计信息
0 0