更新时间:2023-03-17 GMT+08:00

COLLECT STATISTICS

在Teradata中,COLLECT STAT采集优化器统计信息,用于查询性能。GaussDB(DWS)使用ANALYZE语句来替代COLLECT STAT。

详情请参见1

输入:COLLECT STATISTICS

1
COLLECT STAT tab1 COLUMN (c1, c2);	

输出

1
ANALYZE tab1 (c1, c2);

输入:COLLECT STATISTICS

1
2
3
4
5
COLLECT STATISTICS
  COLUMN (customer_id,customer_name) 
, COLUMN (postal_code) 
, COLUMN (customer_address) 
ON customer_t;

输出

1
2
3
4
5
6
7
ANALYZE customer_t (
     customer_id
     ,customer_name
     ,postal_code
     ,customer_address
)
;

输入:COLLECT STATISTICS,使用COLUMN

1
2
3
4
5
6
7
8
9
COLLECT STATISTICS
  COLUMN (
  Order_Date
  -- ,o_orderID
/*COLLECT 
STATISTICS*/
  ,Order_ID
     )  
ON order_t;

输出

1
2
3
4
5
ANALYZE order_t (
    Order_Date
     ,Order_ID
)
;

输入:COLLECT STATISTICS,使用schemaname

1
2
3
4
5
6
COLLECT STATS COLUMN (
     empno
     ,ename
)
     ON ${schemaname}."usrTab1"
;

输出

1
2
3
4
5
6
ANALYZE ${schemaname}."usrTab1" 
(
     empno
     ,ename
)
;

COLLECT STATISTICS

统计(COLLECT STATISTICS)基于抽样计算百分比。

输入

COLLECT STATISTICS 
 USING SAMPLE 5.00 PERCENT 
 COLUMN ( CDR_TYPE_KEY ) , 
 COLUMN ( PARTITION ) , 
 COLUMN ( SRC ) , 
 COLUMN ( PARTITION,SBSCRPN_KEY ) 
 ON DT_SDM.FCT_OTGO_NTWK_ACTVY_DAILY ;

输出:

SET
default_statistics_target = 5.00 ;
ANALYZE DT_SDM.FCT_OTGO_NTWK_ACTVY_DAILY (CDR_TYPE_KEY) ;
ANALYZE DT_SDM.FCT_OTGO_NTWK_ACTVY_DAILY (PARTITION) ;
ANALYZE DT_SDM.FCT_OTGO_NTWK_ACTVY_DAILY (SRC) ;
ANALYZE DT_SDM.FCT_OTGO_NTWK_ACTVY_DAILY (PARTITION,SBSCRPN_KEY) ;
 RESET default_statistics_target ;