Materialized query tables with shared data

Patent metadata

Field	Value
Publication number	US-9928277-B2
Application number	US-201414536778-A
Country	US
Kind code	B2
Filing date	Nov 10, 2014
Priority date	Nov 10, 2014
Publication date	Mar 27, 2018
Grant date	Mar 27, 2018

Abstract

A computer system can generate a materialized query table (MQT) for data from a base table that has a plurality of rows and columns that each contain stored data. The computer system can receive a query to data stored in a row from the base table. The system can identify a column in the row and determine that data from a cell in the row and the column is above a threshold memory size. The system can generate, in response to determining that the data being above the threshold memory size, an MQT that includes data for the row and that includes a reference pointer that points to a location shared with another table.

First claim

What is claimed is:

1. A database manager system for creating a materialized query table (MQT) for data from a base table having a plurality of rows and columns that contain stored data, the system comprising:
one or more computer processor circuits configured to:
receive a first query to retrieve data stored in a row from the base table;
identify a first cell and a second cell in the base table, the first cell corresponding to a first column in the row, the second cell corresponding to a second column in the row;
determine that data in the first cell is above a threshold memory size, wherein the data from the first cell is a large object (LOB) and the threshold memory size is set by a database manager;
determine based on a share parameter of the base table, that the base table is permitted to share the data for the row with a first materialized query table (MQT), wherein the share parameter of the base table is automatically configured based upon a set of configuration rules;
generate, in response to determining that the LOB is above the threshold memory size, the first MQT, wherein the first MQT contains the data for the second cell and has a first reference pointer in place of the data of the first cell, the first reference pointer indicating a location of the LOB in the base table;
identify a first refresh schedule, the first refresh schedule being associated with the base table;
identify a second refresh schedule, the second refresh schedule being associated with the first MQT;
synchronize the first refresh schedule and the second refresh schedule to a common refresh rate, the common refresh rate maintaining consistency with changes in the base table;
determine, based on a share parameter of the first MQT, that the first MQT is permitted to share the data with a second MQT, wherein the share parameter of the first MQT is automatically configured based upon a second set of configuration rules; and
generate the second MQT, wherein the second MQT contains data for a third cell and has a second reference pointer in place of the data of the first cell, the second reference pointer indicating a location of the first reference pointer in the first MQT.

2. The system of claim 1 , wherein the one or more computer processor circuits are further configured to:
store the first MQT in a location that is separate from the base table; and
generate an index that includes a storage space pointer, the storage space pointer being configured to identify the location of the first MQT.

3. The system of claim 2 , wherein the one or more computer processor circuits are further configured to:
receive a second query to retrieve data stored in the first cell;
access the index that stores the storage space pointer to the first MQT;
rewrite, by the database manager, the second query to direct the second query to the first MQT;
identify the first reference pointer in the first MQT, the first reference pointer identifying the location of the LOB in the base table; and
retrieve, from the base table, the data for the LOB.

4. The system of claim 2 , wherein the one or more computer processor circuits are further configured to:
receive a third query to retrieve data stored in the row in the base table;
identify the first cell and the third cell in the base table, the first cell corresponding to the first column in the row, and the third cell corresponding to a third column in the row; and
determine that data from the first cell is above the threshold memory size.

5. The system of claim 4 , wherein the one or more computer processor circuits are further configured to:
identify the first reference pointer in the first MQT, the first reference pointer identifying the location of the first cell; and
retrieve from the base table, the data for the LOB.

6. The system of claim 1 , wherein the one or more computer processor circuits are further configured to:
determine that data in the first cell is below a threshold memory size; and
copy the data in the first cell directly into the first MQT.

7. A computer program product for generating a materialized query table (MQT) for data from a base table having a plurality of rows and columns that contain stored data, the computer program product comprising a computer readable storage medium having program instructions embodied therewith, wherein the computer readable storage medium is not a transitory signal per se, the program instructions executable by a processing circuit to perform a method comprising:
receiving a first query to retrieve data stored in a row from the base table;
identifying a first cell and a second cell in the base table, the first cell corresponding to a first column in the row, the second cell corresponding to a second column in the row;
determining that data in the first cell is above a threshold memory size, wherein the data from the first cell is a large object (LOB) and the threshold memory size is set by a database manager;
determining based on a share parameter of the base table, that the base table is permitted to share the data for the row with a first materialized query table (MQT), wherein the share parameter of the base table is automatically configured based upon a set of configuration rules;
generating, in response to determining that the LOB is above the threshold memory size, the first MQT, wherein the first MQT contains the data for the second cell and has a first reference pointer in place of the data of the first cell, the first reference pointer indicating a location of the LOB in the base table;
identifying a first refresh schedule, the first refresh schedule being associated with the base table;
identifying a second refresh schedule, the second refresh schedule being associated with the first MQT;
synchronizing the first refresh schedule and the second refresh schedule to a common refresh rate, the common refresh rate maintaining consistency with changes in the base table;
determining, based on a share parameter of the first MQT, that the first MQT is permitted to share the data with a second MQT, wherein the share parameter of the first MQT is automatically configured based upon a second set of configuration rules; and
generating the second MQT, wherein the second MQT contains data for a third cell and has a second reference pointer in place of the data of the first cell, the second reference pointer indicating a location of the first reference pointer in the first MQT.

8. The computer program product of claim 7 , wherein the method further comprises:
storing the first MQT in a location that is separate from the base table; and
generating an index that includes a storage space pointer, the storage space pointer being configured to identify the location of the first MQT.

9. The computer program product of claim 8 , wherein the method further comprises:
receiving a second query to retrieve data stored in the first cell;
accessing the index that stores the storage space pointer to the first MQT;
rewriting, by the database manager, the second query to direct the second query to the first MQT;
identifying the first reference pointer in the first MQT, the first reference pointer identifying the location of the LOB in the base table; and
retrieving, from the base table, the data for the LOB.

10. The computer program product of claim 8 , wherein the method further comprises:
receiving a third query to retrieve data stored in the two in the base table;
identifying the first cell and the third cell in the base table, the first cell corresponding to the first column in the row, and the third cell corresponding to a third column in the row
determining that data from the first cell is above the threshold memory size.

Assignees

• IBM

Inventors

• Konik Rafal P
• Mittelstadt Roger A
• Muras Brian R
• Theuer Mark W

Classifications

• G06F17/30442

  Physics · mapped topic

• G06F17/30504

  Physics · mapped topic

• G06F17/30339

  Physics · mapped topic

• G06F17/30371

  Physics · mapped topic

• G06F17/30457 Primary

  Physics · mapped topic