Materialized query tables with shared data

Patent metadata

Field	Value
Publication number	US-9589016-B2
Application number	US-201615098567-A
Country	US
Kind code	B2
Filing date	Apr 14, 2016
Priority date	Nov 10, 2014
Publication date	Mar 7, 2017
Grant date	Mar 7, 2017

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 computer-implemented method performed by a processor, comprising:
receiving a first query to retrieve data stored in a row in a 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, the first MQT containing the data for the second cell and having 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;
storing the first MQT in a location that is separate from the base table;
generating an index that includes a storage space pointer, the storage space pointer being configured to identify the location of the first MQT;
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;
receiving a second query to retrieve the 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;
retrieving, from the base table, the data for the LOB;
receiving a third query to retrieve data stored in the row in the base table;
identifying the first cell and a third cell in the base table, the first cell corresponding to the first column in the row, the third cell corresponding to a third column in the row;
determining that data from the first cell is above the threshold memory size;
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 set of configuration rules;
generating the second MQT, the second MQT containing the data for the third cell and having 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;
identifying the first reference pointer in the first MQT, the first reference pointer identifying the location of the first cell; and
retrieving, from the base table, the data for the LOB.

Assignees

• IBM

Inventors

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

Classifications

• G06F17/30442

  Physics · mapped topic

• G06F17/30457 Primary

  Physics · mapped topic

• G06F17/30339

  Physics · mapped topic

• G06F17/30575

  Physics · mapped topic

• G06F17/30383

  Physics · mapped topic