Usage Notes

Teradata: Enhancing Performance of FedSQL on Teradata

FedSQL queries that access system catalog tables (for example, the UPDATE statement, DROP TABLE statement, etc.) depend on joins of the system tables to obtain a result set from the Teradata database. In environments that create and drop a large number of database objects, performance suffers if the statistics about these system tables are not current. The following commands can be added to regular database maintenance processes to keep system table statistics up-to-date.
drop stats on dbc.tvm;
drop stats on dbc.owners;
drop stats on dbc.dbase;
drop stats on dbc.accessrights;
drop stats on dbc.tvfields;

collect stats on dbc.tvm column (tvmId);
collect stats on dbc.tvm INDEX ( DatabaseId ,TVMNameI );
collect stats on dbc.tvm column (DatabaseId );
collect stats on dbc.owners INDEX (ownerId);
collect stats on dbc.dbase INDEX ( DatabaseId );
collect stats on dbc.dbase column(JournalId);
collect stats on dbc.accessrights INDEX ( UserId ,DatabaseId );
collect stats on dbc.accessrights INDEX ( TVMId );
collect stats on dbc.accessrights column ( UserId ,TVMId);
collect stats on dbc.accessrights column (DatabaseId );
collect stats on dbc.tvfields column (DatabaseId);
collect stats on dbc.tvfields column (FieldId);
collect stats on dbc.tvfields column (tableId);


/* help stats displays what's there, this is optional */

help stats dbc.tvm;
help stats dbc.owners;
help stats dbc.dbase;
help stats dbc.accessrights;
help stats dbc.tvfields;



/* routine recollect, if stats have not been dropped */

collect stats on dbc.tvm ;

collect stats on dbc.owners ;
collect stats on dbc.dbase ;
collect stats on dbc.accessrights ;

collect stats on dbc.tvfields ;
Last updated: February 23, 2017