Limitations When Creating Virtually Hashed Tables

Limitations for creating virtually hashed tables.

  • You cannot use create table on the segment that includes a virtually hashed table, since a virtually hashed table must take only one exclusive segment, which cannot be shared by other tables or databases.

  • Virtually hashed tables must have unique rows. Virtually hashed tables do not allow multiple rows with the same key column values because the SAP ASE server cannot keep one row in the hash region and another with the same key column value in the overflow clustered region.

  • truncate table is not supported. Use delete from table_name instead.

  • SQL92 does not allow two unique constraints on a relation to have the same key columns. However, the primary key clause for a virtually hashed table is not a standard unique constraint, so you can declare a separate unique constraint with the same key columns as the virtually hashed keys.

  • Because you cannot create a virtually hashed clustered index after you create a table, you also cannot drop a virtually hashed clustered index.

  • You must create a virtually hashed table on an exclusive segment. You cannot share disk devices you assign to the segments for creating a virtually hashed table with other segments.

  • You cannot create two virtually hashed tables on the same exclusive segment. The SAP ASE server supports 32 different segments per database. Three segments are reserved for the default, system, and log segments, so the maximum number of virtually-hashed tables per database is 29.

  • You cannot use the alter table or drop clustered index commands on virtually hashed tables.

  • Virtually hashed tables must use all-pages locking.

  • The key columns and hash factors of a virtually hashed table must use the int datatype.

  • You cannot include text or image columns in virtually hashed tables, or columns with datatypes based on the text or image datatypes.

  • You cannot create a partitioned virtually hashed table.