Apparatus and method for reducing size of intermediate results by analyzing having clause information during SQL processing
AssigneeInternational Business Machines Corporation
InventorJohn Matthew Santosuosso
Life of this patent
Estimating the term…
Abstract
A query processor includes a HAVING clause processing mechanism that processes the HAVING clause in a query when generating the intermediate results, instead of generating the intermediate results without regard to the HAVING clause, then applying the HAVING clause to the intermediate results. As a result, the size of the intermediate results may be significantly reduced, and effort is not wasted in placing data into the intermediate results in one step that would be removed were the HAVING clause processed separately.
Claims3
An apparatus comprising:
- at least one processor;
- a memory coupled to the at least one processor;
- a database query residing in the memory that includes a SELECT clause and a HAVING clause;
- and
- a query processor residing in the memory and executed by the at least one processor, the query processor processing the database query and including only data that satisfies both the SELECT clause and the HAVING clause in a temporary table that holds intermediate results for the database query, wherein the temporary table never includes data that satisfies the SELECT clause without satisfying the HAVING clause and never includes data that satisfies the HAVING clause without satisfying the SELECT clause, wherein the query processor analyzes an index in generating the intermediate results, skips entries in the index that do not satisfy both the SELECT clause and the HAVING clause, and generates a final result set for the database query from the intermediate results in the temporary table.
A computer-implemented method for processing a database query that includes a SELECT clause and a HAVING clause, the method comprising the steps of:
- analyzing the database query;
- analyzing an index corresponding to the database query;
- skipping entries in the index that do not satisfy both the SELECT clause and the HAVING clause;
- and
- including only data that satisfies both the SELECT clause and the HAVING clause in a temporary table that holds intermediate results for the database query, wherein the temporary table never includes data that satisfies the SELECT clause without satisfying the HAVING clause and never includes data that satisfies the HAVING clause without satisfying the SELECT clause;
- and
- generating a final result set for the database query from the intermediate results in the temporary table.
A computer-readable program product comprising:
- (A) a query processor that processes a database query that includes a SELECT clause and a HAVING clause, the query processor including only data that satisfies both the SELECT clause and the HAVING clause in a temporary table that holds intermediate results for the database query, wherein the temporary table never includes data that satisfies the SELECT clause without satisfying the HAVING clause and never includes data that satisfies the HAVING clause without satisfying the SELECT clause, wherein the query processor analyzes an index in generating the intermediate results, skips entries in the index that do not satisfy both the SELECT clause and the HAVING clause, and generates a final result set for the database query from the intermediate results in the temporary table;
- and
- (B) recordable media bearing the query processor.
Dependencies are inferred from the claim text.