System and method for performing database operations on a continuous stream of tuples

• US 20020038313A1
• Filed: 11/29/2001
• Published: 03/28/2002
• Est. Priority Date: 07/06/1999
• Status: Active Grant

• Current Assignee: Hewlett Packard Enterprise Development LP (Hewlett-Packard Enterprise Company)
• Original Assignee: Compaq Computer Corporation (HP Inc.)
• Inventors: Rathee, Raj K., Zeller, Hansjorg, Klein, Johannes, Van der Linden, Robbert C.
• Granted Patent: US 6,604,102 B2
• Time in Patent Office: Days
• US Class Current: 707/200
• CPC Class Codes: G06F 16/24557 Efficient disk access during query execution
• CPC Class Codes: G06F 16/24568 Data stream processing; Continuous queries
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access

Abstract

The SQL compiler and SQL executor in a database management system are extended to process queries requiring streaming mode processing of data stored in a table. A scan operator performs table access functions during execution of the streaming mode query. The scan operator first performs an initial scan to access rows in a specified database table, and then performs a delta scan to access new rows added to the table, as well as rows modified by other queries. The scan operator continues to process new data added to the table until the initiating user or application closes the associated cursor. A set of data structures are provided to keep track of active scan operators, including a session control block that includes fields for keeping track of whether the scan operator is performing an initial scan or a delta scan. The session control block also includes, for streaming mode scan operators, a delta scan list of new and modified rows to be processed in the delta scan. When a transaction terminates, any streaming mode scan operators whose operation was suspended because of rows locked by the transaction are automatically rescheduled for execution.

Claims

1. A method of processing a stream of data in a database management system, in accordance with a specified query, comprising:

using a scan operator, scanning a defined range of rows in a database table in accordance with the specified query, and returning qualifying rows in the defined range to a calling application;

using the scan operator, suspending operation of the scanning step when there are no qualifying rows in the defined range; and

automatically resuming operation of the scanning step when one or more qualifying rows in the defined range are added to the database table.

2. The method of claim 1, further including

suspending operation of the scanning step when the scanning step encounters a row in the database table that is locked in a mode that conflicts with a data access mode associated with the scanning step.

3. The method of claim 1, further including

automatically resuming operation of the scanning step when a transaction holding at least one lock on a row in the database table terminates.

4. The method of claim 1, wherein the scanning step includes:

performing an initial scan to access the defined range of rows in the database table;

while performing the initial scan, building a list of rows that are added to the database table during the initial scan and that are to be accessed after the initial scan;

performing a secondary scan to access rows in the list, including accessing the rows in the list that are not locked;

while performing the secondary scan, adding to the list of rows additional rows added to the database table after the initial scan; and

processing the accessed rows in accordance with the specified query.

5. The method of claim 1, wherein when the query specifies that any rows of the database table locked in conflicting modes are to be skipped, the conflicting modes preventing the scan operator from accessing the locked rows, the scanning step includes skipping over the rows, if any, locked in the conflicting modes.

6. The method of claim 5, wherein when the query does not specify that any rows of the database table locked in the conflicting modes are to be skipped, the suspending step includes suspending operation of the scanning step when the scanning step encounters a row in the database table that is locked in any conflicting mode that conflicts with a data access mode associated with the scanning step.

7. A method of processing a stream of data in a database management system, in accordance with a specified query, comprising:

using a scan operator, performing an initial scan to access a defined range of rows in the database table;

while performing the initial scan, building a list of rows that become available during the initial scan and that fall within the defined range of rows in the database table;

using the scan operator, performing a secondary scan to access rows included in the list;

while performing the secondary scan, adding to the list rows that become available after the initial scan and that fall within the defined range of rows in the database table; and

processing the accessed rows in accordance with the specified query.

8. The method of claim 7, wherein a row becomes available when added to the database table.

9. The method of claim 7, wherein a row of the database table that does not fall within the defined range of rows becomes available after it is modified to fall within the defined range of rows.

10. A database management system, comprising:

a database, including a plurality of tables, each table storing data in one or more rows;

an execution engine for executing a streaming mode query, for processing a stream of data, the execution engine including a scan operator for accessing a defined range of rows in a specified one of the database tables;

the scan operator including;

initial scan instructions for performing an initial scan to access the defined range of rows in the specified table;

a list data structure for storing a list of references to rows, if any, in the specified table that become available during the initial scan and that fall within the defined range of rows in the specified table; and

secondary scan instructions for performing a secondary scan to access rows referenced in the list data structure.

11. The database management system of claim 10, wherein

the list data structure is used to store references to additional rows within the defined range of rows in the specified table, if any, that become available after the initial scan.

12. The database management system of claim 10, wherein a row becomes available when added to the specified table.

13. The database management system of claim 10, wherein a row of the specified table that does not fall within the defined range of rows becomes available after it is modified to fall within the defined range of rows.

15. The computer program product of claim 14, wherein

the list data structure further storing references to additional rows within the defined range of rows in the specified table, if any, that become available after the initial scan.

16. The computer program product of claim 15, wherein a row becomes available when added to the specified table.

17. The computer program product of claim 15, wherein a row of the specified table that does not fall within the defined range of rows becomes available after it is modified to fall within the defined range of rows.

19. The method of claim 18, wherein a row becomes available when added to the database table.

20. The method of claim 18, wherein a row of the database table that does not fall within the defined range of rows becomes available after it is modified to fall within the defined range of rows.

21. The method of claim 18, wherein the scanning step includes:

performing an initial scan to access the defined range of rows in the database table;

while performing the initial scan, building a list of rows that become available during the initial scan and that fall within the defined range of rows in the database table;

performing a secondary scan to access rows included in the list;

while performing the secondary scan, adding to the list rows additional rows within the defined range of rows in the database table that become available after the initial scan; and

processing the accessed rows in accordance with the specified query.

14. A computer program product for use in conjunction with a computer system, the computer system including a database, including a plurality of tables, each table storing data in one or more rows;

the computer program product comprising a computer readable storage medium and a computer program mechanism embedded therein, the computer program mechanism comprising;

an execution engine for executing a streaming mode query, for processing a stream of data, the execution engine including a scan operator for accessing a defined range of rows in a specified one of the database tables;

the scan operator including;

initial scan instructions for performing an initial scan to access the defined range of rows in the specified table;

a list data structure for storing a list of references to rows, if any, in the specified table that become available during the initial scan and that fall within the defined range of rows in the specified table; and

secondary scan instructions for performing a secondary scan to access rows referenced in the list data structure.

18. A method of processing a stream of data in a database management system, in accordance with a specified query, comprising:

using a scan operator, scanning a defined range of rows in a database table in accordance with the specified query, and returning qualifying rows in the defined range to a calling application;

using the scan operator, suspending operation of the scanning step when there are no qualifying rows in the defined range; and

automatically resuming operation of the scanning step after one or more qualifying rows in the defined range in the database table become available.

22. A database management system, comprising:

a database, including a plurality of tables, each table storing data in one or more rows;

an execution engine for executing a streaming mode query, for processing a stream of data, the execution engine including a scan operator for accessing a defined range of rows in a specified one of the database tables;

the scan operator including;

instructions for scanning a defined range of rows in a database table in accordance with the specified query, and returning qualifying rows in the defined range to a calling application; and

instructions for suspending operation of the scan operator when there are no qualifying rows in the defined range; and

the execution engine including instructions for automatically resuming operation of the scan operator after one or more qualifying rows in the defined range become available in the database table.

23. The database management system of claim 22, wherein a row becomes available when added to the database table.

24. The database management system of claim 22, wherein a row of the database table that does not fall within the defined range of rows becomes available after it is modified to fall within the defined range of rows.

25. The database management system of claim 22, wherein the scan operator includes instructions for:

performing an initial scan to access the defined range of rows in the database table;

while performing the initial scan, building a list of rows that fall within the defined range of rows in the database table and become available during the initial scan;

performing a secondary scan to access rows in the list;

while performing the secondary scan, adding to the list rows additional rows that fall within the defined range of rows in the database table and become available after the initial scan; and

processing the accessed rows in accordance with the specified query.

27. The computer program product of claim 26, wherein a row becomes available when added to the database table.

28. The computer program product of claim 26, wherein a row of the database table that does not fall within the defined range of rows becomes available after it is modified to fall within the defined range of rows.

29. The computer program product of claim 26, wherein the scan operator includes instructions for:

performing an initial scan to access the defined range of rows in the database table;

while performing the initial scan, building a list of rows within the defined range of rows in the database table that become available during the initial scan and that are to be accessed after the initial scan;

performing a secondary scan to access rows in the list;

while performing the secondary scan, adding to the list of rows additional rows within the defined range of rows in the database table that become available after the initial scan; and

processing the accessed rows in accordance with the specified query.

26. A computer program product for use in conjunction with a computer system, the computer system including a database, including a plurality of tables, each table storing data in one or more rows;

the computer program product comprising a computer readable storage medium and a computer program mechanism embedded therein, the computer program mechanism comprising;

an execution engine for executing a streaming mode query, for processing a stream of data, the execution engine including a scan operator for accessing a defined range of rows in a specified one of the database tables;

the scan operator including;

instructions for scanning a defined range of rows in a database table in accordance with the specified query, and returning qualifying rows in the defined range to a calling application; and

instructions for suspending operation of the scan operator when there are no qualifying rows in the defined range; and

the execution engine including instructions for automatically resuming operation of the scan operator after one or more qualifying rows in the defined range become available in the database table.