Page-Level Compression

Use page-level compression to compress the amount of data redundancy on a page.

When you specify page-level compression for regular data, SAP ASE performs row-level compression first, then page-level compression.

Data pages often include repeated information (for example, the same date, time, or department ID). Instead of storing the same value multiple times, page-level compression lets you store these values in a single place and use a symbol on the data page to refer to them.

A number of techniques for page-level compression are used:

Extracting repetitive information from variable-length byte strings and replacing them with shorter symbols.

When you insert a new row into a data page, the data in the columns is compared with the symbols in the page dictionary. If a match is found in the dictionary for the new data, the dictionary symbol is stored instead of the data, and the row is compressed. When the data is retreived, the symbol indicates the appropriate data. A page dictionary can include multiple entries, each with a different symbol that compresses a different piece of information.

Extracting and removing short, duplicate values that use fixed-length columns from the rows.

If a fixed-length column includes a high number of duplicates, SAP ASE stores the duplicate value in the page index, and uses a status bit in the row to indicate that this value is stored in the page index and is available for compression. When you retrieve data from the row, the status bit indicates the value that SAP ASE includes in the result set.

A page index may contain multiple entries for different duplicate values in the page.

For example, if you create this table:

create table order_line (
   order_id int,
   disp_id tinyint,
   width_id smallint,
   number tinyint,
   info_id int,
   supply smallint,
   delivery datetime,
   quantity smallint,
   amount float,
   dist_info char(24))
lock datapages

And insert this data:

682, 1, 7, 11, 30000, 7, 'Dec 2 2008 1:19PM', 5, 290, 'Houston')
748, 1, 7, 12, 93193, 7, 'Sep 27 2009 1:15PM', 5, 9900, 'Bakersfield')
239, 1, 7, 13, 50383, 7, 'Aug 18 2008 11:47AM', 5, 8480, 'Modesto')
594, 1, 7, 14, 70901, 7, 'Aug 19 2008 10:37AM', 5, 84840, 'Houston')
849, 1, 7, 1, 3459, 7, 'July 10 2010 3:15PM', 5, 940, 'Alberta')
994, 1, 7, 2, 1232, 7, 'Jan 3 2010 2:15PM', 5, 848, 'Sonoma')
219, 1, 7, 3, 55341, 7, 'Feb 12 2008 9:26AM', 5, 4884, 'Vallejo')
004, 1, 7, 4, 98313, 7, 'Jan 19 2007 2:05PM', 5, 4484, 'Houston')
229, 1, 7, 5, 1347, 7, 'Aug 8 2009 3:37PM', 5, 448, 'Bakersfield')
394, 1, 7, 6, 51276, 7, 'Nov 10 2009 1:38PM', 5, 4473, 'Napa')
119, 1, 7, 1, 18089, 7, 'Oct 29 2009 12:56PM', 5, 312, 'Los Angeles')
938, 1, 7, 2, 38396, 7, 'June 1 2009 3:46PM', 5, 2248, 'Houston')

The disp_id, width_id, supply, and quantity columns all contain duplicate values (1, 7, 7, and 5), that are all short fixed-length columns, and candidates for page index compression.

For char and varchar columns, frequently used characters are encoded with a representation that takes less storage.

If the row length after compression exceeds the original row length, the original row is used instead of the compressed row.

SAP ASE analyzes the data and automatically selects the appropriate method of page-level compression.

Compression does not automatically occur on a table configured for page-level compression until you insert a row that causes the page to become full.