Data processing: database and file management or data structures – Database design – Data structure types
Reexamination Certificate
1999-07-06
2002-01-15
Alam, Hosain T. (Department: 2172)
Data processing: database and file management or data structures
Database design
Data structure types
C707S793000, C707S793000
Reexamination Certificate
active
06339772
ABSTRACT:
RELATED APPLICATIONS
This application is related to the following applications: “Database Management System and Method For Accessing Rows in a Partitioned Table,” Ser. No. 09/347,998; “Database Management System And Method For Dequeuing Rows Published to a Database Table,” Ser. No. 09/347,996; “System And Method For Performing Database Operations and for Skipping over Tuples Locked in an Incompatible Mode,” Ser. No. 09/347,995; and “Database Management System and Method For Updating Specified Tuple Fields Upon Transaction Rollback,” Ser. No. 09/347,994, all filed Jul. 6, 1999.
FIELD OF INVENTION
The present invention relates generally to a relational database system that has been extended to perform operations on a continuous stream of tuples, and particularly to a system and method for optimizing such operations and minimizing the impact of such those operations on other operations being performed by the same system.
BACKGROUND OF THE INVENTION
Traditional relational database systems receive commands falling into two categories: data definition commands (DDL) and data manipulation commands (DML). Data manipulation commands can be categorized as either queries that read data in the database or update statements that insert, delete or update data in the database base. Traditional database systems are designed and optimized for those operations. These systems are not designed and optimized for allowing applications to register to receive notifications when new data becomes available or existing data is updated.
However, receiving such notifications is essential for many applications. The need for such services has caused most transaction service vendors to extend their systems by including separate, special purpose resource managers for queue management and publish/subscribe services. These resource managers allow applications to enqueue and dequeue requests for information. Publish/subscribe services allow applications to publish and subscribe to notifications. In such systems, transactions access both an SQL database system (which may be considered to be a type of resource manager) as well as resource managers for queuing and/or publish/subscribe services. As a result, the SQL database system and the other resource managers have to participate together in an expensive two-phase commit protocol. Due to the lack of integration between the resource managers and the database system, the SQL compiler cannot optimize access to both the data stored by the queuing or publish/subscribe resource manager and the data stored in the database, and cannot perform joins across the different data sources.
To avoid subjecting applications to the two-phase commit protocol, some SQL database vendors (e.g., Oracle and Sybase) have integrated transactional queuing and publish/subscribe services into their database products. While their implementations remove the need for a two-phase commit protocol, these implementations use special purpose objects for queues and publication channels. These implementations prevent queues and publication channels from being accessed as part of SQL statements, unless the user is willing to forgo important characteristic properties of queue or notification channels, particularly suspending execution when all qualifying tuples have been returned and automatically resuming execution when new tuples become available. It also effectively prevents the SQL compiler in these systems from optimizing access to notifications and SQL data.
The present invention provides transactional queuing and publish/subscribe extensions to an SQL database infrastructure. These extensions do not introduce any special objects. Applications access regular SQL database tables, including tables used as queues or publication channels. Applications use SQL select statements to subscribe and/or dequeue notifications. Furthermore, applications use SQL insert and update statements to publish notifications. These extensions remove the need for a two-phase commit protocol, allow applications to perform join operations on data from different sources, and allow the SQL compiler to optimize access to both notifications and “normal” SQL data, using previously developed query optimization techniques. Further, execution of an SQL statement that accesses a data stream is blocked when all qualifying tuples have been processed and then resumes once new notifications become available.
As indicated above, the present invention extends traditional relational database systems to perform operations on a continuous stream of tuples, while retaining the traditional benefits of such systems, such as set oriented access using relational operators, transactional protection, and so on. The stream of tuples may represent queue entries, or notifications.
A simple example will assist in understanding the difference between traditional and streaming queries. A traditional query for viewing or otherwise processing records relating to sales by a particular salesperson might be:
select*from SalesInfo where salesperson=“Johnson, Mary”.
This query would retrieve all records from the table named “SalesInfo” having a value of “Johnson, Mary” in the salesperson field (column). Only records in the table at the time the query is submitted will be reported by this query.
However, if the user wants the system to continuously monitor new sales entries for a particular salesperson, the user of a traditional relational database system cannot define a simple SQL statement to perform that function, since monitoring features are not an integrated part of any of the widely used “dialects” of SQL. In contrast to that, the present invention allows users to issue an SQL select statement that performs this function:
select * from stream(SalesInfo) where salesperson=“Johnson, Mary”;
Using the present invention, when an SQL statement specifies stream access, the execution of the SQL statement never returns an “end of data” indicator. Rather, when all qualifying tuples in the specified table have been processed, continued execution is blocked, and then resumes when more qualifying tuples become available.
SUMMARY OF THE INVENTION
The SQL compiler and SQL executor of a relational database system are extended to process operations on streams of tuples and to access regular database tables as continuous streams of tuples. In particular, a new table access method provides “stream access” to a specified table. When using stream access, the SQL executor first reads all qualifying tuples in a specified table, and subsequently monitors for and returns new qualifying tuples being added to the table. The first part of the method is performed by a regular table scan, while the second part of the method is performed by a so-called delta scan. The monitoring function is performed until the cursor representing the SQL statement being executed, including the scan operations, is closed by the calling application.
The stream access mode of operation causes execution of an SQL statement to block, and thus be suspended, when there are no qualifying tuples to be returned to the calling application. Execution of the SQL statement automatically resumes (actually, is rescheduled) when new data becomes available.
A set of data structures are provided to keep track of active table access operators associated with active statements (whose execution has not yet terminated). The data structures keep track of the status of each active table access (scan) operator. In particular, a session control block is expanded to include fields for keeping track of whether a scan is in the initial “regular” scan phase, or is in the delta scan phase of a scan operation. The session control block also includes a “delta scan list” of new and modified rows to be processed in the delta scan phase. When any transaction terminates, and the terminated transaction updated the delta scan list, the delta scan operation is automatically rescheduled for execution to process the rows in the delta scan list that were added or modified by the terminated transaction.
Another feature of the prese
Klein Johannes
Rathee Raj K.
Van der Linden Robbert C.
Zeller Hansjorg
Alam Hosain T.
Compaq Computer Corporation
Nguyen Tam
Pennie & Edmonds LLP
LandOfFree
System and method for performing database operations on a... does not yet have a rating. At this time, there are no reviews or comments for this patent.
If you have personal experience with System and method for performing database operations on a..., we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and System and method for performing database operations on a... will most certainly appreciate the feedback.
Profile ID: LFUS-PAI-O-2844591