About Cleaning up Load Isolated Tables - Advanced SQL Engine - Teradata Database

Load Isolation uses row versioning to ensure that only the most recently committed data is retrieved. Logically deleted rows are created as an artifact of row versioning and, as a result, a load-isolated table typically grows faster than a table without load isolation. A DBA must physically delete the logically deleted rows at regular intervals to reclaim disk space and improve query performance.

Larger tables with more concurrent reads during updates will require more frequent cleanup. When you collect summary statistics on your load-isolated tables, monitor the logically deleted rows (DelRowCount) as a percentage of physical rows (PhyRowCount). Perform cleanup when the table contains a significant percentage of logically deleted rows or you notice degraded query performance on the table while modifications occur.

To clean up logically deleted rows, you can use either the RELEASE DELETED ROWS option of an ALTER TABLE request or issue a stored procedure to perform cleanup on cascaded, logically deleted rows in load-isolated tables and underlying join indexes and secondary indexes.

Alter Table Cleanup Method

Use the RELEASE DELETED ROWS option in an ALTER TABLE request to interactively clean up logically deleted rows from a load-isolated table. For example:

ALTER TABLE  Employee  RELEASE DELETED ROWS;

For details on the syntax, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

Stored Procedure Cleanup Method

Use a stored procedure to clean up logically deleted rows from multiple load-isolated tables and their underlying join indexes and secondary indexes. Use LDI_Clean_ANSI in ANSI mode and LDI_Clean in Teradata mode. The input and output is the same for both ANSI and Teradata mode. (These stored procedures are created typically during Vantage installation but can be added later using the DIPDEM or DIPPOST scripts.)

The following example uses LDI_Clean in Teradata mode:

call ldi_clean('database_name', 'table_name','Reset_flag_value');

where Reset_flag_value is either ‘Y’ or ‘N’ to specify whether you want to reset the incremental value of the current load to 0. For example:

call ldi_clean('finance', 'acct_payable','y');

Teradata responds with the following output:

acct_payable  altered,  finance  altered