Autonomic refresh of a materialized query table in a computer database
View Patent ↗An apparatus and 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.
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 optimizes queries that access the database, and wherein the query optimizer autonomically determines when to refresh at least one materialized query table (MQT); and
wherein the query optimizer autonomically determines when to refresh the at least one MQT depending on an estimated time for a query to access data in at least one database table in the database.
2. The apparatus of claim 1 wherein the query optimizer autonomically determines when to refresh the at least one MQT by comparing the estimated time for the query to access data in the at least one database table to a time to refresh the at least one MQT.
3. The apparatus of claim 2 wherein the query optimizer considers a time of trigger events to estimate the time to refresh the at least one MQT.
4. The apparatus of claim 2 wherein the query optimizer considers results stored in a staging table to estimate the time to refresh the at least one MQT.
5. The apparatus of claim 1 wherein the query optimizer autonomically updates the at least one MQT that is in deferred maintenance.
6. A program product comprising:
(A) a query optimizer that optimizes queries that access a database and autonomically determines when to refresh at least one materialized query table (MQT) depending on an estimated time for a query to access data in at least one database table in the database; and
(B) a non-transitory computer-recordable media bearing the query optimizer.
7. The program product of claim 6 wherein the query optimizer autonomically determines when to refresh the at least one MQT by comparing the estimated time for the query to access data in the at least one database table to a time to refresh the at least one MQT.
8. The program product of claim 6 wherein the query optimizer autonomically updates the at least one MQT that is in deferred maintenance.
9. The program product of claim 6 wherein the query optimizer considers a time of trigger events to estimate the time to refresh the at least one MQT.
10. The program product of claim 6 wherein the query optimizer considers results stored in a staging table to estimate the time to refresh the at least one MQT.