Browse Prior Art Database

A Method for Recognizing The Passage of Time With Respect to Stored Time Sensitive SQL Statements Disclosure Number: IPCOM000014396D
Original Publication Date: 2000-May-01
Included in the Prior Art Database: 2003-Jun-19

Publishing Venue



Disclosed is a method for recognizing and accommodating the passage of time when dealing with time sensitive versions of cached SQL statements. This method covers both static SQL stored in system catalogs as well as dynamic SQL statements that are cached in memory and shared between multiple applications. A recent innovation in relational databases has been the introduction of time-sensitive copies, or "shadows", of real tables that contain data taken from the real table at some point in time and made available as a substitute to the real table for queries accessing this data. Use of these shadow tables has allowed for dramatic performance increases when evaluating common expressions and aggregations by calculating these things ahead of time. Automatic Summary Tables (ASTs) are an example of this type of shadow table. The rate at which the data in these shadow tables is refreshed and re-evaluated from the real table varies and is defined at the time of the shadow table creation. The data in a shadow table is only valid, relative to the real table, as of the time of its last refresh. When a user specifies an SQL statement, they can also specify an acceptable time range for the data to be used in answering their query. This time range, or delta, is used when deciding if any of the existing shadow tables can be used to resolve the query and it indicates the amount of time that the user will allow to exist between the time of the request and the time of the last refresh to the data in the shadow table. Basically, the acceptable time delta dictates the "freshness" of the data that the user will allow to be used to resolve a query. Problems begin to arise when SQL statements that reference shadow tables are stored either as static SQL in the system catalog tables or as cached dynamic SQL. The main problem is that the enforcement of the user's acceptable time delta also has to account for the length of time that the statement has been stored. With every reuse of a stored SQL statement, the "freshness" of the data sources must be re-evaluated relevant to the current request time and the current setting of the acceptable time delta to be used for the query. The difficulty arises in how to determine the current freshness of the data without recompiling the statement which defeats the original rationale for storing the SQL statement which was to avoid compilation where possible. For reuse, we need to be able to determine if the stored statement is still suitable given