ALTER TABLE and CREATE TABLE statement have been changed

Details

Version 10.1 introduces a new method of compressing data rows that use page-level compression dictionaries and a table-level compression dictionary called adaptive row compression. In previous releases, the method of compressing data rows used a table-level compression dictionary and it is now called classic row compression.

The COMPRESS YES ADAPTIVE or COMPRESS YES clauses on the CREATE TABLE and ALTER TABLE statements enable adaptive row compression on a table. The new COMPRESS YES STATIC clause enables classic row compression. The COMPRESS YES clause continues to be the default. However, it has a different behavior than in previous releases because it enables adaptive row compression. In previous releases, the COMPRESS YES clause enabled classic row compression.

After enabling row compression on an existing table, write operations only result in compressed data rows. Existing data on the table remains in uncompressed format. To compress the existing data, you must reorganize the table or reload the data to rebuild the page-level compression dictionaries and table-level compression dictionary.

In Version 10.1, the IMPLICITLY HIDDEN clause in the ALTER TABLE and CREATE TABLE statements can be used in any type of column. In previous releases, you can only specify this clause for ROW CHANGE TIMESTAMP columns. For more details on tables that use IMPLICITLY HIDDEN columns, see "Time period and transaction start columns for system-period temporal tables".

Resolution

If you have scripts or applications that issue the ALTER TABLE or CREATE TABLE statements with the COMPRESS YES clause, make sure that you add the STATIC or ADAPTIVE keyword to explicitly indicate the table compression method that you want.

After upgrading your databases from previous releases, to enable adaptive row compression on existing tables, issue the ALTER TABLE statement with the COMPRESS YES ADAPTIVE clause and rebuild the compression dictionaries. For more details, see "Improved table compression rates with greater ease of use". To continue to enable classic row compression, you must use the COMPRESS YES STATIC clause.

Start using hidden columns in new and existing tables. To add hidden columns to existing tables, use the LOAD, IMPORT, or EXPORT commands with the includeimplicitlyhidden modifier to populate them.