dws
COLLECT STATISTICS
更新时间:2020/04/21 GMT+08:00
在Teradata中,COLLECT STAT采集优化器统计信息,用于查询性能。GaussDB T、GaussDB A 和 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 ;
父主题: Teradata语法迁移
