International Business Machines Corporation

Allan Benjamin Lebovitz

Yun Yu Wang

Fen-Ling Lin

Li-Mey P Lee

Yao-Ching Stephen Chen

Hubertus Franke

Hong Min

Guogen Zhang

You-Chin Gene Fuh

Mengchu Cai

Yefim Shuf

Lee-Chin Hsu Liu

Jason Alexander Cu

Akiko Hoshikawa

Binghua Zhen

Terence Patrick Purcell

Akira Shibamiya

Christopher John Crone

Yao Ching Stephen Chen

Brian L Baggett

Provided are techniques for reduced fixed length sort of variable length columns. A fixed length sort is performed to sort a table having rows for a query, wherein the fixed length sort is based on a sort key comprised of a variable length column. A pre-determined number of the rows are read to identify the variable length column as a candidate for truncation. A length for truncating the variable length column is determined based on database statistics and based on the reading of the pre-determined number of the rows. The variable length column is truncated to the determined length to output a truncated variable length column for the sort key. The rows are sorted on the sort key having the truncated variable length column. Results are generated using the sorted rows and returned for the query.

Reduced fixed length sort of variable length columns

A sequence of one or more input objects are sorted by identifying a property that is exhibited by a sequence of one or more input objects, determining whether each input object from the sequence of one or more input objects exhibits the property, storing each of the one or more input objects into a buffer, wherein an input object is stored in a first buffer if it exhibits the property and an input object is stored in a second buffer if it does not exhibit the property, sorting each of the one or more input objects in each buffer, and merging the one or more input objects in each buffer into a sequence of one or more input objects.

Method for sorting data

An apparatus and method for efficiently performing a query sort on a data set with duplicate key values is disclosed. The method includes identifying unique key values for a key in a data set after determining that a number of duplicate key values for the key exceed a predefined threshold. The method also includes recording an association of each unique key value with a record in the data set and sorting unique key values. The method further includes storing the unique key values in a sorted order, wherein each unique key value is associated with an appropriate record in the data set.

System and method for an efficient query sort of a data stream with duplicate key values

A system and computer readable medium for executing a query to access data stored in a database, wherein the query includes a plurality of DISTINCT keys, is disclosed. The system and computer readable medium includes a capture module for identifying each of the plurality of DISTINCT keys in the query and a sort module coupled to the capture module for determining if more than one sort is needed to execute the query, performing a first DISTINCT operation on a first DISTINCT key of the plurality of DISTINCT keys, storing data fetched from the first DISTINCT operation in a master workfile only if more than one sort process is needed to execute the query, and utilizing the master workfile to perform subsequent DISTINCT operations on the other of the plurality of DISTINCT keys.

System for executing a query having multiple distinct key columns

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

Disclosed is method for processing an aggregate function. Rows that contain a reference to intermediate result structures are grouped to form groups. For each group, aggregate element structures are formed from the intermediate result structures and, if the aggregate function specifies ordering, the aggregate element structures are sorted based on a sort key.

Optimizing aggregate processing

A method for executing a query to access data stored in a database, wherein the query includes a plurality of DISTINCT keys, is disclosed. The method of the present invention includes providing a first DISTINCT operation on a first DISTINCT key, storing data fetched from the first DISTINCT operation in a master workfile if more than one sort process is needed to execute the query, and utilizing the master workfile to perform subsequent DISTINCT operations for the other DISTINCT keys.

Method for executing a query having multiple distinct key columns

Disclosed is a method, system, and program for processing an aggregate function. Rows that contain a reference to intermediate result structures are grouped to form groups. For each group, aggregate element structures are formed from the intermediate result structures and, if the aggregate function specifies ordering, the aggregate element structures are sorted based on a sort key.

Growing community of inventors

Morgan Hill, CA, United States of America

Allan Benjamin Lebovitz