Method and system for limiting the cardinality of an SQL query result

• US 5,956,706 A
• Filed: 05/09/1997
• Issued: 09/21/1999
• Est. Priority Date: 05/09/1997
• Status: Expired due to Term

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Kossmann, Donald Alan, Carey, Michael James
• Primary Examiner(s): HO, RUAY L
• Application Number:
  US08/853,685
• Time in Patent Office: 865 Days
• Field of Search: 707/1-10, 707/100-104, 707/200-206
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2438 Embedded query languages
• CPC Class Codes: G06F 16/2445 Data retrieval commands; View definitions
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing

Abstract

A STOP AFTER clause for a relational database management system (RDBMS) structured query language (SQL) causes the RDBMS, in response to a query, to return a query result having at most a predetermined cardinality specified in the query. A query with a STOP AFTER clause is processed by the RDBMS by provision of one or more STOP operators that are inserted into a query execution plan generated by the RDBMS to execute the query. In a conservative policy, STOP operators are inserted in the query execution plan such that no tuples that might be required in the query result are discarded. In contrast, an aggressive policy inserts a STOP operator in the query execution plan wherever it is able to provide a beneficial cardinality reduction. A RESTART operator is inserted into aggressive policy query execution plans to ensure that at least the predetermined number of tuples are returned in the query result, and a final STOP operator is added at or near the root of the plan to ensure that at most the specified number of tuples are produced.

Claims

1. A method of generating a query execution plan in a computer with a memory responsive to a query including a specified size for a result of the query, comprising:

generating a query execution plan in the memory of the computer; and

inserting at least one STOP operator in the query execution plan for limiting the size of the query result to no more than a specified cardinality.

2. The method of claim 1, wherein the STOP operator is inserted in a conservative location of the query execution plan, the conservative location being characterized such that no tuples are discarded during execution of the query that may be required in the query result.

3. The method of claim 2, wherein the conservative location is characterized such that predicates in the query execution plan above the conservative location are non-reductive.

4. The method of claim 1, wherein the query includes an ORDER BY clause and the STOP operator is inserted in an aggressive location of the query execution plan, the aggressive location being characterized such that a first column of the ORDER BY clause may be computed.

5. The method of claim 4, wherein the aggressive location is a location in the query execution plan below at least one non-reductive predicate in the query execution plan.

6. The method of claim 5, flier comprising determining a stopping cardinality for the STOP operator, based on the specified cardinality.

7. The method of claim 4, further comprising inserting a RESTART operator into the query execution plan at a restart location above the aggressive location, the RESTART operator for restarting execution of the query when an input stream to the RESTART operator contains fewer than a preselected number of tuples.

8. The method of claim 7, wherein the restart location is characterized such that predicates in the query execution plan above the restart location are non-reductive.

9. A method executable by a computer for limiting the size of a query result in a database management system in response to a query, comprising:

receiving a query in the memory of the computer;

examining the query to determine whether the query includes a requirement for a size of a result of the query;

generating at least one query execution plan in response to the query; and

;

inserting at least one STOP operator in the query execution plan for limiting a number of tuples in the result to a cardinality number corresponding to a required size of the result.

10. The method of claim 9, further comprising generating a query execution plan with a plurality of predicates.

11. The method of claim 10, further comprising the step of:

inserting the STOP operator in a conservative location of the query execution plan, the conservative location being characterized such that predicates in the query execution plan above the conservative location are non-reductive.

12. The method of claim 10, further comprising:

inserting the STOP operator in an aggressive location of the query execution plan, the aggressive location being characterized such that the aggressive location is below at least one non-reductive predicate in the query execution plan.

13. The method of claim 12, further comprising determining a stopping cardinality for the STOP operator, based on the requirement for size of the result.

14. The method of claim 13, further comprising inserting a RESTART operator into the query execution plan at a restart location above the aggressive location, the RESTART operator for causing execution of the query execution plan to restart when an input stream to the RESTART operator contains fewer tuples than the stopping cardinality.

15. The method of claim 14, wherein the restart location is characterized such that predicates in the query execution plan above the restart location are non-reductive.

16. A computer program device comprising:

a computer program storage device readable by a digital processing apparatus; and

a program means on the program storage device and including instructions for causing the digital processing apparatus to limit the number of tuples returned by a database management system in a query result, by;

receiving a query including specification of a result cardinality;

generating a query execution plan in response to the query, the query execution plan having predicates for executing the query; and

placing an operator in the query execution plan to limit a number of tuples returned in the query result to no more than the result cardinality.

17. The computer program device of claim 16, wherein limiting is accomplished by inserting at least one STOP operator in the query execution plan, the STOP operator for limiting the size of an output stream in the query execution plan.

18. The computer program device of claim 17, the program means is further for:

inserting the STOP operator in a conservative location of the query execution plan, the conservative location being characterized such that predicates in the query execution plan above the conservative location are non-reductive.

19. The computer program device of claim 17, the program means is farther for:

inserting the STOP operator in an aggressive location of the query execution plan, the aggressive location being characterized such that the aggressive location is below at least one nonreductive predicate in the query execution plan.

20. The computer program device of claim 19, the program means is further for determining a stopping cardinality for the STOP operator, based on the result cardinality.

21. The computer program device of claim 20, the program means is further for inserting a RESTART operator into the query execution plan at a restart location above the aggressive location, the RESTART operator for causing execution of the query execution plan to restart when an input stream to the RESTART operator contains fewer than a predetermined number of tuples.

22. The computer program device of claim 21, wherein the restart location is characterized such that predicates in the query execution plan above the restart location are non-reductive.

23. A method for querying a database using a database management system, the method executable on a computer with a memory and including:

receiving in the memory of the computer a query that includes a clause limiting the size of a query result to N;

executing the query to produce a result set of tuples that satisfy the query;

during execution of the query, limiting the number of tuples in the result set to no more than N; and

returning the result set to a user.

24. The method of claim 23, wherein the clause is a STOP AFTER clause.

25. The method of claim 24, wherein the query further includes an ORDER BY clause.