收集统计信息
Posted by wukaiqiang; tagged with none
我们使用method_opt => 'for all columns size auto'方式对表收集统计信息。
SQL> BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'T_STATS',
estimate_percent => 100,
method_opt => 'for all columns size auto',
no_invalidate => FALSE,
degree => 1,
cascade => TRUE);
END
我们查看直方图信息。
SQL> select a.column_name,
2 b.num_rows,
3 a.num_nulls,
4 a.num_distinct Cardinality,
5 round(a.num_distinct / b.num_rows * 100, 2) selectivity,
6 a.histogram,
7 a.num_buckets
8 from dba_tab_col_statistics a, dba_tables b
9 where a.owner = b.owner
10 and a.table_name = b.table_name
11 and a.owner = 'SCOTT'
12 and a.table_name = 'T_STATS';
COLUMN_NAME NUM_ROWS NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
--------------- ---------- ---------- ----------- ----------- --------------- -------
EDITION_NAME 72674 72674 0 0 NONE 0
NAMESPACE 72674 1 21 .03 NONE 1
SECONDARY 72674 0 2 0 NONE 1
GENERATED 72674 0 2 0 NONE 1
TEMPORARY 72674 0 2 0 NONE 1
STATUS 72674 0 2 0 NONE 1
TIMESTAMP 72674 1 1592 2.19 NONE 1
LAST_DDL_TIME 72674 1 1521 2.09 NONE 1
CREATED 72674 0 1472 2.03 NONE 1
OBJECT_TYPE 72674 0 45 .06 NONE 1
DATA_OBJECT_ID 72674 64833 7796 10.73 NONE 1
OBJECT_ID 72674 1 72673 100 NONE 1
SUBOBJECT_NAME 72674 72145 140 .19 NONE 1
OBJECT_NAME 72674 0 44333 61 NONE 1
OWNER 72674 0 31 .04 FREQUENCY 31
15 rows selected.