Examine the command:
SQL>DBMS_STATS.SET_TABLE_PREFS(‘SH’,’CUSTOMERS’,’PUBLISH’,’false’);
Which statement describes the effect of the above command?
A.
Automatic statistics collection is stopped for the CUSTOMERS table
B.
Statistics for the CUSTOMERS table are locked and cannot be overwritten
C.
Existing statistics for the CUSTOMERS table become unusable for the query optimizer
D.
Subsequently, statistics gathered on the CUSTOMERS table are stored as pending statistics
SET_TABLE_PREFS Procedure
This procedure is used to set the statistics preferences of the specified table in the specified schema.
The DBMS_STATS.GET_PREFS function allows you to check the ‘PUBLISH’ attribute to see if statistics are automatically published. The default value of TRUE means they are automatically published, while FALSE indicates they are held in a pending state.
SQL> select dbms_stats.get_prefs(‘PUBLISH’,’stats’,’test_table’) from dual;
The database stores the published statistics in the DBA_TAB_STATS and the DBA_IND_STATS views. The database stores the pending statistics in the DBA_TAB_PENDING_STATS and the DBA_IND_PENDING_STATS views.
You can change the PUBLISH setting at either the schema or the table level. For example, to change the PUBLISH setting for the customers table in the SHschema, execute the statement:
EXEC DBMS_STATS.SET_TABLE_PREFS(‘SH’, ‘CUSTOMERS’, ‘PUBLISH’, ‘false’);
Subsequently, when you gather statistics on the customers table, the statistics will not be automatically published when the gather job completes. Instead, the database stores the newly gathered statistics in the USER_TAB_PENDING_STATS table.