The options available depend on the version of the database platform.
● DB Space: Specifies the name of the database where the table data is stored.
● Segment: Specifies the name of the segment where the table is stored.
● Locking Scheme: Specifies the locking scheme to implement for DDL transactions.
• ALLPAGES: Locks the data pages and index pages affected by the query.
• DATAPAGES: Locks the data pages but not the index pages, and the lock is held until the end of the transaction.
• DATAROWS: Locks the individual rows on data pages; rows and pages are not locked. This scheme requires more overhead but is beneficial for small tables with much contention.
● Max Rows per Page: Specifies the maximum number of rows per page.
● Reserve Page Gap: Specify the reserve gap parameters.
● Identity Gap: Controls the allocation of ID numbers and potential gaps resulting from restarting the server. Creates ID number blocks of a specific size which improves performance by reducing contention for the table. Overrides the server-wide identity burning set factor. If the identity gap is set to 1000m the server allocates the first 1000 numbers and stores the highest number of the block to disk. When all the numbers are used, the server takes the next 1000 numbers. If after inserting row 2050 the power fails, then when the server restarts it’ll start with ID 3000, leaving an ID gap of 950 numbers. Each time the server must write the highest number of a block to disk, performance is affected so you must find the best setting to achieve the optimal performance with the lower gap value acceptable for your situation.
● Replacement Strategy: Select to use the fetch-and-discard (or MRU) replacement strategy that reads pages into cache at the most recently-used (MRU) end of the buffer, so it does not flush other pages. But these pages remain in cache a much shorter time, so subsequent queries are less likely to find the page in cache.
● Prefetch Strategies: Enables or disables large I/O for a session.