# 直方图、基数、选择性、群集因子

1. 创建实验表
SQL> show user;
USER is "ANDY"

SQL>create table test as select * from dba_objects;

2. 先收集统计信息

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ANDY',
tabname => 'TEST',
estimate_percent => 100,
method_opt => 'for all columns size skewonly',
no_invalidate => FALSE,
degree => 1,
END;
/

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ANDY',
tabname => 'TEST',
estimate_percent => 100,
method_opt => 'for all columns size 1',
no_invalidate => FALSE,
degree => 1,
END;
/

3.查询统计信息 (基数和选择性)

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 = 'ANDY'
and a.table_name = 'TEST';

COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------ ---------- ----------- ----------- --------------- -----------
OWNER 74770 30 .04 FREQUENCY 30
OBJECT_NAME 74770 46694 62.45 HEIGHT BALANCED 254
SUBOBJECT_NAME 74770 51 .07 FREQUENCY 51
OBJECT_ID 74770 74770 100 NONE 1
DATA_OBJECT_ID 74770 9792 13.1 HEIGHT BALANCED 254
OBJECT_TYPE 74770 43 .06 FREQUENCY 43
CREATED 74770 1120 1.5 HEIGHT BALANCED 254
LAST_DDL_TIME 74770 1185 1.58 HEIGHT BALANCED 254
TIMESTAMP 74770 1240 1.66 HEIGHT BALANCED 254
STATUS 74770 2 0 FREQUENCY 2
TEMPORARY 74770 2 0 FREQUENCY 2

COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------ ---------- ----------- ----------- --------------- -----------
GENERATED 74770 2 0 FREQUENCY 2
SECONDARY 74770 2 0 FREQUENCY 2
NAMESPACE 74770 20 .03 FREQUENCY 20
EDITION_NAME 74770 0 0 NONE 0

15 rows selected.

1.

2.

3.

+ 订阅