Initiating a Compression Estimate

Apply compression attributes to a selected tables to reduce the size and improve performance for a database.

  1. In the left pane of the Administration Console, expand ASE Servers > Schema Objects > Tables > User Tables.
  2. Click the Name field of the table, then click the drop-down arrow and select Compression Advisor.
  3. On the Storage screen:
    1. Select a sampling size for the selected table and click Apply.
      The sampling information reflects a sampling size for the selected table. To estimate the compressed size of the selected table, the compression advisor creates a temporary table and loads into it sample data from the table to be compressed.

      For large tables, a sample size of 10% may provide good results. However, for smaller tables, a larger sample size may provide better results.

    2. Select a database to use for the sampling process.
    The temporary table is created once you click Estimate. It is deleted when you close the Compression Advisor Results dialog.
  4. On the Table Attributes screen:
    1. Choose the level of data compression.
      • Row-level compression compresses individual rows in a table.
      • Page compression performs row-level compression first, then page-level compression.
      For uncompressed tables, data compression is, by default, set to page-level compression.
    1. If the table has one or more indexes, and the version is 16.0 or later, select the type of index compression.
    To compress large object (LOB) data, choose the compression level for FastLZ (100 –101) or ZLib (1–9).

    The LOB compression option is available for tables that use text, image, Unitext, or Java LOB datatypes.

  5. (Optional) On the Columns Attributes screen, for LOB columns, click Calculate to calculate the best LOB length for each LOB column based on the sampling size.
    1. Click Cancel to cancel the calculation and restore the original values.
    2. Once the calculation is complete, click Restore to restore the original values.
  6. (Optional) On the Columns Attributes screen, select individual column attributes to override the inherited table-level attributes. You can also choose to not compress individual columns.
    • You cannot change an in-row column to an off-row column.
    • You cannot reduce the in-row length.
    • You must set at least one column to be compressed.
  7. (Optional; only available for tables with indexes) In the Indexes Attributes window, select either page compression or no compression for individual indexes. Selected compression values override the inherited index-level compression attributes.
  8. Click Summary to see a comparison of the selected compression options for the table.
  9. Click Estimate to start the compression estimate calculation.
    The Results window automatically appears.
  10. Click Preview to show the compression attributes changes in SQL statements.
    Preview is available once the compression estimate has been initiated.