Creating Indexes on Compressed Table

Considerations that apply when creating indexes on compressed tables.

  • If a table requires sorting, the SAP ASE server compresses all rows that are available for compression during the data copy operation.

  • The SAP ASE server does not perform a data copy if you create a clustered index using sorted_data, and does not compress any data rows while it builds the clustered index.

  • The SAP ASE server does not compress index key values: It compresses only the values in the data rows.

  • You may select index key columns and create unique indexes even if the key columns are compressed. To perform nonclustered uniqueness checks, examine the uncompressed index keys in an index page.

  • The SAP ASE server uses the uncompressed index key and row formats to verify support for ignore_dup_key, ignore_dup_row, and allow_dup_row.

  • The SAP ASE server applies the fillfactor parameter only for row-level compression.

  • When it applies the fillfactor parameter to the data pages of an allpages-locked clustered index, the SAP ASE server considers the:

    • Final compressed row format

    • Space required for compression

    The SAP ASE server may additionally compress the page space used with subsequent page compression operations, resulting in lower fillfactor values.

  • The SAP ASE server applies the respagegap parameter at the page level so it is not affected by compression.

  • max_rows_per_page includes a page’s data rows only, and not the hidden page-dictionary, index, and character-encoding entries.

Parent topic: create index