Browse Prior Art Database

Apparatus, System and Method for query-optimized snapshot views

IP.com Disclosure Number: IPCOM000177502D
Original Publication Date: 2008-Dec-16
Included in the Prior Art Database: 2008-Dec-16
Document File: 8 page(s) / 55K

Publishing Venue

IBM

Abstract

Query-optimized snapshot views provide improved query performance for repeated access to complex view definitions for scenarios where changes of the underlying base entities are not of interest for the repeated access to the complex views.

This text was extracted from a PDF file.
At least one non-text object (such as an image or picture) has been suppressed.
This is the abbreviated version, containing approximately 22% of the total text.

Page 1 of 8

Apparatus, System and Method for query-optimized snapshot views

Background:

If an application needs to do repeated retrieval of data form a database system, which involves usage of one or more views (which are based on a complex view definition), where changes made to the underlying tables/views are not of interest for a number of subsequent queries, this repeated retrieval of the same data produces unnecessary high processing costs and execution time, because the complex view has to be re-evaluated for each subsequent query. The phrase "not of interest" can mean two things here: The application does not care, if there was a change in the underlying data, or the application needs to be shielded from any changes during the duration of the repeated queries. The solution presented below should address this scenario.

The known solutions to the scenario described above include:

Using standard database views - this is the most common solution

1.

Using a materialized view/materialized query table

2.

Using a materialized view/materialized query table with deferred update (if

3.

feature is available in the used database system)

Summary:

The proposed solution to address the issue mentioned above introduces the usage of temporary tables, which are created adjusted to the need of the queries using these temporary tables. The term "adjusted" in this case means to have the temporary table containing only the subset of records and columns, that is needed for a set of related queries (logically combined as so-called query sequence) along with setting up additional elements of the database system to support efficient query execution (for example by creating additional indexes).

The known solutions cited above have drawbacks / shortcomings in one or more of the following areas:
bad query performance along with high execution costs
no change encapsulation across UOW boundaries
bad insert/update performance
increased storage requirements (space, I/O) for large data sets

The advantages of the proposed solution compared to the known solutions are

Improved query performance / reduced execution costs without having bad performance/throughput at insert/update time.

Reduced storage requirements in case of large underlying data sets by avoiding/reducing redundant data storage.

Change encapsulation across query / UOW boundaries even at concurrent access
Standard view is still available for non-sequence queries.

The proposed solution is described in more detail in the remainder of the document.

Description:

Some applications need to do a number of related SQL queries, which use or include data from a view based on a complex view definition (or one or more tables using a

1

Page 2 of 8

complex where clause) within one application operation of higher abstraction. Other application operations might need to query the data from the view only once duri...