Common spool files for maintaining join indexes

Details Common spool files for maintaining join indexes Common spool files for maintaining join indexes

2000-06-15

2003-09-09

Popovici, Dov (Department: 2175)

Data processing: database and file management or data structures

Database design

Data structure types

C707S793000, C707S793000, C709S241000

Reexamination Certificate

active

06618720

ABSTRACT:

BACKGROUND OF THE INVENTION
1. Field of the Invention
This invention relates in general to computerized relational databases, and in particular, to the use of common spool files for the maintenance of join indexes.
2. Description of Related Art
Relational DataBase Management Systems (RDBMS) using a Structured Query Language (SQL) interface are well known in the art. The SQL interface has evolved into a standard language for RDBMS software and has been adopted as such by both the American National Standards Institute (ANSI) and the International Standards Organization (ISO).
In an RDBMS, all data is externally structured into tables. A table in a relational database is two dimensional, consisting of rows and columns. Each column has a name, typically describing the type of data held in that column. As new data is added, more rows are inserted into the table. A user query selects some rows of the table by specifying clauses that qualify the rows to be retrieved based on the values in one or more of the columns.
The SQL interface allows users to formulate relational operations on the tables either interactively, in batch files, or embedded in host languages such as C, COBOL, etc. Operators are provided in SQL that allow the user to manipulate the data, wherein each operator performs functions on one or more tables and produces a new table as a result. The power of SQL lies in its ability to link information from multiple tables or views together to perform complex sets of procedures with a single statement.
The SQL interface allows users to formulate relational operations on the tables. One of the most common SQL queries executed by the RDBMS is the SELECT statement. In the SQL standard, the SELECT statement generally comprises the format: “SELECT<clause>FROM<clause>WHERE<clause>GROUP BY<clause>HAVING<clause>ORDER BY<clause>.” The clauses generally must follow this sequence, but only the SELECT and FROM clauses are required.
Generally, the result of a SELECT statement is a subset of data retrieved by the RDBMS from one or more existing tables stored in the relational database, wherein the FROM clause identifies the name of the table or tables from which data is being selected. The subset of data is treated as a new table, termed the result table.
A join operation is usually implied by naming more than one table in the FROM clause of a SELECT statement. A join operation makes it possible to combine tables by combining rows from one table with another table. The rows, or portions of rows, from the different tables are concatenated horizontally. Although not required, join operations normally include a WHERE clause that identifies the columns through which the rows can be combined. The WHERE clause may also include a predicate comprising one or more conditional operators that are used to select the rows to be joined.
Join indexes have been used to improve the performance of join operations in an RDBMS. A description of join indexes, including a novel join index, can be found in co-pending and commonly-assigned application Ser. No. 09/073,113, filed on May 5, 1998, by Chi Kim Hoang, entitled “A JOIN INDEX FOR RELATIONAL DATABASES”, which application is incorporated by reference herein.
Join indexes are automatically maintained by the RDBMS when updates are performed on the underlying tables. Usually, additional steps are included in the execution plans for these updates to regenerate the affected portions of the join indexes. In many cases, the queries that define the join indexes have to be re-processed in order to update the join indexes.
When there are a large number of join indexes defined on a table, the overhead of maintaining the join indexes can be quite high. Thus, there is a need in the art for techniques that minimize the overhead involved in performing maintenance on join indexes.
SUMMARY OF THE INVENTION
To overcome the limitations in the prior art described above, and to overcome other limitations that will become apparent upon reading and understanding the present specification, the present invention discloses a method, apparatus, article of manufacture, and data structure related to the maintenance of a plurality of join indexes. The join indexes are defined for a table stored in the database. As the table is updated, queries that define the join indexes are re-processed to obtain the records for updating the join indexes, wherein one or more common joins are identified among the join indexes, each common join is materialized only once in a common spool file, and the common spool file is used to materialize one or more rows for updating the join indexes.


REFERENCES:
patent: 5241648 (1993-08-01), Cheng et al.
patent: 5666525 (1997-09-01), Ross
patent: 5758146 (1998-05-01), Schiefer et al.
patent: 5870747 (1999-02-01), Sundaresan
patent: 5983215 (1999-11-01), Ross et al.
patent: 5987453 (1999-11-01), Krishna et al.
patent: 6374236 (2002-04-01), Chen et al.
A. Witkowski et al. “NCR 3700—The Next-Generation Industrial Database Computer,” Proceeding of the 19thVLDB Conference, Dublin, Ireland, 1993, 14 pgs.

Affiliated with

Also associated with

Rating

Common spool files for maintaining join indexes does not yet have a rating. At this time, there are no reviews or comments for this patent.

If you have personal experience with Common spool files for maintaining join indexes, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Common spool files for maintaining join indexes will most certainly appreciate the feedback.

Rate now

Comments { 0 }

Profile ID: LFUS-PAI-O-3047074