介绍
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行数据收集统计信息。
直方图统计信息
直方图统计信息被编译器用来生成最优的执行计划。当直方图统计信息不可用时,编译器会默认选择一套它认为最优的执行计划去执行,当然不一定准确。直方图统计信息反映了表的最新的状态。
编译器并不需要表中所有列的统计信息,统计信息一般在以下情况下比较有用,
- PREDICATE
- GROUP BY
- ORDER BY
- 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