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 COLLECT STAT.

Learn more information in ANALYZE.

Input - COLLECT STATISTICS

COLLECT STAT tab1 COLUMN (c1, c2);

Output:

ANALYZE tab1 (c1, c2);

Input - COLLECT STATISTICS

COLLECT STATISTICS
  COLUMN (customer_id,customer_name)
, COLUMN (postal_code)
, COLUMN (customer_address)
ON customer_t;

Output:

ANALYZE customer_t (
     customer_id
     ,customer_name
     ,postal_code
     ,customer_address
)
;

Input - COLLECT STATISTICS with COLUMN

COLLECT STATISTICS
  COLUMN (
  Order_Date
  -- ,o_orderID
/*COLLECT
STATISTICS*/
  ,Order_ID
     )
ON order_t;

Output:

ANALYZE order_t (
    Order_Date
     ,Order_ID
)
;

Input - COLLECT STATISTICS with Schema Name

COLLECT STATS COLUMN (
     empno
     ,ename
)
     ON ${schemaname}."usrTab1"
;

Output:

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 ;