How to shrink or reorganize the table in HANA.

[Former Member]

Dear Experts,

After removing most of the records from the table, I need to release HWM.

What tools in HANA  can do it?

For example, in oracle I can use reorganization via brtools or use command "ALTER TABLE <table_name> SHRINK SPACE [COMPACT] [CASCADE];"

Thanks for your attention, I look forward to your comments.

Best Regards

[davidebruno · Accepted solution]

Hi,

for row store you  have to follow the

1813245 - SAP HANA DB Row store reorganization

[Former Member · Accepted solution]

Thanks for your reply,

This note consists information about online reorganization all tables.

Can I reorganize a single table? Or I have misunderstood?

[davidebruno · Accepted solution]

in HANA you can't reorg a single row-store table

[Former Member · Accepted solution]

That's correct up to SAP HANA SPS 08. Starting with SPS 09 can can reorganize individual row store tables using:

ALTER TABLE "<table_name>" RECLAIM DATA SPACE

This is e.g. helpful to resolve issues caused by the existence of multiple table containers (see SAP Note 2000002 -> "Bad performance on specific row store table, unexpected UNION ALL in execution plan").

[davidebruno · Accepted solution]

Thank you Martin! I didn't know this new functionality!

I think that to do the reorg of a table in production, we have to plan a lock of activities for this table, it's correct?

[Former Member · Accepted solution]

It's like for all DDL statements: They acquire a table lock and open changes can be escalated to table level locks while the DDL statement is waiting until the open changes are finished. Setting the lock timeout to a small value for the executing transaction can reduce the impact:

SET TRANSACTION LOCK WAIT TIMEOUT <ms>