select table_name, subpartition_name, global_stats, last_analyzed, num_rows 2 from dba_tab_subpartitions 3 where table_name='TEST_TAB1' 4 and table_owner='TESTUSER' 5 order by 1, 2, 4 desc nulls last; TABLE_NAME SUBPARTITION_NAME GLO LAST_ANALYZED NUM_ROWS ------------------------------ ------------------------------ --- -------------------- ---------- TEST_TAB1 P_20100131_GROT NO TEST_TAB1 P_20100131_HALO NO TEST_TAB1 P_20100131_JUNE NO TEST_TAB1 P_20100131_OTHERS NO TEST_TAB1 P_20100209_GROT NO 28-FEB-2010 3 TEST_TAB1 P_20100209_HALO NO 28-FEB-2010 3 TEST_TAB1 P_20100209_JUNE NO 28-FEB-2010 3 TEST_TAB1 P_20100209_OTHERS NO 28-FEB-2010 3 36 rows selected.The subpartition stats are ok, then, but the aggregation process hasn't happened and that's because _miminal_stats_aggregation is set to TRUE (the default) which instructs Oracle to minimise aggregation operations and one of the ways it does so is to aggregate statistics as a result of a partition exchange operation but to leave you to do that manually by gathering stats on the table partition.Actually, before looking at any recent features, let me introduce one more aspect of the existing aggregation approach used by Oracle.

To avoid the generation of logs and make this faster, Just before the deletion, you may alter the partition as and then proceed with deletion and truncation.

But for partitions containing a large percentage of the total data in table, this will still be slower If you have a global index on the partition, UPDATE INDEXES will truncate this index as well.

The Solar Winds Renewal Program comes with a host of benefits including the most recent product updates, 24/7 technical support, virtual instructor-led training and more.

I have to truncate a partition (392,000 records) of a table with 20.000.000 records.

Now I'll use LOAD_TAB1 to repeat the same process for the four different subpartitions - INSERT data into LOAD_TAB1, gather stats on it and then exchange it with the relevant subpartition of TEST_TAB1.

select table_name, global_stats, last_analyzed, num_rows 2 from dba_tables 3 where table_name='TEST_TAB1' 4 and owner='TESTUSER' 5 order by 1, 2, 4 desc nulls last; TABLE_NAME GLO LAST_ANALYZED NUM_ROWS ------------------------------ --- -------------------- ---------- TEST_TAB1 NO SQL select table_name, partition_name, global_stats, last_analyzed, num_rows 2 from dba_tab_partitions 3 where table_name='TEST_TAB1' 4 and table_owner='TESTUSER' 5 order by 1, 2, 4 desc nulls last; TABLE_NAME PARTITION_NAME GLO LAST_ANALYZED NUM_ROWS ------------------------------ ------------------------------ --- -------------------- ---------- TEST_TAB1 P_20100131 NO TEST_TAB1 P_20100201 NO TEST_TAB1 P_20100202 NO TEST_TAB1 P_20100203 NO TEST_TAB1 P_20100204 NO TEST_TAB1 P_20100205 NO TEST_TAB1 P_20100206 NO TEST_TAB1 P_20100207 NO TEST_TAB1 P_20100209 NO 9 rows selected.

Taking a look at the Oracle Docs it gives me three options: 1- (This is what I'm doing) Are the 37 indexes you have on the table global indexes?

Where you truncate and then rebuild This method is most appropriate if the partition that you are truncating has at least 10% of the total data in the table.

Which means we need to understand how the stats might be gathered and then aggregated up to the partition and table-level stats.

Although there might be other approaches, I'd say that there are two distinct approaches you are likely to use.1) Create a temporary load table, load it with data, gather statistics on it and then exchange it with the relevant subpartition in the real table.2) Create a temporary load table, load it with data, exchange it with the relevant subpartition and then gather stats on the subpartition.

But a quick reason why I am more of an "exchange and then gather stats" person is that I don't always have my exchange table indexed in the same way as the partitioned table and if Oracle was say gathering column level stats on the indexed columns I would not have a like for like statistics exchange...