In Netezza, when you update your table definition, like adding columns, Netezza creates a separate version of the table behind the scenes with the new schema.
New records then get inserted into the new version.
During query execution the multiple versions of the table are merged together using a UNION ALL operation.
To merge the two versions of your table together do a GROOM TABLE tablename VERSIONS;
How can I find the tables that are versioned?
SELECT tablename
FROM _v_table
WHERE RELVERSION<>0
Note:
If you have multiple versions of a table due to adding/dropping a column, you cannot use the groom command to clean up deleted rows until you first use the groom versions command to merge the multiple versions of the table into a single current version.
SELECT tablename
FROM _v_table
WHERE RELVERSION<>0
Note:
If you have multiple versions of a table due to adding/dropping a column, you cannot use the groom command to clean up deleted rows until you first use the groom versions command to merge the multiple versions of the table into a single current version.
When we modify tables all views go in-active. Even for cases where we just add a new column.
ReplyDeleteWill the Groom Versions command help with this at all.