Data processing: software development – installation – and managem – Software program development tool – Translation of code
Reexamination Certificate
1999-12-16
2003-06-17
Khatri, Anil (Department: 2122)
Data processing: software development, installation, and managem
Software program development tool
Translation of code
C717S140000, C707S793000, C706S045000
Reexamination Certificate
active
06581205
ABSTRACT:
BACKGROUND OF THE INVENTION
1. Field of the Invention.
This invention relates in general to database management systems performed by computers, and in particular, to the intelligent compilation of materialized view maintenance for query processing in database management systems.
2. Description of Related Art.
Computer systems incorporating Relational DataBase Management System (RDBMS) software 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 Nationals Standard Institute (ANSI) and the International Standards Organization (ISO).
For most RDBMS software, combinations of tables and views are used to access data stored in tables in the database. A materialized view or summary table is often based on a “full select” against one or more underlying base tables, the results of which are then materialized in the view or table. The columns of the view or table are based on the elements of the select list of the full select.
Generally, the query used to generate the summary table or materialized view condenses, combines, or otherwise processes the data residing in one or more underlying base tables. When the underlying base tables are modified, the materialized view or summary table must also be maintained to accurately reflect the modified tables. In general, modification operations made to the same table are synchronized by the DBMS in order that all transactions retrieve the correct results from the materialized view or summary table. However, in the case of summary tables, such synchronization may lead to frequent deadlocks between transactions, since the summary table usually condenses large amounts of data into relatively few rows. In these cases, most modifications to the underlying base tables will need to modify the same set of rows in the summary table.
To alleviate such potential deadlocks, the isolation level or lock modes can be adjusted in such situations where the summary table is being accessed for the purposes of propagating an update. This is often difficult to achieve in the prior art, because it relies upon the skill and knowledge of the person(s) developing the transactions, to ensure certain parts of a transaction run with the minimally correct level of isolation or obtains the minimal required locks in the transactions, rather than upon any automatic operations of the RDBMS software. Furthermore, the RDBMS software typically overrides any such lock modes or isolation levels requested by the user when modifications are involved. Thus, there is a need in the art for automated mechanisms within the RDBMS software that modify transactions to ensure the correct level of isolation.
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, and article of manufacture for maintaining a materialized view derived from at least one base table in a database stored on a computer. An update is performed to the base table in a transaction. Thereafter, the materialized view is accessed to identify affected records therein using at least one mechanism, such as an isolation level or update- and exclusive-locks, to guarantee consistency while avoiding deadlocks with other transactions that modify at least one base table of the materialized view and to improve concurrency with other transactions that are accessing the materialized view when the modification to the base table is applied to the materialized view.
It is an object of the present invention to optimize queries using materialized views that can be replicated and/or partitioned across multiple processors. More specifically, it is an object of the present invention to the maintenance of materialized views or summary tables in an RDBMS.
REFERENCES:
patent: 5261069 (1993-11-01), Wilkinson et al.
patent: 5367675 (1994-11-01), Cheng et al.
patent: 5388259 (1995-02-01), Fleischman et al.
patent: 5440732 (1995-08-01), Lomet et al.
patent: 5481700 (1996-01-01), Thuraisingham
patent: 5544355 (1996-08-01), Chaudhuri et al.
patent: 5546570 (1996-08-01), McPherson, Jr. et al.
patent: 5963934 (1999-10-01), Cochrane et al.
patent: 5987455 (1999-11-01), Cochrane et al.
patent: 6081801 (2000-06-01), Cochrane et al.
patent: 6339769 (2002-01-01), Cochrane et al.
patent: 6341288 (2002-01-01), Yach et al.
patent: 6353828 (2002-03-01), Ganesh et al.
patent: 6405368 (2002-06-01), Freyburger
patent: 6411951 (2002-06-01), Galindo-Legaria et al.
patent: 6411964 (2002-06-01), Iyer et al.
patent: 6460027 (2002-10-01), Cochrane et al.
Elkan, “Independence of logic database quries and updates”, ACM pp 154-160, 1990.*
Hanson, “A performance analysis of view materialization strategies”, ACM pp 440-453, 1987.*
Gal, Obsolescent materialized views in query processing of enterprise information systems:, ACM CIKM, pp 367-374, 1999.*
Ross et al, Materialized view maintenance and integrity constrainte checking: trading space for time, ACM SIGMOD, pp 447-458, 1996.*
Lu et al. Efficient maintenance of materialized mediated views, ACM SIGMOD, pp 340-351, 1995.*
Hamid Pirahesh et al., “Extensible/Rule Based Query Rewrite Optimization in Starburst”, Proceedings of ACM SIGMOD '92 International Conference on Management of Data, San Diego, CA, [Sigmod record: vol. 21, Issue 20] Jun. 1992, pp. 39-48.
Cochrane Roberta Jo
Leung Ting Yu
Mohan Chandrasekaran
Pirahesh Mir Hamid
Sidle Richard Sefton
Gates & Cooper LLP
International Business Machines - Corporation
Khatri Anil
LandOfFree
Intelligent compilation of materialized view maintenance for... does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with Intelligent compilation of materialized view maintenance for..., we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Intelligent compilation of materialized view maintenance for... will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-3134424