Importance

Loading Importance Indicators...

Abstract

A method is provided for optimizing data queries using switch predicates to determine which query execution plan or sub-plan (generated by a query optimizer) is executed. The method may be implemented for a query that has multiple possible forms of execution or outcomes depending on a run-time condition or element (e.g., the value associated with a variable). The query may be expanded, and the different possibilities may be addressed in different sub-queries of the expanded query (e.g., with the sub-queries being joined through a UNION operation). Switch predicates are added to one or more sub-queries to determine which one(s) will execute, based on the run-time condition. A separate query execution sub-plan may be generated for each sub-query and the sub-plans may be combined to form a master or overall plan that is executed at run-time. For example, a NULLIF predicate returns one set of data (e.g., a single row of a database table) if an associated variable has a non-null value and another set of data (e.g., all rows having a value within a particular column) if the variable has a null value. Switch predicates comprising Boolean operations to test whether the variable has a null or non-null value may be applied to sub-queries corresponding to the different run-time possibilities. Optimization sub-plans may be generated and only a sub-plan associated with a switch predicate that evaluates to true will execute. Because the NULLIF predicate has been reduced to its different forms of operation, each possibility can be optimized to form a very efficient execution sub-plan.

Loading the Abstract Image...

First Claim