Operator-Based Parse Tree Representation for User-Defined Routines
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional relational database systems are unable to apply performance-enhancing features like indexing and statistics to query plans that involve user-defined routines, such as user-defined methods and functions, due to the lack of representation as operator-based parse trees, leading to suboptimal database performance.
Innovation Solution
The introduction of a mechanism to represent user-defined routines as operator-based parse trees through the use of indicators like BIND_TO_BINOP in data definition language statements, allowing the optimizer to categorize predicate expressions and utilize indexing and statistics for improved query planning.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If user-defined routines (UDMs and UDFs) are used in database queries, then functionality and flexibility are improved, but the ability to apply performance-enhancing features (indexing and statistics) deteriorates
Solution Approach 1:
The patent introduces an intermediary mechanism (operator-based parse tree representation) that bridges user-defined routines and the optimizer. By representing UDMs and UDFs as operator-based parse trees with bindable operators, the system enables the optimizer to categorize and analyze these routines, allowing performance-enhancing features like indexing and statistics to be applied effectively while maintaining the full functionality of user-defined routines.
2Productivity
If conventional operator-based parse trees are used for predefined data types, then query optimization efficiency is improved, but compatibility with user-defined routines deteriorates
Solution Approach 1:
The patent applies universality by extending the operator-based parse tree representation to handle both predefined data types and user-defined routines uniformly. The BIND_TO_BINOP indicator and associated mechanisms enable the same parse tree structure to represent both conventional operators and user-defined routines, allowing the optimizer to process both types with the same efficient categorization and analysis procedures.
Data Source
AI summary
A database system constructs representation for predicate expressions invoking user-defined routines. A first representation is constructed if an indicator has a first value, and a second representation is constructed if the indicator has a second value. Based on which representation is used, an optimizer in the database system selects use of indexing and/or statistics and/or other performance-enhancement features in generating a query plan.