Netezza update records operation is costlier. IBM Netezza does not perform updates, but rather does deletes the records and inserts updated values.
When you run nzsql command to update record, Netezza marks the record being updated as logically deleted by setting current transaction value to the deletexid field, but does not delete it. This ensures that the database system adheres to the ACID properties of RDBMS SQL standards.
How Netezza Update Records in Tables?
Each record in Netezza contains two slots, one for createxid another for deletexid. Deletexid allows you to mark a record with current transaction for deletion. As of Netezza version 5.x, you were allowed upto 31 transaction for all tables.
Once a record is modified, it’s deletexid field value is updated from value 0 to current transaction id. In Netezza, any record with non-zero deletexid value is deleted. New record will always have value 0.
For examples, Consider the below scenario for your understanding purpose:
[ROW id][Create xid][Delete xid] //Typical row in Netezza
[R1][T1][0] // First time a record is loaded, record R1
// After some time, updating the same record
[R1][T1][T33] // Record R1 is updated. T33 – Current Transaction
[R33][T33][0] // New update record R33. Similar to a new record this has zero for Delete Xid
If the record is deleted, simply deletionxid will contain that transaction id.
To improve the performance, instead of updating the rows in a table, delete the row first and then insert the row. This approach is very very fast. Or create intermediate table with updated records, truncate and load the base table.
Remove Logically deleted records in Netezza
You have just learned how Netezza updates records. In fact, all the deleted records will still be holding space in Netezza disks.
You have to perform Netezza nzreclaim or Netezza GROOM operation to reclaim the disk space.
Read:
- Groom in Netezza Tables and Database
- Database ACID Properties and Explanation
- Netezza Table Locking and Concurrency
- nzsql Command and its Usage
- Netezza nzload Command and its Usage
Next question arises, is there any way we can get the deleted-records back? The answer is YES.
Undo a Netezza DELETE or UPDATE Operations
Netezza does soft deletes, this means your old data is really still there until you run the GROOM or nzreclaim command.
In normal circumstances, when you run a select query; you will not see rows that have a deletexid value 0. Set the session variable “show_deleted_records”, that allows you to see those deleted records. You can include these in script or run manually for that particular session.
set show_deleted_records = true
If you want to recover only the deleted records , just select where deletexid is not zero.
SELECT createxid,deletexid, * FROM table WHERE deletexid !=0;