收集有关分区表的统计信息

Question

Ultimately i need to know if this will be enough. In oracle, there is a setting on a table to incrementally gather statistics, rather than a full table.  Basically, it will only gather stats on partitions where the data has changed.  We need to make sure all partitioned tables have INCREMENTAL set to TRUE.

On Partitioning Tables just setting Incremental to true is enough or do we have to also set Publish command to true as well? If so how can i add it?
BEGIN
DBMS_STATS.SET_TABLE_PREFS ('ANT', 'S_WAREHOUSE_PRODUCT_FACT', 'INCREMENTAL', 'TRUE');
END

P Lease让我知道是否需要更改或添加到代码中。这对我正在做的事情有必要吗？

1) The PUBLISH value for the partitioned table is true.(Default is TRUE)
2)The user specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY when gathering statistics on the table.(Default is ESTIMATE_PERCENT=>AUTO_SAMPLE_SIZE and GRANULARITY=>AUTO)
How can i verify if tables already has publish set to true?
Can i leave default value as it is? Default is 
ESTIMATE_PERCENT=>AUTO_SAMPLE_SIZE and GRANULARITY=>AUTO

Answer 1

要么使用

BEGIN
DBMS_STATS.SET_TABLE_PREFS ('DW_FEI', 'INVOICE_HEADER_FACT', 'INCREMENTAL', 'TRUE');
END; 
/ 

或

EXEC DBMS_STATS.SET_TABLE_PREFS ('DW_FEI', 'INVOICE_HEADER_FACT', 'INCREMENTAL', 'TRUE');

但不能同时两者。

Answer 2

正如Wernfried指出的那样，EXEC是错误的。您可以在开始结束块中调用多个过程：

BEGIN
DBMS_STATS.SET_TABLE_PREFS ('DW_FEI', 'BMI_SALES_FACT', 'INCREMENTAL', 'TRUE'); 
DBMS_STATS.SET_TABLE_PREFS ('DW_FEI', 'AP_OPEN_HEADER_RAY', 'INCREMENTAL', 'TRUE')
DBMS_STATS.SET_TABLE_PREFS ('DW_FEI', 'AP_OPEN_HEADER_FACT', 'INCREMENTAL', 'TRUE');
END;
/

而且你不需要COMMIT，DBMS_STATS(和所有其他DML语句(自动(或隐式(为你执行此操作。作为证明：

CREATE TABLE t (i NUMBER);
SELECT * FROM USER_TAB_STAT_PREFS WHERE table_name='T';
BEGIN
DBMS_STATS.SET_TABLE_PREFS (NULL, 'T', 'INCREMENTAL', 'TRUE');
END; 
/

(在其他环节：(

SELECT * FROM USER_TAB_STAT_PREFS WHERE table_name='T';
TABLE_NAME PREFERENCE_NAME PREFERENCE_VALUE
T          INCREMENTAL     TRUE