Trafodion 更新统计信息

介绍

Trafodion数据库中使用UPDATE STATISTICS语句来更新统计信息，可以更新表中一个或多个列的组合的直方图信息。更新后的统计信息可以帮助优化执行计划。

语法

UPDATE STATISTICS FOR TABLE table [CLEAR | on-clause]
on-clause is:
    ON column-group-list CLEAR
    ON column-group-list [histogram-option]...
column-group-list is:
    column-name
    column-name TO column-name
    (column-name, column-name, ...)
    EVERY COLUMN [, column-list] ...
    EVERY KEY [, column-list] ...
    EXISTING COLUMN[S] [, column-list] ...
    NECESSARY COLUMN[S] [, column-list] ...
histogram-option is:
    GENERATE n INTERVALS
    SAMPLE [r ROWS]
    SAMPLE RANDOM percent PERCENT
    SAMPLE PERIODIC size ROWS EVERY period ROWS

语法描述

• CLEAR

用于删除表上所有或者部分统计信息，一般在切换到新的应用时且不再需要某些统计信息的时候用到。不指定column-gorup-list的时候表示清除表上所有统计信息，带column-group-list时表示只删除对应的统计信息。

• EVERY COLUMN

表示对表中每个单独的列，以及组成主键和索引的所有列组合都生成相应的统计信息。比如，表上有A B C D四列，主键是(A B C)，则统计信息会对A, B, C, D, (A, B, C), (A, B)进行收集。EVERY COLUMN是在EVERY KEY的基础上增加了对每个单独列的统计信息的收集。

• EVERY KEY

对组成主键和索引的所有列组合生成相应的统计信息。比如，表上有A B C D四列，主键是(A B)，统计信息在(A, B), A, B上面生成。若主键是(A B C)，统计信息在(A, B, C), (A, B), A, B, C上面生成。若主键是(A B C D), 统计信息在(A, B, C, D), (A, B, C), (A, B), A, B, C上面生成。

• EXISTING COLUMN[S]

表示对表上所有已有的统计信息进行更新。统计信息必须之前已经被收集过。

• NECESSARY COLUMN[S]
  

用于收集优化器需要但还不存在的那些统计信息。必须要ENABLE自动更新统计信息。

• GENERATE n INTERVALS

n范围在1~10000之间，表示隔多长时间更新一次统计信息。

• SAMPLE

SAMPLE后面不带任何选项的时候，收集统计信息取决于表中记录数。如果表记录数在10K以内，整个表都会收集统计信息（等于没有SAMPLE）； 如何表记录数在10K~1M之间，随机选择10K的记录收集统计信息； 如果表记录数超过1M， 随机从表中取出1%的数据进行收集统计信息。

• SAMPLE [r ROWS]

表示取样r条记录进行统计信息的收集

• SAMPLE RANDOM percent PERCENT

percent在1~100之间，表示随机抽样固定百分比的数据收集统计信息。

• PERIODIC size ROWS EVERY period ROWS

表示 对每多少行数据取出固定行收集统计信息，如PERIODIC 5 ROWS EVERY 20 ROWS，表示对每20行数据取前5行数据收集统计信息。

直方图统计信息

直方图统计信息被编译器用来生成最优的执行计划。当直方图统计信息不可用时，编译器会默认选择一套它认为最优的执行计划去执行，当然不一定准确。直方图统计信息反映了表的最新的状态。

编译器并不需要表中所有列的统计信息，统计信息一般在以下情况下比较有用，

1. PREDICATE
2. GROUP BY
   
3. ORDER BY
   
4. HAVING

或者其他类似的语句中。除了需要单个列的统计信息，编译器同时也需要多个列组合的统计信息，比如在GROUP BY 1,2,3中，就需要对1,2,3列的组合的统计信息。

事务

注意，UPDATE STATISTICS不要放在一个BEGIN TRASACTION后，因为它会自己启动多个事务。

自动化更新统计信息

如果希望打开自动化更新统计信息，需要设置CQD USTAT_AUTOMATION_INTERVAL，如下，

CQD USTAT_AUTOMATION_INTERVAL '1440';

上面的1440原义表示自动收集统计信息的时间间隔，但从TRAFODION1.0以后，只要这个数字大于0，就表示打开自动更新统计信息。

样例

1 创建包含两列的测试表并对EVERY COLUMN收集统计信息

SQL>create table test1(a int not null, b varchar(10),
+>primary key (a));

--- SQL operation complete.

SQL>insert into test1 values(1, 'Zhang'), (2, 'Li');

--- 2 row(s) inserted.

SQL>update statistics for table test1 on every column;

--- SQL operation complete.
SQL>showstats for table test1 on every column;

Histogram data for Table TRAFODION.SEABASE.TEST1
Table ID: 2477655943999136035

   Hist ID # Ints    Rowcount         UEC Colname(s)
========== ====== =========== =========== ===========================
 943763229      2           2           2 A
 943763224      2           2           2 B

--- SQL operation complete.

2 创建包含三列的测试表并对EVERY KEY收集统计信息

SQL>create table test2(a int not null, b varchar(10) not null, c varchar(10),
+>primary key (a,b));

--- SQL operation complete.

SQL>insert into test2 values(1, 'Zhang', 'Shan'), (2, 'Li', 'Si'), (3, 'Wang', 'Wu');

--- 3 row(s) inserted.

SQL>showstats for table test2 on every key;

Histogram data for Table TRAFODION.SEABASE.TEST2
Table ID: 2477655943999163921

   Hist ID # Ints    Rowcount         UEC Colname(s)
========== ====== =========== =========== ===========================

No Histograms exist for the requested columns or groups

--- SQL operation complete.

SQL>update statistics for table test2 on every key;

--- SQL operation complete.

SQL>showstats for table test2 on every key;

Histogram data for Table TRAFODION.SEABASE.TEST2
Table ID: 2477655943999163921

   Hist ID # Ints    Rowcount         UEC Colname(s)
========== ====== =========== =========== ===========================
1253505537      1           3           3 A, B

--- SQL operation complete.

直方图元数据

在用户模式下，只要有表执行了更新统计信息的操作，这个模式下便会相应地生成两个表，SB_HISTOGRAMS和SB_HISTOGRAM_INTERVALS，SB_HISTOGRAMS保存表的实际的统计信息，而SB_HISTOGRAM_INTERVALS则保存了直方图的统计区间信息,以便优化器计算具体的数据分布。

SQL>show tables sb*

TABLE NAMES
--------------------------------------------------------------------------------
SB_HISTOGRAMS           SB_HISTOGRAM_INTERVALS