Repolulating the tables with multi-million rows for reduced redo logs

[mike9]

APPEND use direct path and appends the data after the HWM.

Truncate with reuse storage would not bring down HWM. So not recommended to use when you are using APPEND hint.

Thanks,

Truncate allways brings the HWM down.

[vnktummala]

Hi Mike,

Please see the REUSE STORAGE section from this link.

Regards,

[mike9]

Hi Mike,

Please see the REUSE STORAGE section from this link.

Regards,

Where is it written that the HWM is not reset?

How should a truncate work without reseting the HWM?

The REUSE STORAGE option is related to the allocated exetens not to the HWM.

Regards

[vnktummala]

If the extents are not de-allocated then the HWM will at the end correct? In this case if we are using APPEND hint then oracle start allocating the space for new records after HWM which wastes some storage.

Please correct me if I am wrong.

Regards,

[mike9]

Hi,

There is no direct relation between the HWM and the number of extents.

An extent is just a logical space allocated to a table/segment.
The HWM informs from which point on all blocks are empty and unformatted.

The reason why a truncate is so fast is because Oracle just resets the HWM.
With the REUSE STORAGE option the extent allocated to the table are not removed. The main advantage of it is that the extents don't need to be reallocated to the table when data are inserted again. However I don't think that you got a real performance advantage when using a locally managed tablespace.

Regards