# US 2013/0159284 A1 · Autonomic refresh of a materialized query table in a computer database

> Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt

- Number: US 2013/0159284 A1
- Document ID: US20130159284A1
- Status: Published application
- Kind: A1
- Application number: 13/770,189
- Filed: 2013-02-19
- Published: 2013-06-20
- Earliest claimed: 2005-08-04
- Assignees: International Business Machines Corporation
- Inventors: John M. Santosuosso
- Web page: https://patentgrep.com/patent/US20130159284A1

## CPC

- G06F 16/24539: {using cached or materialised query results}
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)

## Abstract

A method autonomically refreshes a materialized query table (MQT) in a computer database to improve database performance and utility. In preferred embodiments, the query optimizer autonomically initiates a refresh of MQT depending on an estimated time for the query to access the base tables. In other preferred embodiments, the query optimizer estimates the time for the query to access the base tables and compares it to the estimated time to refresh the MQT to determine whether to refresh the MQT and run the query over the MQT rather than the base tables.

## Claims

### Claim 1

An apparatus comprising:
at least one processor;
a memory coupled to the at least one processor;
a database residing in the memory having data;
a query optimizer residing in the memory that parses a query to identify a database table with a materialized query table (MQT) in deferred maintenance; and
wherein the query optimizer autonomically determines whether to update the MQT in deferred maintenance by estimating a time to execute the query over a base table corresponding to the MQT in deferred maintenance, estimating a time to update the MQT in deferred maintenance, comparing the estimated time to update the MQT in deferred maintenance and the estimated time to execute the query over the base tables; and
wherein the query optimizer updates the MQT in deferred maintenance where the estimated time to update the MQT is less than the time to execute the query; and executes the query over the updated MQT in deferred maintenance.

### Claim 2 (depends on 1)

The apparatus of claim 1 wherein the query optimizer considers a time of trigger events to estimate the time to refresh the MQT.

### Claim 3 (depends on 1)

The apparatus of claim 1 wherein the query optimizer considers results stored in a staging table to estimate the time to refresh the MQT.

### Claim 4

A program product comprising:
a query optimizer that parses a query to identify a database table with a materialized query table (MQT) in deferred maintenance, wherein the query optimizer autonomically determines whether to update the MQT in deferred maintenance by estimating a time to execute the query over a base table corresponding to the MQT in deferred maintenance, estimating a time to update the MQT in deferred maintenance, comparing the estimated time to update the MQT in deferred maintenance and the estimated time to execute the query over the base tables, wherein the query optimizer updates the MQT in deferred maintenance where the estimated time to update the MQT is less than the time to execute the query; and executes the query over the updated MQT in deferred maintenance; and
non-transitory computer-recordable media bearing the query optimizer.

### Claim 5 (depends on 4)

The program product of claim 4 wherein the query optimizer considers a time of trigger events to estimate the time to refresh the MQT.

### Claim 6 (depends on 4)

The program product of claim 4 wherein the query optimizer considers results stored in a staging table to estimate the time to refresh the MQT.