International Business Machines Corporation

Nelson Hop Hing

Matthew Albert Huras

Michael Jeffrey Winer

Bruce Gilbert Lindsay

Matthew J Spilchen

Keriley Kay Romanufa

Catherine Suzanne McArthur

Jeffrey James Goss

Frank-Martin Haas

Leslie Anne Cranston

Sean W McKeough

Torsten W E Ziegler

Provided are techniques for reclamation of empty pages in database tables. In response to receiving a plurality of records for insertion into a database table, the plurality of records are inserted into one or more contiguous pages. In response to at least some of the plurality of records being deleted from the database table, the one or more contiguous pages are reclaimed by: relocating any records from the plurality of records remaining in the one or more contiguous pages and releasing the one or more contiguous pages.

Reclamation of empty pages in database tables

Implementation of append mode concurrent insert operations in tables in a page-based concurrent relational database management system is carried out by defining a set of last pages as a current cache into which rows may be inserted by appenders. Append mode insert operations for each page in the current cache are serialized. Appenders seek to access the pages in the current cache using a two-pass process in which latches are sought to be acquired conditionally in the first pass and unconditionally in the second pass. A shared data structure is provided to define a current cache and a next cache for a table supporting append mode inserts in the relational database. The shared data structure includes information reflecting the unused space for each page in the current cache.

Computer program and product for append mode insertion of rows into tables in database management systems

Method for append mode insertion of rows into tables in database management systems

A database table reorganization is defined to permit online access of the table during the reorganization. Records are reorganized in the database table by vacating records from a defined number of pages and then filling the pages with records in accordance with a desired ordering for the records. Temporary pointers to the new locations of moved records are used to prevent table scanner access to the database table from missing or duplicating records while scanning the database table during reorganization. Removal of the temporary pointers is synchronized with the completion of scanning of all table scanners that are commenced during a time when records are being moved as part of a vacating or filling step.

Online database table reorganization

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.

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

Growing community of inventors

Scarborough, Canada

Nelson Hop Hing