通过分区交换（Oracle 10g）加载表

I have a few questions about optimizing this type of load.

我有一些关于优化此类负载的问题。

One builds a new table of data to be loaded into a partitioned table and then builds the indexes on this new table.

一个构建要加载到分区表中的新数据表,然后在此新表上构建索引。

Should you build the index with the COMPUTE STATISTICS option or use the Cascade option of the DBMS_Stats?

您应该使用COMPUTE STATISTICS选项构建索引还是使用DBMS_Stats的Cascade选项?
Should you gather stats on the table before the swap or on the partition after the swap?

你应该在交换之前在桌子上收集统计数据还是在交换之后收集分区上的统计数据?
If you do it after the swap and you specify the partition name in the parameter list, what interplay does the granularity parameter have? For instance, if I specify a partition name and then set granularity to 'GLOBAL AND PARTITION' does that do Global at all? Does it do just that one partition?

如果在交换后执行此操作并在参数列表中指定分区名称,则粒度参数具有哪些相互作用?例如,如果我指定一个分区名称,然后将粒度设置为“GLOBAL AND PARTITION”,那么Global会做什么吗?它只做那个分区吗?

2 个解决方案

#1

Should you build the index with the COMPUTE STATISTICS option or use the Cascade option of the DBMS_Stats?

您应该使用COMPUTE STATISTICS选项构建索引还是使用DBMS_Stats的Cascade选项?

If this is a data warehouse then first consider not gathering statistics at all, and using dynamic sampling. Second, if you do gather statistics then by all means use compute statistics on the index.

如果这是一个数据仓库,那么首先考虑不收集统计数据,并使用动态采样。其次,如果你收集统计数据,那么一定要使用索引上的计算统计数据。

Should you gather stats on the table before the swap or on the partition after the swap?

你应该在交换之前在桌子上收集统计数据还是在交换之后收集分区上的统计数据?

Gather statistics on the new-data table before the swap to get partition statistics on the new data -- gather statistics on the partitioned table afterwards to gather table statistics

在交换之前收集有关新数据表的统计信息以获取有关新数据的分区统计信息 - 之后收集分区表的统计信息以收集表统计信息

If you do it after the swap and you specify the partition name in the parameter list, what interplay does the granularity parameter have? For instance, if I specify a partition name and then set granularity to 'GLOBAL AND PARTITION' does that do Global at all? Does it do just that one partition?

如果在交换后执行此操作并在参数列表中指定分区名称,则粒度参数具有哪些相互作用?例如,如果我指定一个分区名称,然后将粒度设置为“GLOBAL AND PARTITION”,那么Global会做什么吗?它只做那个分区吗?

See above.

Seriously, give no statistics and dynamic sampling a chance.

说真的,没有统计数据和动态抽样的机会。

#2

DBMS_STATS is considered the proper way to calculate statistics for this version. Building the index with the COMPUTE STATISTICS is doable, but usually you want to calculate all your stats at one time and take snapshots.

DBMS_STATS被认为是计算此版本统计信息的正确方法。使用COMPUTE STATISTICS构建索引是可行的,但通常您希望一次计算所有统计数据并拍摄快照。
You want to gather stats after the swap. This way the optimizer will make the best guess for executing queries using that partitions' data.

您希望在交换后收集统计信息。这样,优化器将使用该分区的数据进行最佳猜测。
Why would you put both?

你为什么要放两个?

#1