International Business Machines Corporation

Akira Shibamiya

Donald J Haderle

Hong Sang Tie

Yun Yu Wang

Thomas Abel Beavin

Balakrishna Raghavendra Iyer

Robert William Lyle

Min Wang

James Zu-Chia Teng

James Alan Ruddy

Fen-Ling Lin

Bryan Frederick Smith

Steven John Watts

Paramesh Sampatrai Desai

Atul Chadha

Annie S Tsang

Terry Dennis Allen

Namik Hrle

James Z Teng

Julie Ann Watts

Josephine Miu Cheng

John Marland Garth

Melvin R Zimowski

Craig Alan Friske

Tammie Dang

John Joseph Campbell

William Robert Bireley

Allan Benjamin Lebovitz

Hsiuying Yen Cheng

Stephen Walter Turnbaugh

Akiko Hoshikawa

Jerome Quan Wong

Kathryn Ruth Zeidenstein

Maureen Mae McDevitt

Kou Horng Yang

Julie Chen

Michael John Bracey

James Zu-Chia Tseng

A method for fetching an ordered first N rows of a table, includes: reading a row in the table; determining that the read row qualifies as the first N rows of the table for rows read so far, and storing data of the read row; and determining an order of data of qualifying rows and storing the order. Only data in rows that qualify to be among the first N rows are ordered and stored. This provides a significantly more efficient processing. It eliminates tournament tree sorts, corresponding work file read and write I/O's, and associated CPU time. This reduces the time for the running of a query and improves the performance of other applications sharing the use of work files. Further, the improved performance is particular significant when the buffer sizes are within a page of records or rows, although multiple pages may be used.

Fast select for fetch first N rows with order by

A method and article of manufacture, implementing the method, allocates space for a dataset. The dataset has an initial area and zero or more additional allocated areas to provide space for storing the dataset. The size of a new additional area is determined. The new additional area is associated with a new area number, and the size of the new additional area is based on the new area number. Additional space for the dataset is allocated based on the size of the new additional area.

Computer-implemented method for allocating new additional area for the dataset in storage based on the size of the new additional area wherein if the new area number does not exceed clipping threshold, the size of a new additional area being greater than the size of each previously allocated additional area of the dataset

Aspects for improving response time for database query execution are described. The aspects include comparing an estimate of sort pages needed for a database query against at least one threshold. A determination of whether to perform a parallel sort for the database query based on the comparison occurs, wherein response time improvement by the parallel sort is balanced against resource overhead of the parallel sort.

Method and system for improving response time for database query execution

Disclosed is a method, system, and program for database query execution. A range of data pages is assigned to each of multiple parallel tasks. For each of the multiple parallel tasks, the range of data pages is mapped to one or more physical partitions, and a data partitioned secondary index partition associated with each of the one or more physical partitions is identified. Each of the multiple parallel tasks is executed to process the database query against the assigned range of data pages using the one or more data partitioned secondary index partitions.

Method, system, and program for optimizing database query execution

A method, apparatus, and article of manufacture of a computer-implemented parallel database loading system. The optimum number of tasks to be processed by the system is determined by identifying the memory constraints of the system, by identifying available processing capabilities, and by determining a number of load and sort processes to be started in parallel based on the identified memory constraints and processing capabilities. Optimizing the number of load and sort processes increases overall system processing speed.

Technique for determining an optimal number of tasks in a parallel database loading system with memory constraints

A method and system for formatting space in a storage device for a database system is disclosed. The method and system include formatting a first increment for creating a database file and asynchronously formatting at least one subsequent increment for the database file in a background of the database system.

Method and system for performing asynchronous preformatting in a database system

A reorganizer utility performs an online reorganization of a database that provides applications with concurrent access to data during an iterative process of log apply operations to a shadow version of the data. The reorganizer continues this iterative process of log apply operations until an estimate of an elapsed time necessary to complete a last iteration is less than or equal to a user-specified maximum length of time that read-only (RO) access may be provided to the data. When this threshold is reached, the reorganizer performs the last iteration, which includes draining all applications making modifications to the data; allowing read-only access to the data, performing log apply operations against the shadow version of the data, and then switching all of the applications to the shadow data.

Method for estimating the elapsed time required for a log apply process

A method, apparatus, and article of manufacture for a computer implemented insert system. Data is stored in a database stored on a data storage device connected to a plurality of computers. At least one look-aside space map is allocated to each of the computers. At each computer, when data is to be inserted into the database, a look-aside space map allocated to that computer is identified, and a free data page is located using the identified look-aside space map.

Insert performance on a multi-system transaction environment

Query optimization through the use of multi-column statistics to avoid the problems of non-indexed column correlation

The system, method, and program of this invention collects multi-column statistics, by a database management system, to reflect a relationship among multiple columns of a table in a relational database. These statistics are stored in the system catalog, and are used during query optimization to obtain an estimate of the number of qualifying rows when a query has predicates on multiple columns of a table. A multi-column linear quantile statistic is collected by dividing the data of multiple columns into sub-ranges where each sub-range has approximately an even distribution of data, and determining a frequency and cardinality of each sub-range. A multi-column polygonal quantile statistic is collected by dividing the data of multiple columns into sub-spaces where each sub-space contains approximately the same number of tuples, and determining a frequency and cardinality of each sub-space. The system catalog is accessed for the stored multi-column linear quantile statistic for a query having a single range predicate and at least one equal predicate to determine the selectivity value for the predicates of the query. The system catalog is accessed for the stored multi-column polygonal quantile statistic for a query having more than one range predicate. These statistics are used in various ways to determine the selectivity value for the predicates of the query.

Query optimization through the use of multi-column statistics to avoid

A relational data base management system includes a query processor that permits consideration of alternative user-specified access paths, which the processor will validate and incorporate into the query execution plan it otherwise generates. The optimizer of the query processor retrieves user-specified data that defines an access path for a query during bind operations. Any access path parameters that are invalid are replaced with processor-selected values.

User specification of query access paths in a relational database

The present invention provides a system and method for utilizing the proximity of keys in sequential or near sequential index probes to avoid complete index tree traversal. Page information from three pages (LAST, PARENT and NEXT) are stored in separate information fields within an Index Lookaside Buffer. The LAST information field contains information on the most recent leaf page accessed during an index probe in a read key or an insert key operation, the PARENT information field contains information on the parent page of the most recently accessed leaf page described in the LAST information field, and the NEXT information field contains information on the most recent leaf page accessed during a fetch-next key or delete key operation. On a subsequent index probe, the LAST, NEXT and PARENT information fields are sequentially analyzed to determine if the search key is contained within the leaf page described by the LAST or NEXT information fields or if the search key is contained within one of the leaf pages pointed to by the parent page described by the PARENT information field. If none of the LAST, NEXT or PARENT information fields identify the leaf page containing the search key then the default root-to-leaf traversal would commence.

Computer program product for avoiding complete index tree traversals in

System and method for avoiding complete index tree traversals in

A system and method for a computer system having a plurality of DBMS providing a coordinating and assisting function. If a DBMS performs a coordinating function, it is referred to as a coordinating DBMS. If a DBMS performs an assisting function, it is referred to as an assisting DBMS. Each coordinating DBMS receives a query from a user application, decomposes the query into multiple parallel tasks, and allocates the parallel tasks to all of the DBMSs in the system. Each assisting DBMS receives one or more parallel tasks from a coordinating DBMS, executes the parallel tasks and returns the results to the coordinating DBMS. The coordinating DBMSs and assisting DBMSs access one or more storage devices. Furthermore, the present invention comprises a system and method for dynamically disabling a parallel mode on the coordinating DBMSs and the assisting DBMSs.

Query parallelism in a shared data DBMS system

This invention measures the degree of clustering of an index for a relational data base table, estimates the number of physical page accesses required to access the table using a partial index scan using the index, and selects the index providing the fastest access path to the table. The degree of clustering is measured as follows:

Access path optimization using degrees of clustering

Growing community of inventors

Los Altos, CA, United States of America

Akira Shibamiya