1、基数与选择性


    基数(Cardinality) :列唯一键(Distinct_keys)的数量，比如性别，该列只有男女之分，所以这一列基数是2。主键列的基数等于行数。
    选择性(Selectivity) :列唯一键(Distinct_Keys)数量与行数(Num_Rows)的比值。


select count(distinct column_name),
       count(*) total_rows,
       count(distinct column_name) / count(*) * 100 selectivity
  from table_name;


基数和选择性 这两个概念非常重要，在OLTP系统中，基数/选择性高的列，适合建立B-Tree索引，选择性低的列不适合建立索引。在OLAP环境中，基数低的列根据需求，可能会建立bitmap索引。


2、 直方图 ：当某列数据分布不均衡，为了让CBO能生成最佳的执行计划，我们可能需要对表收集直方图，直方图最大的桶数(Bucket)是254。
收集直方图是一个很耗时的过程，如无必要，千万别去收集直方图。


create table test as select * from dba_objects;




选择性 100% 意味着 这一列没有重复值，所以当 进行等值筛选的时候 走索引，选择性 50%意味着，这一列平均2个值是重复的，
这时候也可以选择走索引








收集直方图的脚本




create or replace procedure create_emp(p_tabname varchar2,
                                       p_cnt     number default 100000,
                                       p_tbs     varchar2 default 'users')
authid current_user
as
    v_create_table_sql varchar2(500);
    l_cnt              number := 0;
    v_sql              varchar2(500);
    l_tabsize          number;
    e_error          exception;
    e_tbs_not_exists exception;
    pragma exception_init(e_error, -00955);
    pragma exception_init(e_tbs_not_exists, -00959);
begin
    begin
        v_create_table_sql := 'create table ' || p_tabname || '
                            nologging tablespace ' ||
                              p_tbs || '
                            as select * from emp
                            where 1 = 0';
        execute immediate v_create_table_sql;
        execute immediate 'alter table '|| p_tabname||' modify empno number(8)';
    exception
        when e_error then
            execute immediate 'drop table ' || p_tabname || ' purge';
            dbms_output.put_line('已经存在表：' || p_tabname || ' ,但已删除。');
            execute immediate v_create_table_sql;
            execute immediate 'alter table '|| p_tabname||' modify empno number(8)';
    end;
    while l_cnt < p_cnt loop
        v_sql := 'insert /*+ append */ into ' || p_tabname ||
                 ' select rownum + ' || l_cnt ||
                 ',ename,job,mgr,hiredate,sal,comm,deptno from emp
                where rownum <= ' || to_char(p_cnt - l_cnt);
        execute immediate v_sql;
        l_cnt := l_cnt + sql%rowcount;
        commit;
    end loop;
    dbms_output.put_line('已创建完表： ' || p_tabname || ' 。记录数：' || p_cnt);
    select bytes / 1024 / 1024
      into l_tabsize
      from user_segments
     where segment_name = upper(p_tabname);
    dbms_output.put_line('表的segment_size： ' || l_tabsize || ' M');
exception
    when e_tbs_not_exists then
        dbms_output.put_line('表空间： ' || p_tbs || ' 不存在。');
end;
======================================================================================================


001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
039
040
041
042
043
044
045
046
047
048
049
050
051
052
053
054
055
056
057
058
059
060
061
062
063
064
065
066
067
068
069
070
071
072
073
074
075
076
077
078
079
080
081
082
083
084
085
086
087
088
089
090
091
092
093
094
095
096
097
098
099
100
101
--查看表的总行数，每列的基数，选择性，直方图统计信息。
 
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 = 'SCOTT'
   and a.table_name = 'T1';
 
 /*查出来是空值，因为没有收集统计信息，表连接以后就是空值*/
--判断哪些列出现在了where条件中，可以用来判断哪些列适合创建索引
--必须先执行该过程，让统计信息刷新到磁盘上
 
begin
    dbms_stats.flush_database_monitoring_info;
end;
/
 
select
     r.name owner,
     o.name table_name ,
     c.name column_name,
     equality_preds,
     equijoin_preds, 
     nonequijoin_preds, 
     range_preds, 
     like_preds,  
     null_preds,  
     timestamp
  from
     sys.col_usage$ u,
     sys.obj$ o,
     sys.col$ c,
     sys.user$ r
  where
     o.obj# = u.obj#
  and c.obj# = u.obj#
  and c.col# = u.intcol#
  and r.name='SCOTT' and o.name='T1';
 
 
 
 
--对非分区表收集统计信息，如果是新表，可以先不收集直方图
--等到出现性能问题之后在单独对该列收集直方图(第一次收集直方图使用 for columns owner size skewonly)
--以后再收集直方图可以使用repeat选项。
 
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
                                  tabname          => 'TT',
                                  estimate_percent => 30,
                                  method_opt       => 'for all columns size 1',
                                  no_invalidate    => FALSE,
                                  degree           => 1,
                                  cascade          => TRUE);
END;
/
 
 
 
--查看上一次收集统计信息时的采样率
 
SELECT owner,
       table_name,
       num_rows,
       sample_size,
       trunc(sample_size / num_rows * 100) estimate_percent 
  FROM DBA_TAB_STATISTICS
 WHERE owner='SYS' AND table_name='T1';
 
 
--查看收集一个表的统计信息间隔
 
select owner,
      table_name,
      partition_name,
      subpartition_name,
      stats_update_time,
      stats_update_time - lag(stats_update_time, 1, null) over(partition by owner, table_name order by stats_update_time) interval
 from DBA_TAB_STATS_HISTORY
where owner = 'SYS'
  and table_name = 'T1'
order by owner, table_name, stats_update_time desc;
 
 
--判断统计信息过期
 
begin
    dbms_stats.flush_database_monitoring_info;
end;
/
 
select owner, table_name name, object_type, stale_stats, last_analyzed
  from dba_tab_statistics
 where table_name in ('TT')
   and owner = 'SCOTT';