Re: partitions : truncate/rename OR drop/recreate

Hi Kurt,

if you truncate a partition a global index will also be unusable.

If you use truncate keep storage you avoid any change in your space adminstration. So it is not only faster but also allows a completely static allocation strategy. Further, if you happened to code the partition name in any query for partition elimination or a partitioned view this query will become invalid if you drop it. This will not happen if you truncate it.

However, there are some things to consider for the drop + recreation:

If you ever created this partition nologging and if you have to restore and to recover that partition from a backup it will be marked as logically corrupt as well as its (local) indexes. They will appear in v$backup_corruption. The only way to get them usable again is to truncate (without keep storage) or to drop and recreate (truncate keep storage will make the data segment usable but not its index segments).

Martin

koert54 wrote:
>
> Hi,
>
> We're going to implement a partitioned table containing historical data. The
> table will contain 24 months of data - or 24 partitions. Now as we roll over
> and start storing our '25th' month of
> data, what is the best way handle this :
> 1. drop the oldest partition and recreate it
> 2. truncate the oldest partition and rename it
>
> I know that if you drop a partition you'll invalidate any global index and
> any local index will also
> be dropped ... but beside that, are there any other consequences I should
> keep an eye on ?
>
> Kind Regards,
> Kurt
Received on Sun Jul 01 2001 - 14:20:24 CDT