System and method for optimizing queries

A computer system for optimizing a query in a database management system (DBMS), the computer system comprising:

• a processor that is adapted to execute stored instructions;
• and
• a memory device that stores instructions, the memory device comprising:
• computer-implemented code adapted to receive a first query configured to generate a result set, wherein the query comprises an operation for an outer table, a first predicate, and a second predicate, wherein the first predicate comprises a first subquery correlating an inner table to the outer table, wherein the first predicate and the second predicate are disjunctive, wherein the first subquery comprises an operation for the inner table and a correlation predicate correlating the inner table and the outer table, and wherein the second query comprises a left outer join operation between the inner table and the outer table using the correlation predicate;
• computer-implemented code adapted to generate a first query tree representing the first query;
• computer-implemented code adapted to generate a second query tree representing a second query configured to generate the result set, wherein the second query does not comprise the first subquery, and wherein the second query comprises a group by operation configured to generate one row for each row in the outer table, and wherein the group by operation comprises a having clause comprising the first predicate and the second predicate;
• and
• computer-implemented code adapted to generate a query plan based on the second query tree.

A non-transitory machine-readable medium that stores machine-readable instructions executable by a processor to optimize a query in a database management system (DBMS), the tangible, machine-readable medium comprising:

• machine-readable instructions that, when executed by the processor, receive a first query configured to generate a result set, wherein the first query comprises an operation for an outer table, a first predicate, and a second predicate comprising a subquery correlated to the operation, wherein the first predicate and the second predicate are disjunctive, wherein the subquery comprises an operation for the inner table and a correlation predicate correlating the inner table and the outer table, and wherein the second query comprises a left outer join operation between the inner table and the outer table using the correlation predicate;
• machine-readable instructions that, when executed by the processor, generate a first query tree representing the first query;
• machine-readable instructions that, when executed by the processor, generate a second query tree representing a second query configured to generate the result set, wherein the second query does not comprise the subquery, and wherein the second query comprises a group by operation configured to generate one row for each row in the outer table, and wherein the group by operation comprises a having clause comprising the first predicate and the second predicate;
• and
• machine-readable instructions that, when executed by the processor, generate a query plan based on the second query tree.