COLLECT STATISTICS

COLLECT STAT is used in Teradata for collecting optimizer statistics, which will be used for query performance. GaussDB(DWS) uses the ANALYZE statement to replace the COLLECT STAT statement.

For details, see 1.

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
)
;

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 ;