Tuesday, 9 December 2014

Netezza Groom Table to free table space - Netezza Administration

Netezza Groom Table command to reclaim table space -  Netezza Administration

In Netezza when a record is updated, it marks the original record for deletion and inserts a new record with the updated values. So basically it is doing a logical delete. The records marked for deletion are not visible when you do a simple select from a table. However, those records marked for deletion are still available till you groom the table. The same thing happen when you delete records from a table. Those records stay still a table is groomed.  If a table is not groomed regularly it can impact the performance since those deleted records also need to be scanned. Hence, Grooming tables is essential as part of Netezza Administration.

If you want to remove those logically deleted records and free up space, then groom the table.

A simple groom command to reclaim all deleted records would be:


GROOM TABLE <tablename> RECORDS ALL;


Groom command to reorganize new data that is added  to previously groomed table is :

GROOM TABLE <tablename> RECORDS READY;


Note:
1) You do not have to groom a table after truncating a table since truncating a table deletes all the records permanently

2) Groom is non blocking however it is resource intensive so better to schedule it weekly or monthly or during backup operation.

3) Netezza Groom replaces reclaim operation so you do not have to use reclaim operation after groom.

You can find other options available for the groom command here:
https://www-304.ibm.com/support/knowledgecenter/SSULQD_7.1.0/com.ibm.nz.dbu.doc/r_dbuser_groom_table.html