Table truncation with lob option

[Former Member]

Hi All,

We have a table where the LOB data is on the disk, we have truncated the table but the space occupied shows up as some amount of GB still.

Is there a way to truncate the table where the data on the disk also gets deleted immediately?

This is a row store table with LOB data on the disk.

[Former Member · Accepted solution]

Can you check if the LOB space is still allocated after several hours / one day? Normally I would expect that the LobGarbageCollector cleans up asynchronously after some time.

[Former Member · Accepted solution]

Hi Martin,

Thanks a ton for the response. To be honest LobGarbageCollector always shows up as inactive. We had done the truncation yesterday still the LOB data shows up data consumed almost 25 % of the initial space occupied.

[Former Member · Accepted solution]

Can you tell me how you determine the current LOB size and what the exact result is?

[Former Member · Accepted solution]

Martin,

Standard query for the table using M_TABLE_LOB_FILES and M_TABLE_PERSISTANCE_LOCATIONS.

It took a long time to drop the data from the disk. Is this expected behavior?

[Former Member · Accepted solution]

One more question: Which SAP HANA Revision do you use?

[Former Member · Accepted solution]

Hi Martin,

Rev 82. We will be upgrading to rev 85 shortly.

[Former Member · Accepted solution]

Can you check via SAP Note 2007021 if there are orphan LOB files?

[Former Member · Accepted solution]

Will check Martin, but is the LOB truncation slow?

[Former Member · Accepted solution]

Not really.

[Former Member · Accepted solution]

But martin, eveidence suggests that LOB dropping takes a long amount of time.

[Former Member · Accepted solution]

How much time per record? In any case I would suggest to check the same on a more recent SAP HANA Revision than 82, some improvements in the area of LOB handling came in over time.

[Former Member · Accepted solution]

Martin,

Business will hate this answer for sure. We cannot upgrade, there are so many testing cycles involved.

The number of records is not high less than 200,000. But it takes about 2 days to drop around 150 GB of data in the table which is LOB data.

[Former Member · Accepted solution]

If it is a bigger issue, you can open a SAP incident so that a more detailed performance analysis can be performed. 2 days for 150 GB sounds for me like there is somewhere a bottleneck.

[Former Member · Accepted solution]

Hi martin,

Have opened the SAP message. Yes you are very correct about the LOB deletion reclaim storage part.

Thanks so much for the help martin.