Efficient Processing of Queries Containing User-Defined Predicates

Query optimizers often have difficulties with the treatment of user-defined functions. In particular, the optimization of joins involving complex, user-defined predicates rather than just simple arithmetic comparisons may lead to query plans of poor quality. In this paper, we identify a class of user-defined functions that can be included in queries in such a way that efficient query optimization is still possible. For this class of functions, a join between two sets R and S, for example, could still be computed in time significantly less than O(¦R¦ · ¦S¦). To achieve this goal we introduce the concept of the φ-function, an operator to process each set separately with respect to the user-defined function(s) being used. These φ-functions dynamically derive information to constrain subsequent processing steps. The derived information allows in particular the application of an existing index or some other traditional join strategy. After demonstrating this technique on various examples, we present the results of a practical performance evaluation.