Optimizing database query performance by deriving query predicates

A database management computer system comprising:

• (a) computer readable memory operatively connected to said data processing system;
• and
• (b) a module providing operation for directing said data processing system to:
• (i) for each column and each simple equality predicate in the query, determine whether the predicate can be used together with an associated column generating expression to derive an additional predicate, deriving the additional predicate, add the derived predicate to the query, and remove each predicate used in the derivation from a list of unexamined predicates;
• (ii) examine the monotonicity of the column generating expression if there is any remaining inequality predicate in the list of unexamined predicates;
• (iii) determine the type of derived predicate based on the monotonicity of the column generating expression for each remaining inequality predicate in the list of unexamined predicates;
• and
• (iv) generate the resulting derived predicate from each of the remaining inequality predicates.

A method for use in a database management system for generating a derived predicate from a database query containing at least one column generating expression, said method comprising:

• (a) reading a selected query statement;
• (b) selecting a column generating expression;
• (c) selecting a column;
• (d) selecting a first simple predicate in the column generating expression;
• (e) determining:
• (i) whether the first predicate is an IS NULL,
• (ii) whether the first predicate equates a constant value to a column,
• (iii) whether the first predicate is an inequality that connects the selected column to a constant,
• (iv) whether there is another inequality predicate associated with another column,
• (v) whether there is another inequality predicate associated with the selected column,
• (vi) whether there is an IN LIST predicate associated with another column,
• (vii) whether the first predicate is an IN LIST predicate associated with the selected column,
• (viii) whether there is an IN LIST predicate already associated with the selected column,
• (ix) whether there is an IN LIST predicate already associated with another column,
• (x) whether there is an inequality predicate already associated with another column,
• (xi) whether there is any column associated with an IS NULL predicate,
• (xii) whether any column is left unresolved, and
• (xiii) whether any unresolved column is associated with an IS NULL predicate,
• (f) setting the derived predicate to a calculated value based on the determinations in step (e);
• and
• (g) for each inequality predicate, determining the monotonicity status of the column generating expression referring to the column in the predicate by steps comprising using an inequality predicate lookup table related to the type of monotonicity of the generating expression, calculating constants, and generating a derived predicate.