Creating a virtually-hashed table

To create a virtually-hashed table, specify the maximum value for the hash region. This is the partial syntax for create table; the new parameters are shown in bold:

create table [database.[owner].]table_name 
. . .
             | {unique | primary key}
             using clustered
             (column_name [asc | desc] [{, column_name [asc | desc]}...])=
             (hash_factor [{, hash_factor}...])
                            with max num_hash_values key

Where:

• using clustered – indicates you are creating a virtually-hashed table. The list of columns are treated as key columns for this table.

• column_name [asc | desc] – because rows are placed based on their hash function, you cannot use [asc | desc] for the hash region. If you provide an order for the key columns of virtually hashed tables, it is used only in the overflow clustered region.

• hash_factor – required for the hash function for virtually-hashed tables. For the hash function, a hash factor is required for every key column. These factors are used with key values to generate hash value for a particular row.

• with max num_hash_values key – the maximum number of hash values that you can use. Defines the upper bound on the output of this hash function.

You can keep the hash factor for the first key as 1. The hash factor for all the remaining key columns is greater than the maximum value of the previous key allowed in the hash region multiplied by its hash factor.

Adaptive Server allows tables with hash factors greater than 1 for the first key column to have fewer rows on a page. For example, if a table has a hash factor of 5 for the first key column, after every row in a page, space for the next four rows is kept empty. To support this, Adaptive Server requires five times the amount of table space.

If the value of a key column is greater than or equal to the hash factor of the next key column, the current row is inserted in the overflow clustered region to avoid collisions in the hash region.

For example, t is a virtually-hashed table with key columns id and age, and corresponding hash factors of (10,1). Because the hash value for rows (5, 5) and (2, 35) is 55, this may result in a hash collision.

However, because the value 35 is greater than or equal to 10 (the hash factor for the next key column, id), Adaptive Server stores the second row in the overflow clustered region, avoiding collisions in the hash region.

In another example, if u is a virtually-hashed table with a primary index and hash factors of (id1, id2, id3) = (125, 25, 5) and a max hash_value of 200:

• Row (1,1,1) has a hash value of 155 and is stored in the hash region.

• Row (2,0,0) has a hash value 250 and is stored in overflow clustered region.

• Row (0,0,6) has a hash factor of 6 x 5, which is greater than or equal to 25, so it is stored in the overflow clustered region.

• Row (0,7,0) has a hash factor of 7 x 25, which is greater than or equal to 125, so it is stored in the overflow clustered region

This example creates a virtually-hashed table named orders on the pubs2 database on the order_seg segment:

create table orders(
id int,
age int,
primary key using clustered (id,age) = (10,1) with max 1000 key)
on order_seg

The layout for the data is:

• The order_seg segment starts on page ID 51200.

• The ID for the first data object allocation map (OAM) page is 51201.

• The logical page size is 2048 bytes

• For the logical page size of 2048 bytes, the maximum rows per page is 168.

• The row size is 10.

• The root index page of the overflow clustered region is 51217.

This example creates a virtually-hashed table named orders on the pubs2 database on the order_seg segment:

create table orders(
id int default NULL,
age int,
primary key using clustered (id,age) = (10,1) with max 100 key,
name varchar(30)
)
on order_seg

The layout for the data is:

• The order_seg segment starts on page ID 51200.

• The logical page size is 2048 bytes

• The ID for the first data OAM page is 51201.

• The maximum rows per page is 42.

• For the logical page size of 2048 bytes, the row size is 45.

• The root index page of the overflow clustered region is 51217.