Examine this command:
SQL > exec DBMS_STATS.SET_TABLE_PREFS (‘SH’, ‘CUSTOMERS’, ‘PUBLISH’, ‘false’);
Which three statements are true about the effect of this command?
A.
Statistics collection is not done for the CUSTOMERS table when schema stats are gathered.
B.
Statistics collection is not done for the CUSTOMERS table when database stats are gathered.
C.
Any existing statistics for the CUSTOMERS table are still available to the optimizer at parse time.
D.
Statistics gathered on the CUSTOMERS table when schema stats are gathered are stored as pending
statistics.
E.
Statistics gathered on the CUSTOMERS table when database stats are gathered are stored as pending
statistics.
Explanation:
* SET_TABLE_PREFS Procedure
This procedure is used to set the statistics preferences of the specified table in the specified schema.
* Example:
Using Pending Statistics
Assume many modifications have been made to the employees table since the last time statistics were
gathered. To ensure that the cost-based optimizer is still picking the best plan, statistics should be gathered
once again; however, the user is concerned that new statistics will cause the optimizer to choose bad plans
when the current ones are acceptable. The user can do the following:
EXEC DBMS_STATS.SET_TABLE_PREFS(‘hr’, ’employees’, ‘PUBLISH’, ‘false’);By setting the employees tables publish preference to FALSE, any statistics gather from now on will not be
automatically published. The newly gathered statistics will be marked as pending.