You have a range-partitioned table in your database. Each partition in the table contains the sales data for a quarter. The partition related to the current quarter is modified frequently, whereas other partitions undergo fewer data manipulations. The preferences for the table are set to their default values. You collect statistics for the table using the following command at regular intervals:
SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(‘SH’,’SALES’,GRANULARITY=>’AUTO’);
You need statistics to be collected more quickly.
How do you achieve this?
A.
Set the PUBLISH preference to TRUE for the partition table.
B.
Set the NO_VALIDATE preference to TRUE for the partition table.
C.
Set the INCREMENTAL preference to TRUE for the partition table.
D.
Increase the value of the STALE_PERCENT preference for the partition table.
Explanation:
Oracle will update the global table statistics by scanning only the partitions that have been
changed instead of the entire table if the following conditions hold:
INCREMENTAL value for the partitioned table is set to TRUE
PUBLISH value for the partitioned table is set to TRUE;
User specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for
GRANULARITY when gathering statistics on the table
If the INCREMENTAL value for the partitioned table was set to FALSE (default value), a full
table scan is used to maintain the global statistics which is a much more resource intensive
and time-consuming operation for large tables.