Optimal hash size limit

The UltraLite default maximum hash size of 4 bytes suits most deployments. You can increase the size to include more data with the row ID. However, this change could increase the size of the index and fragment it among multiple pages. This change can possibly increase the size of the database as a result. The impact of an increased maximum hash size depends on the number of rows in the table: for example, if you only have a few rows, a large index hash key would still fit on the index page. No index fragmentation occurs in this case.

When choosing an optimal hash size, consider the data type, the row data, and the database size (especially if a table contains many rows).

The only way to determine if you have chosen an optimal hash size is to run benchmark tests against your UltraLite client application on the target device. Observe how various hash sizes affect the application and query performance, in addition to the changes in database size itself.

Index hashing improves inserts, updates, deletes, and searches when the columns being indexed have a good distribution of values, such as strings that do not have a common prefix, at the cost of bigger index structures. Hashed indexes locate rows first by using the hash, and then by using direct row comparison to differentiate rows with the same hash value. If the hash size is sufficiently big, the hash uniquely identifies a row without reading and comparing the row. However, if the hash size is too big and the page size small, the index may need too many database pages.

The data type

To hash the entire value in a column, note the size required by each data type in the table that follows. UltraLite only uses the maximum hash size if it is necessary, and it never exceeds the maximum hash size you specify. UltraLite uses a smaller hash size if the column type does not use the full byte limit.

Data type	Bytes used to hash the entire value
LONG VARCHAR, DOUBLE, FLOAT, REAL, LONG BINARY, ST_GEOMETRY	Not hashed.
BIT, TINYINT	1
SMALLINT	2
INTEGER, DATE	4
BIGINT, DATETIME, TIME, TIMESTAMP, TIMESTAMP WITH TIMEZONE	8
DECIMAL, NUMERIC	Approximately the precision divided by two.
CHAR, VARCHAR	To hash the entire string, the maximum hash size in bytes must match the declared size of the column. In a UTF-8 encoded database, always multiply the declared size by a factor of 2, but only to the allowed maximum of 32 bytes. For example, if you declare a column VARCHAR(10) in a non-UTF-8 encoded database, the required size is 10 bytes. However, if you declare the same column in a UTF-8 encoded database, the size used to hash the entire string is 20 bytes. For UltraLite Java edition databases, each VARCHAR character is hashed using its UTF-8 representation, which may be 1-3 characters long. UltraLite Java edition uses (3 * n) bytes to hash a VARCHAR(n) column. However, you may specify a hash size of n bytes to hash all values if the VARCHAR(n) column only contains ASCII7 characters.
BINARY, VARBINARY	The maximum hash size in bytes must match the declared size of the column. For example, if you declare a column BINARY(30), the required size is 30 bytes.
UNIQUEIDENTIFIER	16

For example, if you set a maximum hash size of 6 bytes for a two-column composite index that you declared as INTEGER and BINARY (20) respectively, then based on the data type size requirements, the following changes occur:

The entire value of the row in the INTEGER column is hashed and stored in the index because only 4 bytes are required to hash integer data types.
Only the first 2 bytes of the BINARY column are hashed and stored in the index because the first 4 bytes are used by the INTEGER column. If these remaining 2 bytes do not hash an appropriate amount of the BINARY column, increase the maximum hash size.

The row data

The row values of the data being stored in the database also influence the effectiveness of a hashed index.

For example, if you have a common prefix shared among entries of a given column, you can render the hash ineffective if you choose a size that only hashes prefixes. In this case, choose a size that ensures more than just the common prefix is hashed. If the common prefix is long, consider not hashing the values at all.

When a non-unique index stores many duplicate values, and UltraLite cannot hash the entire value, the hash likely cannot improve performance.

The database size

Each index page has some fixed overhead, but the majority of the page space is used by the actual index entries. A larger hash size means each index entry is bigger, which means that fewer entries can fit on a page. For large tables, indexes with large hashes use more pages than indexes with small or no hashes. Requiring more pages increases the database size and degrades performance. The latter typically occurs because the cache can only hold a fixed number of pages, thereby causing UltraLite to swap pages.

The following table gives you an approximation of how the hash size can affect the number of pages required to store data in an index:

Table	Page size	Hash size	Number of entries	Pages required
Table A	4 KB	0	1200	3 pages
Table B	4 KB	32 bytes	116	3 pages
Table C	4 KB	32 bytes	1200 entries	11 pages

Set the hash size

You can set the maximum hash size in two ways:

To store a database default for the maximum size, set the max_hash_size creation parameter when you create your database. If you do not want to hash indexes by default, set this value to 0. Otherwise, you can change it to any value up to 32 bytes, or keep the UltraLite default of 4 bytes.
Override the default by setting a specific hash size when you create a new index. Use one of the following approaches:
- In Sybase Central, set the Maximum Hash Size property when creating a new index.
- With SQL, use the WITH MAX HASH SIZE clause in either the CREATE TABLE or CREATE INDEX statement.