Oracle 11g引入了INCREMENTAL选项，允许数据库仅扫描变化的分区来更新全局统计信息，降低资源消耗。当分区表的INCREMENTAL值设为TRUE且DBMS_STATS.GATHER_TABLE_STATS使用ESTIMATE_PERCENT参数时，此功能生效。增量维护可能导致SYSAUX表空间使用增加，但随着表和分区的增长，其性能优势将更加明显。设置INCREMENTAL选项需要使用dbms_stats.set_table_prefs等过程，并通过检查last_analyzed日期验证统计信息是否正确更新。
在11g中，数据库可以增量维护global统计信息，通过仅扫描那些有一定比例数据发生变化的分区，其他分区仍然使用老的统计信息。
about INCREMENTAL option:
INCREMENTAL |
Determines whether the global statistics of a partitioned table will be maintained without doing a full table scan. |
INCREMENTAL - This value determines whether or not the global statistics of a partitioned table will be maintained without doing a full table scan. With partitioned tables it is very common to load new data into a new partition. As new partitions are added and data loaded, the global table statistics need to be kept up to date. 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:
the
INCREMENTALvalue for the partitioned table is set toTRUE;the
PUBLISHvalue for the partitioned table is set toTRUE;the user specifies
AUTO_SAMPLE_SIZEfor
最低0.47元/天 解锁文章
扫一扫
2426
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
