International Business Machines Corporation

David C Sharpe

Vijayshankar Raman

Gopi K Attaluri

Calisto Paul Zuzarte

Richard Sefton Sidle

Liping Zhang

Bruce Gilbert Lindsay

Nattavut Sutyanyong

Robert Neugebauer

Daniel Costante Zilio

Linqi Liu

Mir Hamid Pirahesh

Paul C Huffman

Kathy A McKnight

Iqbal A Goralwalla

Guy Maring Lohman

Matthew Albert Huras

Ian R Finlay

Michael Jeffrey Winer

John Frederick Hornibrook

Qi Cheng

William T O'Connell

Shaorong Liu

Miroslaw Adam Flasza

Serge Philippe Rielau

Mokhtar Kandil

Chi Man J Sizto

Josep Aguilar Saborit

Jose A Saborit

A method implemented by at least one processing device, a processing device, and a computer program product are provided for adjusting hash partitions for a hash join operation. An amount of rows within each of an initial number of hash partitions is determined during assignment of respective rows to respective hash partitions. The initial number of hash partitions is adjusted to a final number of hash partitions based on the amount of rows within each of the initial number of hash partitions and an amount of available memory. The adjusting further includes determining the final number of hash partitions based on the amount of rows within each of the initial number of hash partitions and the amount of available memory, and assigning the rows to the final number of hash partitions. The hash join operation is then performed.

Switching between a non-partitioned hash join and a partitioned hash join based on an amount of available memory

A system for sorting in a column organized relational database by sorting only key columns in the column organized relational database to reduce memory and CPU usage. The sort may be performed using an in memory sort. After sorting the key columns, the system orders a payload column according to an order indicated by the key columns. The payload column is associated with the key columns in the column organized relational database. The system assesses memory capacity prior to ordering the payload column. Prior to ordering a plurality of payload columns, the system refrains from automatically concatenating the plurality of payload columns, and refrains from automatically de-concatenating any concatenated payload columns after the ordering the plurality of payload columns. The system determines an amount of required memory necessary to order the payload column, and detects an amount of available memory accessible to order the payload column.

Efficient handling of sort payload in a column organized relational database

A computer-implemented method includes receiving a query plan. The method includes identifying a plurality of qualified relevant rows and one or more encoding candidate payload columns. The method includes analyzing the relevant rows in the encoding candidate payload columns to yield a count of distinct contents and a payload column width. The method includes estimating a cost and determining whether the cost is larger than an amount of available memory for on-the-fly encoding all of the plurality of encoding candidate payload columns. The method is responsive to the estimated cost being less than the amount of available memory, by on-the-fly encoding the encoding candidates and responsive to the estimated cost being greater than the amount of available memory by on-the-fly encoding fewer than all of the encoding candidates so as not to exceed the available memory, and leaving alone one or more remaining encoding candidate payload columns unencoded.

Query plan optimization for large payload columns

Techniques are disclosed for accessing a dimensional data model when processing a query. In one embodiment, the query is processed by performing a two-stage zigzag join operation including a zigzag main join and a zigzag back join. The zigzag main join is configured to generate a set of row identifiers of fact records satisfying any local predicates in the query. The zigzag back join is configured to generate query results based on the set of row identifiers. The query results are returned responsive to the query.

Accessing a dimensional data model when processing a query

A query processing system has a query processor and a data manager. The query processor calls the data manager to carry out data access for a query including a filtering operation. The data manager accesses the data in a set of data and before returning the data, initiates a callback to the query processor to determine if the located data meets the filtering criteria. Where the data does not satisfy the filtering criteria, the data manager seeks additional data in the set of data, without having to return the first located data to the query processor.

Execution of database queries including filtering

Methods and apparatus, including computer program products, implementing and using techniques for executing a query in a computer to retrieve data from a relational database stored on a data storage device. A query is received that contains a CASE expression having one or more subqueries. A query execution plan is generated. The query execution plan uses a parameterized union all operator to keep the semantics of the CASE expression, and a nested loop join operator to drive data across the section of the query execution plan that executes the CASE expression without performing any join operations. The generated query execution plan is executed in the computer to retrieve data from the relational database.

Executing CASE expressions containing subqueries

There is disclosed a system and method for executing multiple distinct aggregate queries. In an embodiment, the method comprises: providing at least one Counting Bloom Filter for each distinct column of an input data stream; reviewing count values in the at least one Counting Bloom Filter for the existence of duplicates in each distinct column; and if necessary, using a distinct hash operator to remove duplicates from each distinct column of the input data stream, thereby removing the need for replicating the input data stream and minimizing distinct hash operator processing. Also, the use of Counting Bloom Filters for monitoring data streams allow an early duplicate removal of the input stream of data, resulting in savings in computation time and memory resources.

System and method for multiple distinct aggregate queries

Disclosed is a data processing system implemented method, a data processing system and an article of manufacture for executing a query having a union operator. A data processing system implemented method direct the data processing system to execute a query against a database having data objects. The query has sub-queries and having a union operator. The union operator is operable on sub-queries associated with the query. The database is operatively coupled to the data processing system. The data processing system implemented method including grouping the sub-queries of the union operator according to identified structural similarities, the identified structural similarities being based on an analysis of the sub-queries, grouping the data objects of the database according to the grouped sub-queries, replacing the grouped data objects and any sub-queries associated with the grouped data objects with a reference to a representative data object and a representative sub-query, and accessing at least one member of the grouped data objects, the accessing of the at least one member of the grouped data object being based on the reference.

Method, system and program for executing a query having a union operator

Disclosed is a data processing system, a data processing system implemented method and an article of manufacture for executing a query having a union all operator. The data processing system implemented method directs the data processing system to execute a query against a data object. The query has a union all operator and has a set of data modifying operators. The set of data modifying operators is associated with the union all operator. The union all operator references the data object. The data processing system implemented method includes preventing the union all operator from being applied to the data object, and applying the set of data modifying operators against the data object.

Method, system, and program for executing a query having a union all operator and data modifying operations

Disclosed is a data processing system implemented method, a data processing system and an article of manufacture for executing a query having a union operator. The data processing system implemented method directs the data processing system to process a query against data objects. The data objects are operatively coupled to the data processing system. The query includes a parent operator. The parent operator references a union operator. The union operator references sub-queries. The sub-queries reference the data objects. The data processing system implemented method includes noting a set of partitionings for the union operator, the noted set of partitionings being based on the sub-queries and being based on the data objects reference by the sub-queries, and executing the query having the union operator, the execution of the query being based on the noted set of partitionings and the parent operator.

Method, system and program for executing a query having a UNION operator

Growing community of inventors

St. Catharines, Canada

David C Sharpe