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 ;