Database query processing using horizontal data record alignment of multi-column range summaries

Patent metadata

Field	Value
Publication number	US-9792341-B2
Application number	US-201414293827-A
Country	US
Kind code	B2
Filing date	Jun 2, 2014
Priority date	Jun 2, 2014
Publication date	Oct 17, 2017
Grant date	Oct 17, 2017

Abstract

Organizing data within a database is provided. In response to determining that a group of coarsified data records within a database table is not an aligned group of data records, a virtually replicated subgroup of coarsified data records that corresponds to the group of coarsified data records is generated from different groups of coarsified data records within the database table. The virtually replicated subgroup of coarsified data records is aligned with the corresponding group of coarsified data records.

First claim

What is claimed is:

1. A computer-implemented method for decreasing processing time of database queries by a computer, the computer-implemented method comprising:
determining, by the computer, that a group of correlated data record columns within a database table is not horizontally aligned with a different group of correlated data record columns;
responsive to the computer determining that the group of correlated data record columns within the database table is not horizontally aligned with the different group of correlated data record columns, generating, by the computer, a virtually replicated row of data records that includes one or more values in the group of correlated data record columns from the different group of correlated data record columns within the database table; and
horizontally aligning, by the computer, the virtually replicated row of data records with the group of correlated data record columns by placing the virtually replicated row of data records with the group of correlated data record columns so that a database query having a predicate defined on correlated data record columns eliminates non-qualifying groups of correlated data record columns to decrease the processing time of the computer.

2. The computer-implemented method of claim 1 , further comprising:
receiving, by the computer, an input to perform a query on the database table; and
pruning, by the computer, non-qualifying groups of correlated data record columns from a query result using a set of one or more multi-column data record range summary tables for the query with a predicate defined on any correlated column in a set of correlated columns of data records within the database table by examining relevant groups of correlated data record columns and their corresponding virtually replicated data record rows in the set of one or more multi-column data record range summary tables.

3. The computer-implemented method of claim 2 , further comprising:
determining, by the computer, that the query is a data record range query; and
responsive to the computer determining that the query is a data record range query, searching, by the computer, all relevant groups of correlated data record columns and all their corresponding virtually replicated data record rows that fall at a boundary of a range of a query predicate for a result that satisfies the query predicate.

4. The computer-implemented method of claim 1 , further comprising:
determining, by the computer, that a new data record corresponds to a particular group of correlated data record columns in a set of different groups of correlated data record columns within the database table;
responsive to the computer determining that the new data record corresponds to the particular group of correlated data record columns in the set of different groups of correlated data record columns within the database table, inserting, by the computer, the new data record into a corresponding group of data record columns; and
inserting, by the computer, the new data record into a virtual set of replicated rows of data records to horizontally align different groups of correlated data record columns and their corresponding virtually replicated rows of data records having same data record values.

5. The computer-implemented method of claim 1 , further comprising:
receiving, by the computer, an input to delete a data record from the database table;
deleting, by the computer, the data record from a corresponding group of correlated data record columns in a set of different groups of correlated data record columns within the database table; and
deleting, by the computer, the data record from a virtual set of replicated rows of data records to maintain horizontal alignment of different groups of correlated data record columns and their corresponding virtually replicated rows of data records having same data record values.

6. The computer-implemented method of claim 1 , further comprising:
receiving, by the computer, an input to update a data record within the database table;
deleting, by the computer, the data record from a corresponding group of correlated data record columns in a set of different groups of correlated data record columns within the database table;
deleting, by the computer, the data record from a virtual set of replicated rows of data records;
inserting, by the computer, the updated data record into the corresponding group of correlated data record columns in the set of different groups of correlated data record columns within the database table; and
inserting, by the computer, the updated data record into the virtual set of replicated rows of data records to horizontally align different groups of correlated data record columns and their corresponding virtually replicated rows of data records having same data record values.

7. The computer-implemented method of claim 1 , further comprising:
determining, by the computer, a set of correlated columns of data records within the database table;
virtually coarsifying, by the computer, the data records in the set of correlated columns to reduce cardinality of data record values in the set of correlated columns;
virtually reorganizing, by the computer, coarsified data record rows in the set of correlated columns based on coarsified data record values in each correlated column;
determining, by the computer, data record groups comprising the coarsified data records based on the coarsified data record values in a subset of correlated columns within the set of correlated columns; and
generating, by the computer, a set of one or more multi-column data record range summary tables corresponding to the data record groups comprising the coarsified data records.

8. The computer-implemented method of claim 7 , further comprising:
analyzing, by the computer, a distribution of data record values within the database table to determine the set of correlated columns of data records.

9. The computer-implemented method of claim 7 , further comprising:
virtually ordering, by the computer, the set of correlated columns of data records into an ordered contiguous block of correlated columns at a beginning of the database table.

10. The computer-implemented method of claim 7 , further comprising:
virtually segmenting, by the computer, the coarsified data records within the set of correlated columns into different groups of coarsified data records based on the coarsified data record values in a first correlated column within the set of correlated columns.

11. A computer for decreasing processing time of database queries, the computer comprising:
a bus system;
a computer readable storage device connected to the bus system, wherein the computer readable storage device stores computer readable program code; and
a hardware processor connected to the bus system, wherein the hardware processor executes the computer readable program code to determine that a group of correlated data record columns within a database table is not horizontally aligned with a different group of correlated data record columns; generate a virtually replicated row of data records that corresponds to includes one or more values in the group of correlated data record columns from the different group of correlated data record columns within the database table in response to determining that the group of correlated data record columns within the database table is not horizontally aligned with the different group of correlated data record columns; and horizontally align the virtually replicated row of data records with the group of correlated data record columns by placing the virtually replicated row of data records with the group of correlated data record colum

Assignees

• IBM

Inventors

• Chang Yuan-Chi
• Hamedani Mohammad S
• Hassanzadeh Oktie
• Malkemus Timothy R

Classifications

• G06F16/24556

  Aggregation; Duplicate elimination · CPC title

• G06F16/22

  Indexing; Data structures therefor; Storage structures · CPC title

• G06F16/27 Primary

  Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor · CPC title

• G06F16/221 Primary

  Column-oriented storage; Management thereof · CPC title

• G06F17/30489

  Physics · mapped topic