vpruthi/doc-exports
1
0
Fork 0
forked from docs/doc-exports
Code Pull Requests Activity
doc-exports/docs/dws/tool/dws_16_0077.html
Lu, Huayi 27019c2991 DWS TOOL 830.201 version 
Reviewed-by: Hasko, Vladimir <vladimir.hasko@t-systems.com>
Reviewed-by: Pruthi, Vineet <vineet.pruthi@t-systems.com>
Co-authored-by: Lu, Huayi <luhuayi@huawei.com>
Co-committed-by: Lu, Huayi <luhuayi@huawei.com>
2024-05-16 07:35:25 +00:00

13 KiB
Raw Permalink Blame History

COLLECT STATISTICS

COLLECT STAT is used in Teradata for collecting optimizer statistics, which will be used for query performance. GaussDB T, GaussDB A, and GaussDB(DWS) use the ANALYZE statement instead of COLLECT STAT.

For details, see ANALYZE.

Input - COLLECT STATISTICS

1
COLLECT STAT tab1 COLUMN (c1, c2);

Output

1
ANALYZE tab1 (c1, c2);

Input - COLLECT STATISTICS

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

Output

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

Input - COLLECT STATISTICS with COLUMN

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

Output

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

Input - COLLECT STATISTICS with Schema Name

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

Output

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

COLLECT STATISTICS

Collect statistics based on sampling percentage.

Input:

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 ;

OUTPUT

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 ;