Method and system for efficiently searching for free space in a table of a relational database having a clustering index

摘要

A method of searching a table in a database management system for free space for inserting a new record into the table. The table resides in a storage media, and includes a series of pages capable of storing a plurality of records. The table has a clustering index defined in a column of the table. The new record has a new record attribute for storing in the column of the table when the new record is stored in the table. The method comprises searching for a target page in the series of pages, the target page being selected to maintain clustering when the new record is inserted thereon. Once the target page has been located, it is searched for sufficient free space to accommodate the new record. If sufficient free space is found on the target page, then the new record is inserted on the target page. If sufficient free space is not found on the target page, then a target neighborhood of pages in the series of pages is searched for sufficient free space to accommodate the new record. The target neighborhood of pages surrounds the target page. If sufficient free space is found on a page in a target neighborhood of pages, then the new record is inserted on to this page in the target neighborhood of pages. If sufficient free space is not found in the target neighborhood of pages, then the method further comprises searching for sufficient free space to accommodate the new record outside the target neighborhood of pages. If sufficient free space is found outside the target neighborhood of pages, then the new record is inserted on a non-neighboring page outside the target neighborhood of pages. If sufficient free space is not found outside the target neighborhood of pages, then a new page having the new record is appended to the end of table.