Apparatus and system for reducing locking in materialized query tables

• US 8,112,414 B2
• Filed: 08/28/2008
• Issued: 02/07/2012
• Est. Priority Date: 08/28/2008
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Bates, James P., Sloan, Jonathan, Zuzarte, Calisto P.
• Primary Examiner(s): Lie, Angela
• Assistant Examiner(s): LEE, MARK B
• Application Number:
  US12/200,674
• Publication Number:
  US 20100057670A1
• Time in Patent Office: 1,258 Days
• Field of Search: 707/717
• US Class Current: 707/717
• CPC Class Codes: G06F 16/2343 Locking methods, e.g. distributed locking or locking implementation details
• CPC Class Codes: G06F 16/2379 Updates performed during online database operations; commit processing

Abstract

Disclosed is an apparatus and system for reducing locking in materialized query tables (MQT) for distributive functions. The apparatus includes an insert module that inserts into an MQT table a child record when a new record is inserted into a base table associated with the MQT. The child record includes values associated with the insert operation. Also included is a delete module that inserts into the MQT a child record that includes measure values that are the negative of the measure values in the base table row that is the subject of the delete operation. An update module inserts two child rows into the MQT, one negating the affected record and the other adding the values of the update operation. Each inserted child row includes a unique identifier that relates the inserted row to a parent row. An execution module generates responses using the values indicated by the cumulative records in a family.

Claims

1. A computer program product stored on a computer-readable medium for reducing the locking of Materialized Query Tables (MQTs), the computer program product comprising:

a MQT definition query defining the aggregates within a MQT as distributive;

an insert module configured to perform an insert operation on the MQT that adds a child row to a MQT without refreshing the MQT in response to an insert operation inserting a base table row in a base table referenced by the MQT,

wherein the added child row comprises;

one or more foreign key column values;

a unique identifier relating the child row and an existing parent row; and

one or more measure values corresponding to one or more insert values of the insert operation in the base table; and

a delete module configured to perform an insert operation on the MQT that adds a child row to the MQT without refreshing the MQT in response to a delete operation deleting a base table row in the base table referenced by the MQT, wherein the added child row comprises;

one or more foreign key column values;

a unique identifier relating the child row and an existing parent row; and

one or more measure values having the negative of the measure values of the base table row that is the subject of the delete operation;

an update module configured to add two child rows to the MQT without refreshing the MQT in response to an update operation updating a base table row in the base table referenced by the MQT with one or more new values;

wherein the first added child row comprises;

one or more foreign key column values;

a unique identifier relating the child row and an existing parent row; and

one or more measure values having the negative of the measure values of the base table row that is the subject of the update operation; and

wherein the second added child row comprises;

one or more foreign key column values;

a unique identifier relating the second added child row and the first added child row; and

one or more measure values corresponding to one or more update values of the update operation in the base table;

an execution module configured to provide a value in response to a query requesting values associated with a particular foreign key by summing all measure values in the MQT sharing the particular foreign key, wherein the summation is executed in an order specified by the unique identifier; and

a consolidation module that executes an aggregation operation that aggregates all records in the MQT having the same foreign key into a single row entry without refreshing the MQT from the base table, wherein the consolidation module is further configured to determine;

that there are sufficient CPU cycles available to execute the aggregation operation without negatively impacting an associated computing system, and wherein the consolidation module initiates aggregation in response to determining that there are sufficient CPU cycles;

that a predetermined time period has passed since a last aggregation operation, and wherein the consolidation module initiates aggregation in response to determining that the predetermined time period has passed; and

that a cost of aggregating the MQT is less than a cost of executing a query referencing the MQT without aggregation, and wherein the consolidation module initiates aggregation in response to determining that the cost of aggregating the MQT is less than a cost of executing the query referencing the MQT without aggregation.

2. The computer program product of claim 1, wherein unique identifier relating the child row and an existing parent row is one of a sequential ID number and a time stamp.

3. A system for reducing the locking of Materialized Query Tables (MQTs), the system comprising:

one or more base tables;

one or more MQTs based on the one or more base tables;

a database client that submits one or more database queries;

a query optimizer that determines the overhead associated with executing a query against the one or more base tables and the overhead associated with updating one or more MQTs;

a MQT definition query defining the aggregates within one or more of the MQTs as distributive;

an insert module configured to perform an insert operation on the distributive MQT that adds a child row to the distributive MQT without refreshing the MQT in response to an insert operation inserting a base table row in a base table referenced by the distributive MQT, wherein the added child row comprises;

one or more foreign key column values;

a unique identifier relating the child row and an existing parent row; and

one or more measure values corresponding to one or more insert values of the insert operation in the base table; and

a delete module configured to perform an insert operation on the distributive MQT that adds a child row to the distributive MQT without refreshing the MQT in response to a delete operation deleting a base table row in the base table referenced by the distributive MQT, wherein the added child row comprises;

one or more foreign key column values;

a unique identifier relating the child row and an existing parent row; and

one or more measure values having the negative of the measure values of the base table row that is the subject of the delete operation; and

an update module configured to add two child rows to the distributive MQT without refreshing the MQT in response to an update operation updating a base table row in the base table referenced by the distributive MQT with one or more new values;

wherein the first added child row comprises;

one or more foreign key column values;

a unique identifier relating the child row and an existing parent row; and

one or more measure values having the negative of the measure values of the base table row that is the subject of the update operation; and

wherein the second added child row comprises;

one or more foreign key column values;

a unique identifier relating the second added child row and the first added child row; and

one or more measure values corresponding to one or more update values of the update operation in the base table; and

an execution module configured to provide a value in response to a query requesting values associated with a particular foreign key by summing all measure values in the MQT sharing the particular foreign key, wherein the summation is executed in an order specified by the unique identifier; and

a consolidation module that executes an aggregation operation that aggregates all records in the MQT having the same foreign key into a single row entry without refreshing the MQT from the base table, wherein the consolidation module is further configured to determine;

that there are sufficient CPU cycles available to execute the aggregation operation without negatively impacting an associated computing system, and wherein the consolidation module initiates aggregation in response to determining that there are sufficient CPU cycles;

that a predetermined time period has passed since a last aggregation operation, and wherein the consolidation module initiates aggregation in response to determining that the predetermined time period has passed; and

that a cost of aggregating the MQT is less than a cost of executing a query referencing the MQT without aggregation, and wherein the consolidation module initiates aggregation in response to determining that the cost of aggregating the MQT is less than a cost of executing the query referencing the MQT without aggregation.