The identity_gap parameter controls the size of identity gaps for a particular table.
For example, if you create a table named books that includes all the books in a bookstore, each book must have a unique ID number, which SAP ASE automatically generates. books includes an IDENTITY column that uses the default numeric value of (18, 0), providing a total of 999,999,999,999,999,999 ID numbers. The identity burning set factor configuration parameter uses the default setting of 5000 (.05 percent of 999,999,999,999,999,999), which means that SAP ASE allocates blocks of 500,000,000,000,000 numbers.
The server allocates the first 500,000,000,000,000 numbers in memory and stores the highest number of the block (500,000,000,000,000) on the table’s OAM page. When all the numbers are assigned to rows or burned, SAP ASE takes the next block of numbers (the next 500,000,000,000,000), starting with 500,000,000,000,001, and stores the number 1,000,000,000,000,000 as the highest number of the block.
If the server fails after row number 500,000,000,000,022, only numbers 1 through 500,000,000,000,022 were used as ID numbers for books. Numbers 500,000,000,000,023 through 1,000,000,000,000,000 are burned. When SAP ASE starts again, it creates ID numbers starting from the highest number stored on the table’s OAM page plus one (1,000,000,000,000,001), which leaves a gap of 499,999,999,999,978 ID numbers.
Creating the books table with an identity_gap value of 1000, overrides the server-wide identity burning set factor setting that resulted in blocks of 500,000,000,000,000 ID numbers. Instead, ID numbers are allocated in memory in blocks of 1000
The server allocates the first 1000 numbers and stores the highest number of the block (1000) to disk. When all the numbers are used, SAP ASE takes the next 1000 numbers, starting with 1001, and stores the number 2000 as the highest number.
If SAP ASE fails after row number 1002, it uses the numbers 1000 through 1002: numbers 1003 through 2000 are lost. When you restart SAP ASE, it creates ID numbers starting from the highest number stored on the table’s OAM page plus one (2000), which leaves a gap of only 998 numbers.
You can significantly reduce the gap in ID numbers by setting the identity_gap for a table instead of using the server-wide table burning set factor. However, if you set this value too low, each time the server must write the highest number of a block to disk, which affects performance. For example, if identity_gap is set to 1, which means you are allocating one ID number at a time, the server must write the new number every time a row is created, which may reduce performance because of page lock contention on the table. You must find the best setting to achieve the optimal performance with the lowest gap value acceptable for your situation.