COLLECT STATISTICS

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

详情请参见ANALYZE。

输入：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 
 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 ;