Ncr Corporation

Teradata Us, Inc.

Paul Laurence Sinclair

Ahmad Said Ghazal

Douglas Paul Brown

Donald Raymond Pederson

Grace Kwan-On Au

Kuorong Chiang

Steven Bart Cohen

Sanjay Nair

Frederick Stuart Kaufmann

Louis Martin Burger

Rama Krishna Korlapati

Mohammed Yassin Al-Kateb

Jeetendra Chaware

Mark Sirek

Philip Jason Benton

Thomas Patrick Julien

Grace K Au

Sung Jin Kim

Rui Zhang

Chi Kim Hoang

Sanjib Kumar Mishra

Mohamed Yassin Eltabakh

Tri Minh Tran

Ganesan Gopal

Yi Xia

Vijay Sarathy

Jerry Lynn Klindt

Srikanth Reddy Muthyala

Richard Leon Kimball, Iii

Chengyang Zhang

Rajendran Kolandasamy

Samrat Pandiri

David O Craig

Xiaorong Luo

Albert Maghbouleb

Mamatha Govind Rao

Lawrence H Higa

Bin Cao

Techniques for improving complex database queries are provided. A determination is made whether to adopt a static or dynamic query execution plan for a received database query based on metrics. When a dynamic query execution plan is adopted, the database query is separated into query fragments. A plan fragment is generated for each query fragment and executed to generate feedback for the plan fragment. The feedback from the execution of each plan fragment is used to initiate query rewrite rules to simplify the corresponding query fragments. The rewritten query fragments are combined to generate the dynamic query plan.

Incremental simplification and optimization of complex queries using dynamic result feedback

A system and method for loading data into column-partitioned database tables. The system and method incorporate a mechanism for buffering data extracted from the rows of a source table in column-oriented fashion within an in-buffer memory, enabling an efficient bulk-write of large arrays of values from the buffer into column-partitioned database tables. The system and method may also include optimizations for grouping columns according to data types and altering the order in which columns are inserted into the database tables.

Buffered data-loading in column-partitioned database tables

A first query execution plan generated for a query on a second time the query was processed by a database is compared against a dynamically generated second query plan generated based on statistics only dynamic feedback for the second time the query is processed by the database. A determination is made on the second time as to whether to cache the first query execution plan, the second query execution plan, or no plan for third or more times the query is processed by the database. The query can be non-parameterized or parameterized.

Dynamic generated query plan caching

One or a soft correlation of a database can be adjusted (e.g., modified, replaced, overwritten) for use with respect to one or more record(s) of the database associated with the soft correlation, by considering at least one or more violations of the soft correlations in the one or more of records database records associated with the soft correlation. In addition, an adjusted soft correlation can be stored and used for optimizations of database queries pertaining to one or more records associated with the adjusted soft correlation. Typically, the adjusted soft correlation is adjusted by at least considering the violations of an original soft correlation in the one or more records relating to the database queries.

Management of soft correlation for databases and optimization of database queries

Data portions of a database can be grouped and ranked in order of priory for reassignment from one or more maps to another one or more maps. It should be noted that a first map can assign the data portions to a first configuration of processors for processing the data portions, and a second map assigns the data portions to a second configuration of processors, different than the first configuration, for processing the data portions in a database system and/or environment. The data portions are reassigned in groups during an available time (window) for reassignment by taking the first one of the groups can be reassigned ('moved') in the available, then the second one in the available reaming time, and so on, until no group of data portions can be moved in the remaining time or all of them have been moved.

Timing of reassignment of data to different configurations of processing units in database systems

A database operation is performed in a file system residing on a plurality of processing modules. The file system includes a first relation having a plurality of first-relation entries. Each of the plurality of first-relation entries has a first-relation attribute that is of interest in the database operation. A value of a distribution attribute in each of the first-relation entries is set to a unique value selected from among a domain of unique values. The first-relation entries of the first relation are redistributed among the plurality of processing modules based on the first-relation attribute and the distribution attribute. The computational operation is performed to produce a result.

Reducing skew for database operation processing with randomization

The number of processors that store data can be limited for some data in order to improve efficiency in database system that uses multiple processing units to process data. For example, a map can be provided for one or more sparse database tables (or tables) that are relatively small in view of the number of processors that are available. The map can effectively assign distinct portions of the data, e.g., rows of a database, for processing only to a limited number of processors (or processing units). The database system can use the map to process data accordingly.

Management of sparse data for database systems with multiple processing units

An order (or priority) for reassigning data portions of a database from a first map to a second map can be determined. It should be noted that the first map assigns the data portions to a first configuration of processors for processing the data portions, and the second map assigns the data portions to a second configuration of processors, different than the first configuration, for processing the data portions in a database system and/or environment. The order can be determined by grouping the data in multiple groups and ordering the groups in accordance with a determined priority for reassignment. In doing so, one or more relationships between the data portions and/or cost of use for each one the groups can be considered.

Reassignment of data to different configurations of processing units in database systems

Techniques for improving complex database queries are provided. A determination is made whether to adopt a static or dynamic query execution plan based on metrics. When the dynamic query execution plan is used, a request fragment of the request is planned and the corresponding plan fragment is executed. The processed fragment provides feedback related to its processing to the remaining request and the process is repeated on the remaining request until the request is completed.

Techniques for improving the performance of complex queries

A database system may include a storage device to store a plurality of database tables. At least a portion of the database tables may be column-partitioned. The database system may also include a processor in communication with the storage device and a row-column subsystem executable by the processor to receive a request to locate a row of a column-partitioned database table. The row in the request may be used to provide a response to a query. The row-column subsystem may be further executable to determine if referenced column values of the requested row are stored in a cache associated with the row-column subsystem. The row-column subsystem may be further executable to retrieve the referenced column values of the row in the request from the cache in response to the determination that the column values are in the cache. The row-column subsystem may be further executable to provide the referenced column values for evaluation with respect to query conditions of the query. A method and computer-readable medium may also implement the row-column subsystem.

Processing column-partitioned data for row-based operations in a database system

Techniques for finding a column with column partitioning are provided. Metadata for a container row is expanded to include information for searching ranges of partitioned column values. The metadata identifies offsets to specific ranges and specific columns within a specific range. The offsets also identify where compressed data for a desired column resides. Thereby, permitting partitioned columns having compressed data to be located without being decompressed and decompressed on demand as needed.

Techniques for finding a column with column partitioning

A method of searching a multilevel partitioned database includes receiving a query data from the multilevel partitioned database. At least for one level partitions are dynamically included. For some levels, partitions may also be statically included for execution of the query. The query is the executed over the partitions that are both dynamically and statically included. In one example, the cost of joining two tables in a multilevel partitioned database includes determining level partitions that can be statically included, estimating level partitions that will be dynamically considered for the join, and determining a cost as a function of the estimated statically included level partitions and estimated dynamically included level partitions.

Product join dynamic partition elimination for multilevel partitioning

A method, computer program, and database system for performing a request on a table associated with an index are disclosed. A database contains a table, an index, and a partition elimination list and accepts a query. A table has one or more rows stored in one or more partitions. The index contains index entries, each of which references a row in a partition, where the row is in the table. The query has a first and a second condition. The partition elimination list is populated with partitions based on the first condition of the query. Only index entries that are identified by the request because of the second condition and that reference partitions that are not eliminated by the partition elimination list are read.

Partition elimination on indexed row IDs

In a relational database system, data is logically represented as a single table, but physically stored as a plurality of overlapping partitions. In some embodiments, the table is created by an instruction defining the table's structure and specifying a plurality of partitions in which to store duplicate copies of some data and in some embodiments, the table is accessed without referencing the overlapping partitions.

Relational database system having overlapping partitions

A database management system receives a request to perform an operation on a set of target data and, at some point after it begins executing that operation, places a lock on the target data to prevent concurrent execution of other operations on the target data. In some embodiments, the system receives an instruction from a user to perform a data-definition operation on a set of target data; places an initial lock on the target data at a level that allows at least one concurrent operation on the target data; begins executing the operation; and then places a final lock on the target data at a level that excludes all other concurrent operations on the target data.

Growing community of inventors

Manhatten Beach, CA, United States of America

Paul Laurence Sinclair