示例

• 收集表的的统计信息。

  --创建customer_info表。
  gaussdb=# CREATE TABLE customer_info(
  wr_returned_date_sk INTEGER ,
  wr_returned_time_sk INTEGER ,
  wr_item_sk INTEGER NOT NULL
  ,wr_refunded_customer_sk INTEGER );
  
  --使用ANALYZE语句更新统计信息。
  gaussdb=# ANALYZE customer_info;
  
  --使用ANALYZE VERBOSE语句更新统计信息，并输出customer_info表的相关信息。
  gaussdb=# ANALYZE VERBOSE customer_info;
  INFO:  analyzing "public.customer_info"(datanode pid=38661)
  INFO:  ANALYZE INFO : estimate total rows of "customer_info": scanned 0 pages of total 0 pages with 1 retry times, containing 0 live rows and 0 dead rows,  estimated 0 total rows(datanode pid=38661)
  INFO:  ANALYZE INFO : "customer_info": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows(datanode pid=38661)
  
  --使用ANALYZE VERBOSE输出customer_info表的wr_returned_time_sk列信息。
  gaussdb=# ANALYZE VERBOSE customer_info(wr_returned_time_sk);
  INFO:  analyzing "public.customer_info"(datanode pid=38661)
  INFO:  ANALYZE INFO : estimate total rows of "customer_info": scanned 0 pages of total 0 pages with 1 retry times, containing 0 live rows and 0 dead rows,  estimated 0 total rows(datanode pid=38661)
  INFO:  ANALYZE INFO : "customer_info": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows(datanode pid=38661)

• 收集分区表的统计信息。

  --创建分区表。
  gaussdb=# CREATE TABLE customer_par( 
  wr_returned_date_sk INTEGER ,
  wr_returned_time_sk INTEGER ,
  wr_item_sk INTEGER NOT NULL,
  wr_returned_customer_sk INTEGER)
  PARTITION BY RANGE(wr_returned_date_sk)(PARTITION P1 VALUES LESS THAN(2452275),PARTITION P2 VALUES LESS THAN(2452640),PARTITION P3 VALUES LESS THAN(2453000),PARTITION P4 VALUES LESS THAN(MAXVALUE))ENABLE ROW MOVEMENT;
  
  --使用ANALYZE语句更新统计信息。
  gaussdb=# ANALYZE customer_par;
  
  --使用ANALYZE VERBOSE输出customer_par表的相关信息。
  gaussdb=# ANALYZE VERBOSE customer_par;
  INFO:  analyzing "public.customer_par"(datanode pid=38661)
  --使用ANALYZE VERBOSE输出P1一级分区表的相关信息。
  gaussdb=# ANALYZE VERBOSE customer_par PARTITION(P1);
  INFO:  analyzing "public.customer_par"(datanode pid=38661)

• 手动收集多列统计信息。

  --手动收集wr_returned_date_sk、wr_returned_time_sk这两列的统计信息。
  gaussdb=# ANALYZE VERBOSE customer_info (wr_returned_date_sk,wr_returned_time_sk);
  INFO:  analyzing "public.customer_info"(datanode pid=38661)
  INFO:  ANALYZE INFO : estimate total rows of "customer_info": scanned 0 pages of total 0 pages with 1 retry times, containing 0 live rows and 0 dead rows,  estimated 0 total rows(datanode pid=38661)
  INFO:  ANALYZE INFO : "customer_info": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows(datanode pid=38661)

• 自动收集多列统计信息。

  --给customer_info表创建索引。
  gaussdb=# CREATE INDEX customer_index ON customer_info USING btree(wr_returned_date_sk,wr_returned_time_sk,wr_item_sk,wr_refunded_customer_sk);
  
  --设置auto_statistic_ext_columns参数为4
  gaussdb=# set auto_statistic_ext_columns=4;
  
  --自动收集多列统计信息。
  gaussdb=# ANALYZE VERBOSE customer_info;
  INFO:  analyzing "public.customer_info"(datanode pid=38661)
  INFO:  ANALYZE INFO : estimate total rows of "customer_info": scanned 0 pages of total 0 pages with 1 retry times, containing 0 live rows and 0 dead rows,  estimated 0 total rows(datanode pid=38661)
  INFO:  ANALYZE INFO : "customer_info": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows(datanode pid=38661)

• 检测当前库的数据文件。

  gaussdb=# ANALYZE VERIFY FAST;

• 检测表和索引的数据文件。

  --检查customer_info表。
  gaussdb=# ANALYZE VERIFY FAST customer_info;
  
  --检查customer_index索引。
  gaussdb=# ANALYZE VERIFY FAST customer_index;
  

• 检测表分区的数据文件。

  --检查customer_par分区表中的P1分区。
  gaussdb=# ANALYZE VERIFY FAST customer_par PARTITION (P1);

• 删除数据。

  --删除索引customer_index。
  gaussdb=# DROP INDEX customer_index;
  
  --删除表customer_info。
  gaussdb=# DROP TABLE customer_info;
  
  --删除分区表customer_par。
  gaussdb=# DROP TABLE customer_par;